这个查询也适用于组合键:

select c.constraint_name
    , x.table_schema as schema_name
    , x.table_name
    , x.column_name
    , y.table_schema as foreign_schema_name
    , y.table_name as foreign_table_name
    , y.column_name as foreign_column_name
from information_schema.referential_constraints c
join information_schema.key_column_usage x
    on x.constraint_name = c.constraint_name
join information_schema.key_column_usage y
    on y.ordinal_position = x.position_in_unique_constraint
    and y.constraint_name = c.unique_constraint_name
order by c.constraint_name, x.ordinal_position

我创建了一个小工具来查询和比较数据库模式: Dump PostgreSQL数据库模式到文本

有关于FK的信息，但ollyc的回复提供了更多的细节。

只需替换'您的表名'在下面的查询与您的表名。

简短但贴心的赞，如果对你有用的话。

select * from information_schema.key_column_usage
where constraint_catalog=current_catalog and table_name='your_table_name'
and position_in_unique_constraint notnull;

SELECT r.conname
      ,ct.table_name
      ,pg_catalog.pg_get_constraintdef(r.oid, true) as condef
  FROM pg_catalog.pg_constraint r, information_schema.constraint_table_usage ct
 WHERE r.contype = 'f' 
   AND r.conname = ct.constraint_name
 ORDER BY 1

正确的解决方案，使用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视图。

最快的验证直接在bash答案完全基于这个答案

IFS='' read -r -d '' sql_code << EOF_SQL_CODE
      SELECT
      o.oid
      , o.conname AS constraint_name
      , (SELECT nspname FROM pg_namespace WHERE oid=m.relnamespace) AS source_schema
      , m.relname AS source_table
      , (SELECT a.attname FROM pg_attribute a
      WHERE a.attrelid = m.oid AND a.attnum = o.conkey[1] AND a.attisdropped = false) AS source_column
      , (SELECT nspname FROM pg_namespace
      WHERE oid=f.relnamespace) AS target_schema
      , f.relname AS target_table
      , (SELECT a.attname FROM pg_attribute a
      WHERE a.attrelid = f.oid AND a.attnum = o.confkey[1] AND a.attisdropped = false) AS target_column
      , ROW_NUMBER () OVER (ORDER BY o.oid) as rowid
      FROM pg_constraint o
      LEFT JOIN pg_class f ON f.oid = o.confrelid
      LEFT JOIN pg_class m ON m.oid = o.conrelid
      WHERE 1=1
      AND o.contype = 'f'
      AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r')
EOF_SQL_CODE

psql -d my_db -c "$sql_code"