Re: How to get a list of tables that have a particular column value?

On 24 Dec 2009, at 14:35, Rajan, Pavithra wrote:

>
> Hello -Yes I need to find out the column value like '%Volt%' in any
> column of data_type (character varying) of any table. Basically what I
> need to do is go thro each columns of all tables and find any entries
> that have  Voltage followed by some number e.g. 'Voltage 4.8000'.Then I
> need to use regexep_replace function to curtail the precision to two
> digits after decimal instead of 4.

Why are you using a text type column? The numerical values in there clearly matter to you as you're trying to change
theirprecision now. A numeric column seems far more suitable, or maybe you should have a look at Martijn van
Oosterhout'stagged types (although that code doesn't compile against modern versions of Postgres anymore it seems). 

> Eg:table name 'app' has a column name description which has 4 entries
> like
>   |    description         |
>   |  character varying(50) |
>   |                        |
>   |    Voltage 2.4000      |
>   |    Voltage 4.8000      |
>   |    Voltgae 3.0509      |              |
            ^^^
Here's another reason to avoid a text type column for storing your data. I'm assuming your actual data is generated and
theabove is a typo in your example, but if anyone _ever_ inserts data by hand you're going to run into this kind of
problem.

>   |    Voltage 1.0010      |
>
> Then I run a sql file with this command any many other Update commands
> form other tables that have similar entries in various columns.
>
> UPDATE app SET app = regexp_replace(description,'4.8000','4.80') where
> description like 'Volt%';

That looks like a lot of work, you're not using all the power regular expressions give you. I think you'll like:
    regex_replace(
        description,
        E'([1-9][0-9]*)\.([0-9]{2})[0-9]*',
        E'\\1.\\2'
    )

development=> select regexp_replace('4.8000', E'([1-9][0-9]*)\.([0-9]{2})[0-9]*'
, E'\\1.\\2');
 regexp_replace
----------------
 4.80
(1 row)

Regards,
Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4b3748619957413871377!