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?

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.