Thread: How to get a list of tables that have a particular column value?
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.
Pavithra Rajan
<mailto:rajanp@coned.com>
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.
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.
On Thursday 24 December 2009 5:35:10 am 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.
>
> 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.
>
>
Would it not be easier to dump the data and does this against the text dump and
then restore the data?
--
Adrian Klaver
aklaver@comcast.net
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!