PostgreSQL - query all tables' all table columns

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

Method I've tried:

select tablename from pg_tables where schemaname = 'public'

UNION

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])...].

time

UNION

time

time

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

Is there any elegant way of doing this?

2 Answers
2

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.

array_agg()

information_schema.tables

information_schema.columns

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.

array_agg()

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

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])]


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.