SELECT 
    conrelid::regclass AS table_from,
    conname,
    pg_get_constraintdef(oid) as condef    
    FROM pg_catalog.pg_constraint r

也适用于所有约束条件。例如，使用pysql:

使用Key所引用的主键的名称并查询information_schema:

select table_name, column_name
from information_schema.key_column_usage
where constraint_name IN (select constraint_name
  from information_schema.referential_constraints 
  where unique_constraint_name = 'TABLE_NAME_pkey')

这里'TABLE_NAME_pkey'是外键引用的主键的名称。

最快的验证直接在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"

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

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

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

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

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)