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 their precision now. A numeric column seems far more suitable, or maybe you should have a look at Martijn van Oosterhout's tagged 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 the above 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!