PostgreSQL 15中如何展示含外键关联的表结构（兼容PG14结果）

你的PG14查询在PG15失效的核心原因是PostgreSQL 15调整了information_schema.constraint_column_usage的收录规则——主键约束不再被该表收录（因为主键是当前表的约束，不存在外部引用），原查询的INNER JOIN会直接过滤掉所有主键记录，导致结果缺失。

以下是适配PG15的修改版查询，能生成和原语句完全一致格式的结果集：

SELECT DISTINCT 
    tc.table_schema "sc",
    tc.table_name "tab1",
    kcu.column_name "columnname",
    tc.constraint_name "conname",
    CASE
        WHEN tc.constraint_type = 'FOREIGN KEY' THEN 'R'
        WHEN tc.constraint_type = 'PRIMARY KEY' THEN 'P'
    END AS constrainttype,
    kcu.ordinal_position "position",
    CASE WHEN tc.constraint_type = 'FOREIGN KEY' THEN ccu.table_schema END AS r_table_schema,
    CASE WHEN tc.constraint_type = 'FOREIGN KEY' THEN ccu.table_name END AS r_table_name,
    CASE WHEN tc.constraint_type = 'FOREIGN KEY' THEN ccu.column_name END AS r_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
LEFT JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
    AND ccu.table_schema = tc.table_schema
    AND tc.constraint_type = 'FOREIGN KEY' -- 仅外键约束关联引用信息
WHERE tc.table_schema = 's1';

关键修改说明 #

1. 将原有的INNER JOIN constraint_column_usage改为LEFT JOIN，确保主键记录不会被过滤
2. 在ccu的关联条件中增加AND tc.constraint_type = 'FOREIGN KEY'，仅为外键约束匹配引用的表/列信息
3. 保留原有的CASE逻辑，保证主键记录的引用字段返回空值，外键记录返回正确关联信息

示例结果（与PG14格式一致） #

内容的提问来源于stack exchange，提问作者Erik Christiansen