获取所有具有外键约束的列名

Question

我正在使用PostgreSQL。我想写一个查询，返回所有具有外键约束的列名，以及它们所引用的表的名称。

Answer 1

据我所见，information_schema视图没有提供列名，因此您必须使用catalog:

SELECT c.conrelid::regclass AS source_table,
a.attname AS column_name,
k.n AS position,
c.confrelid::regclass AS referenced_table
FROM pg_constraint AS c
CROSS JOIN LATERAL unnest(c.conkey) WITH ORDINALITY AS k(attnum, n)
JOIN pg_attribute AS a
ON k.attnum = a.attnum AND c.conrelid = a.attrelid
WHERE c.contype = 'f'
ORDER BY c.conrelid::regclass::text, k.n;

要仅获取特定表格的数据，请在WHERE条件中添加以下内容：

AND c.conrelid = 'mytable'::regclass