Get tables and columns using Postgresql
Posted on April 30th, 2008 by Fred
To get the list of the available tables of a database in PostgreSQL:
SELECT tablename FROM pg_tables
WHERE tablename NOT LIKE ‘pg%’
AND tablename NOT LIKE ‘sql%’
To get the list of columns for a particular table (table_name):
SELECT attname FROM pg_attribute, pg_type
WHERE typname = ‘table_name’
AND attrelid = typrelid
AND attname NOT IN (‘cmin’, ‘cmax’, ‘ctid’, ‘oid’, ‘tableoid’, ‘xmin’, ‘xmax’);
Another way to get the list of columns for a particular table (tb_name) and schema (sc_name):
SELECT column_name
FROM information_schema.columns
WHERE table_name = ‘tb_name’
and table_schema = ‘sc_name’”;
Is there a way of select from the columns and tables returned. Something equivalent to eval() perhaps?