如何获取PostgreSQL中所有表的禁用外键列表？

在PostgreSQL中，没有像MSSQL那样直接提供is_disabled字段来标识禁用的外键。外键的禁用通常是通过禁用其对应的触发器实现的，你可以通过查询系统表获取这类外键的列表：

SELECT 
    conname AS foreign_key_name,
    nsp.nspname AS table_schema,
    cls.relname AS table_name,
    ref_nsp.nspname AS referenced_table_schema,
    ref_cls.relname AS referenced_table_name
FROM pg_constraint con
JOIN pg_class cls ON con.conrelid = cls.oid
JOIN pg_namespace nsp ON cls.relnamespace = nsp.oid
JOIN pg_class ref_cls ON con.confrelid = ref_cls.oid
JOIN pg_namespace ref_nsp ON ref_cls.relnamespace = ref_nsp.oid
JOIN pg_trigger trg ON con.oid = trg.tgconstraint
WHERE con.contype = 'f'
AND trg.tgenabled = 'D';

说明： #

• pg_constraint：存储数据库所有约束，contype = 'f'用于筛选外键约束
• pg_trigger：存储触发器信息，tgenabled = 'D'表示触发器已被禁用
• 关联查询后可得到禁用外键的名称、所在表、关联表等核心信息

如果你的场景是通过设置约束为可延迟且初始延迟（DEFERRABLE INITIALLY DEFERRED）来实现事务内延迟检查外键的效果，也可以用以下查询：

SELECT 
    constraint_name,
    table_schema,
    table_name,
    referenced_table_schema,
    referenced_table_name
FROM information_schema.table_constraints tc
JOIN information_schema.referential_constraints rc 
    USING (constraint_schema, constraint_name)
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.is_deferrable = 'YES'
AND tc.initially_deferred = 'YES';

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