PostgreSQL - query all tables' all table columns

Question

How can I query all tables' all table columns in a database?

Method I've tried:

1. get all table names using select tablename from pg_tables where schemaname = 'public'
2. Process cmd string using UNION method of Postgres.
3. Execute the cmd string.

I have 19 tables in a DB, and my method results in 19 times slower querying time. And further more, it does not return what I want. All of the tables have two columns, one of them always being a column name called time. Using the UNION method does not return 19 time strings. It just returns one time string, and 19 other column names. But I want something like this: [('table_1', ['time', 'col']), ('table_2', ['time', 'col']), ('table_3', ['time', 'col])...].

Is there any elegant way of doing this?

Fresher

Answer 1

You can do this in a single query by using array_agg() and a join on the information_schema.tables and information_schema.columns tables.

This would return something similar to your expected output:

select
    t.table_name,
    array_agg(c.column_name::text) as columns
from
    information_schema.tables t
inner join information_schema.columns c on
    t.table_name = c.table_name
where
    t.table_schema = 'public'
    and t.table_type= 'BASE TABLE'
    and c.table_schema = 'public'
group by t.table_name;

Here I'm taking all the tables first, then I join it with the columns tables, and finally use array_agg() to aggregate them all to an array, grouped by the table name.

Hope it helps :) Feel free to ask if you have any doubts.

Birthright

Answer 2

Since you're working in Python, clearest if you handle this in two steps I think. First, use this query to retrieve table/column name pairs:

select table_name, column_name 
from information_schema.columns 
where table_name in (
    select tablename from pg_tables where schemaname = 'public');

Then, stick the results into a defaultdict:

from collections import defaultdict

my_cols = <your code to execute the query above and fetch all rows>
column_mapping = defaultdict(list)
for tablename, colname in my_cols:
    column_mapping[tablename].append(colname)

This will give you:

>>> column_mapping
defaultdict(<type 'list'>, {'table_1': ['time', 'col'], 'table_2': ['time', 'col'], 'table_3': ['time', 'col]})

Which you can convert trivially with:

>>> column_mapping.items()
[('table_1', ['time', 'col']), ('table_2', ['time', 'col']), ('table_3', ['time', 'col])]

Avirulent

Answer 3

Create a VIEW like this

CREATE VIEW table_column_info AS

SELECT table_name, STRING_AGG(column_name, ', ') AS columns
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name;

And use it at your own disposal: SELECT * FROM table_column_info;

Basion

Answer 4

It is a query for all tables in specified schemas.

SELECT 
    pg_namespace.nspname as schema_name,
    relname as table_name,
    pg_catalog.obj_description(pg_class.oid) as comment
    FROM pg_class
    INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
    WHERE pg_namespace.nspname IN ('public') AND pg_class.relkind IN ('r', 't')
    ORDER BY relname

The example filters only public schema, but you can specify your own.
Condition relkind IN ('r', 't') describes that we need only tables, without indexes, sequences and other.
Here is a doc for table pg_class - https://www.postgresql.org/docs/current/catalog-pg-class.html Function pg_catalog.obj_description returns a comment for the database object. Here is a doc: https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-COMMENT-TABLE

It is as query for all columns in specified tables:

SELECT 
    attrelid::regclass AS table_name,
    attname            AS column_name, 
    pg_catalog.col_description(attrelid, attnum) as column_comment,
    atttypid::regtype  AS column_datatype
    FROM pg_attribute
    INNER JOIN pg_class ON pg_class.oid = attrelid
    WHERE attrelid IN (
        SELECT pg_class.oid
        FROM pg_class
        INNER JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
        WHERE pg_namespace.nspname IN ('public') AND pg_class.relkind IN ('r', 't')
    )
    AND attnum > 0 AND attisdropped IS FALSE
    ORDER BY pg_class.relname, pg_attribute.attnum

Condition attnum > 0 AND attisdropped IS FALSE describes that we need only visible and not deleted columns.
Here is a doc for table pg_attribute - https://www.postgresql.org/docs/current/catalog-pg-attribute.html Function pg_catalog.col_description returns a comment for the table column.

Goatsucker

Answer 5

This works with postgresql v16:

\d+ table_name

Furthermore, here is a quick little cheat sheet:

\l to list all dbs

\c database_name to switch to desired database

\dt to list all tables in selected database

\dt+ to get further info on the selected database

then, as noted above, \d+ table_name to print out info for the table (including columns)

Sanctitude