如何从PostgreSQL的information schema获取约束引用的表和列名

PostgreSQL的information_schema.key_column_usage确实没有直接存储被引用的表和列信息，需要关联多个系统表来实现等价查询，具体SQL语句如下：

SELECT
  tc.constraint_name AS name,
  kcu.table_name AS parent_table,
  kcu.column_name AS parent_column,
  ccu.table_name AS referenced_table,
  ccu.column_name AS referenced_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage ccu
  ON tc.constraint_name = ccu.constraint_name
  AND tc.table_schema = ccu.table_schema
WHERE tc.table_schema = 'public'
  AND tc.constraint_type = 'FOREIGN KEY';

逻辑说明 #

• 通过table_constraints表筛选出指定schema下所有外键约束（constraint_type = 'FOREIGN KEY'）
• 关联key_column_usage获取外键所在的父表及对应列
• 关联constraint_column_usage获取外键引用的目标表及对应列

内容的提问来源于stack exchange，提问作者Judy T Raj