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.
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
