Re: How to get a list of tables that have a particular column value? - Mailing list pgsql-general

 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.

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      |              |
   |    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%';

Hence I need to know all the tables and their column name ("data_type
:character varying") that has this 4 digit extn.

Thank you.




-----Original Message-----
From: John R Pierce [mailto:pierce@hogranch.com]
Sent: Wednesday, December 23, 2009 3:11 PM
To: Rajan, Pavithra
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How to get a list of tables that have a
particular column value?

Rajan, Pavithra wrote:
>
> Hello - I would like to know if there is a way to find all the table
> names in a data base that have a particular* column value*  eg:"volt"
> .ie given a column value (not column name) how to I find which tables
> and their column names  have them .Thank you.
>

Do you want to search for values 'volt' in -any- column of any table, or
just in certain columns if they exist?

you can enumerate the tables by querying information_schema.tables, and
you can enumerate the columns of a table via information_schema.columns,
so I'd imagine you would need a script or program that iterates through
the tables, and through the columns of each table, then constructs and
executes a query of that column of that table for your value.  You'll
probably want to check the column datatype first and not query numeric
fields.

when you do these queries, just what is it you're looking for, the
simple existence of the value in table X as a yes/no thing?    since
each table has its own structure, its unclear what other useful info you
could extract

on a large database, this is going to be very time consuming as it
likely will require sequential scanning everything multiple times if you
want to look at every text column of every table.

now, if this is a requirement to look for this value in a specific
column of various specific tables, perhaps that column should be its OWN
table, (id serial, thing text) and the other tables reference this as a
foreign key.