PostgreSQL - 查询所有表的所有列

Question

我该如何查询数据库中所有表的所有列？

我尝试过以下方法：

1. 使用select tablename from pg_tables where schemaname = 'public'获取所有表名。 2. 使用Postgres的UNION方法处理cmd字符串。 3. 执行cmd字符串。

我的数据库中有19个表，这种方法查询时间慢了19倍。而且它并没有返回我想要的结果。所有表都有两列，其中一列始终是一个名为time的列。使用UNION方法不会返回19个time字符串，而只会返回一个time字符串和19个其他列名。但我想得到像这样的结果：[('table_1', ['time', 'col']), ('table_2', ['time', 'col']), ('table_3', ['time', 'col])...]。

有没有更优雅的方法来实现这个功能？

- Eric Kim

Answer 1

您可以通过在 information_schema.tables 和 information_schema.columns 表上使用 array_agg() 和连接来完成单个查询。

这将返回类似于您期望的输出：

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;

这里我首先获取所有的表格，然后将其与列表格连接，最后使用array_agg()将它们聚合到一个数组中，按表名分组。

希望这能有所帮助 :) 如果您有任何疑问，请随时提出。

- Nimeshka Srimal

Answer 2

由于您正在使用Python，我认为最清晰的方法是分两步处理。首先，使用此查询检索表/列名称对：

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

然后，将结果放入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)

这将给您带来：

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

您可以轻松地使用以下方式进行转换：

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

- chris

Answer 3

创建一个像这样的视图

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;

自行处理并使用：SELECT * FROM table_column_info;

- abir

Answer 4

这是一个查询指定模式下所有表的查询。

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

这个示例过滤了只有public架构，但是你可以指定自己的。
条件relkind IN ('r', 't')描述了我们只需要表格，而没有索引、序列和其他内容。
这里有一个关于表格pg_class的文档 - https://www.postgresql.org/docs/current/catalog-pg-class.html。 函数pg_catalog.obj_description返回数据库对象的注释。 这里有一个文档：https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-COMMENT-TABLE

它是针对指定表格中所有列的查询：

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

条件attnum >0 AND attisdropped IS FALSE描述了我们只需要可见且未删除的列。
这里有一个关于表格pg_attribute的文档 - https://www.postgresql.org/docs/current/catalog-pg-attribute.html 函数pg_catalog.col_description返回表列的注释。

- Mike