注意:在读取约束列时，不要忘记列的顺序!

SELECT conname, attname
  FROM pg_catalog.pg_constraint c 
  JOIN pg_catalog.pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
 WHERE attrelid = 'schema.table_name'::regclass
 ORDER BY conname, array_position(c.conkey, a.attnum)

我自己的贡献。目标是备份所有外键的定义:

SELECT
    'ALTER TABLE ' || tc.table_schema || '.' || tc.table_name || E'\n
    ADD FOREIGN KEY (' || kcu.column_name || ')' || E'\n
    REFERENCES ' || ccu.table_schema || '.' || ccu.table_name ||
    ' (' || ccu.column_name || ') ' || E'\n    ' ||
    CASE WHEN rc.match_option <> 'NONE' THEN E'\n
    MATCH ' || rc.match_option ELSE '' END ||
    CASE WHEN rc.update_rule <> 'NO ACTION' THEN E'\n
    ON UPDATE ' || rc.update_rule || ' ' ELSE '' END ||
    CASE WHEN rc.delete_rule <> 'NO ACTION'
    THEN 'ON DELETE ' || rc.delete_rule ELSE '' END || ';'
AS add_constraint
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
    JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
        AND ccu.table_schema = tc.table_schema
    JOIN information_schema.referential_constraints AS rc
        ON tc.constraint_name=rc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
\t\a\g\a\ta

注意:在读取约束列时，不要忘记列的顺序!

SELECT conname, attname
  FROM pg_catalog.pg_constraint c 
  JOIN pg_catalog.pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY (c.conkey)
 WHERE attrelid = 'schema.table_name'::regclass
 ORDER BY conname, array_position(c.conkey, a.attnum)

PSQL就是这样做的，如果你用:

psql -E

它将准确地显示执行了哪些查询。在查找外键的情况下，它是:

SELECT conname,
  pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '16485' AND r.contype = 'f' ORDER BY 1

在这种情况下，16485是我正在寻找的表的oid -你可以通过将你的表名转换为regclass来获得它:

WHERE r.conrelid = 'mytable'::regclass

如果表名不是唯一的(或者是搜索路径中的第一个)，则对表名进行模式限定:

WHERE r.conrelid = 'myschema.mytable'::regclass

SELECT conrelid::regclass AS table_name, 
       conname AS foreign_key, 
       pg_get_constraintdef(oid) 
FROM   pg_constraint 
WHERE  contype = 'f' 
AND    connamespace = 'public'::regnamespace   
ORDER  BY conrelid::regclass::text, contype DESC;

https://soft-builder.com/how-to-list-all-foreign-keys-in-postgresql-database

正确的解决方案，使用information_schema，使用多列键，在两个表中正确地连接不同名称的列，并且与ms sqlsever兼容:

select fks.TABLE_NAME as foreign_key_table_name
, fks.CONSTRAINT_NAME as foreign_key_constraint_name
, kcu_foreign.COLUMN_NAME as foreign_key_column_name
, rc.UNIQUE_CONSTRAINT_NAME as primary_key_constraint_name
, pks.TABLE_NAME as primary_key_table_name
, kcu_primary.COLUMN_NAME as primary_key_column_name
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS fks -- foreign keys
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_foreign -- the columns of the above keys
    on fks.TABLE_CATALOG = kcu_foreign.TABLE_CATALOG
    and fks.TABLE_SCHEMA = kcu_foreign.TABLE_SCHEMA
    and fks.TABLE_NAME = kcu_foreign.TABLE_NAME
    and fks.CONSTRAINT_NAME = kcu_foreign.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc -- referenced constraints
    on rc.CONSTRAINT_CATALOG = fks.CONSTRAINT_CATALOG
    and rc.CONSTRAINT_SCHEMA = fks.CONSTRAINT_SCHEMA
    and rc.CONSTRAINT_NAME = fks.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.TABLE_CONSTRAINTS pks -- primary keys (referenced by fks)
    on rc.UNIQUE_CONSTRAINT_CATALOG = pks.CONSTRAINT_CATALOG
    and rc.UNIQUE_CONSTRAINT_SCHEMA = pks.CONSTRAINT_SCHEMA
    and rc.UNIQUE_CONSTRAINT_NAME = pks.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu_primary
    on pks.TABLE_CATALOG = kcu_primary.TABLE_CATALOG
    and pks.TABLE_SCHEMA = kcu_primary.TABLE_SCHEMA
    and pks.TABLE_NAME = kcu_primary.TABLE_NAME
    and pks.CONSTRAINT_NAME = kcu_primary.CONSTRAINT_NAME
    and kcu_foreign.ORDINAL_POSITION = kcu_primary.ORDINAL_POSITION -- this joins the columns
where fks.TABLE_SCHEMA = 'dbo' -- replace with schema name
and fks.TABLE_NAME = 'your_table_name' -- replace with table name
and fks.CONSTRAINT_TYPE = 'FOREIGN KEY'
and pks.CONSTRAINT_TYPE = 'PRIMARY KEY'
order by fks.constraint_name, kcu_foreign.ORDINAL_POSITION

注意:在potgresql和sqlserver的information_schema实现之间存在一些差异，这使得顶部答案在两个系统上给出不同的结果——一个显示外键表的列名，另一个显示主键表的列名。出于这个原因，我决定使用KEY_COLUMN_USAGE视图。