选择的答案不为我工作，所以张贴我的sql工作。

select 
    con.conname as constraint_name,
    src_schema.nspname as source_schema,
    source.relname as source_table,
    source_col.attname as source_column,
    trg_schema.nspname as target_schema,
    target.relname as target_table,
    target_col.attname as target_column
from 
    pg_constraint con
inner join 
    pg_class source on source.oid = con.conrelid
inner join
    pg_attribute source_col on source_col.attrelid = con.conrelid and source_col.attnum = con.conkey[1] and source_col.attisdropped = false
inner join
    pg_namespace src_schema on src_schema.oid = source.relnamespace
inner join 
    pg_class target on target.oid = con.confrelid
inner join
    pg_attribute target_col on target_col.attrelid = con.confrelid and target_col.attnum = con.confkey[1] and source_col.attisdropped = false    
inner join
    pg_namespace trg_schema on trg_schema.oid = target.relnamespace 

Ollyc的答案很好，因为它不是特定于postgres的，但是，当外键引用多个列时，它就会崩溃。以下查询适用于任意数量的列，但它严重依赖于Postgres扩展:

select 
    att2.attname as "child_column", 
    cl.relname as "parent_table", 
    att.attname as "parent_column",
    conname
from
   (select 
        unnest(con1.conkey) as "parent", 
        unnest(con1.confkey) as "child", 
        con1.confrelid, 
        con1.conrelid,
        con1.conname
    from 
        pg_class cl
        join pg_namespace ns on cl.relnamespace = ns.oid
        join pg_constraint con1 on con1.conrelid = cl.oid
    where
        cl.relname = 'child_table'
        and ns.nspname = 'child_schema'
        and con1.contype = 'f'
   ) con
   join pg_attribute att on
       att.attrelid = con.confrelid and att.attnum = con.child
   join pg_class cl on
       cl.oid = con.confrelid
   join pg_attribute att2 on
       att2.attrelid = con.conrelid and att2.attnum = con.parent

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

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

以下是Andreas Joseph Krogh从PostgreSQL邮件列表提供的解决方案:http://www.postgresql.org/message-id/200811072134.44750.andreak@officenet.no

SELECT source_table::regclass, source_attr.attname AS source_column,
    target_table::regclass, target_attr.attname AS target_column
FROM pg_attribute target_attr, pg_attribute source_attr,
  (SELECT source_table, target_table, source_constraints[i] source_constraints, target_constraints[i] AS target_constraints
   FROM
     (SELECT conrelid as source_table, confrelid AS target_table, conkey AS source_constraints, confkey AS target_constraints,
       generate_series(1, array_upper(conkey, 1)) AS i
      FROM pg_constraint
      WHERE contype = 'f'
     ) query1
  ) query2
WHERE target_attr.attnum = target_constraints AND target_attr.attrelid = target_table AND
      source_attr.attnum = source_constraints AND source_attr.attrelid = source_table;

这个解决方案处理引用多个列的外键，并避免重复(其他一些答案无法做到这一点)。我唯一改变的是变量名。

下面是一个示例，返回所有引用权限表的雇员列:

SELECT source_column
FROM foreign_keys
WHERE source_table = 'employee'::regclass AND target_table = 'permission'::regclass;

检查ff帖子的解决方案，不要忘记标记这个，当你认为这是有帮助的

http://errorbank.blogspot.com/2011/03/list-all-foreign-keys-references-for.html

SELECT
  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
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
  o.contype = 'f' AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r');

我升级了@ollyc的答案，目前在顶部。 我同意@fionbio，因为key_column_usage和constraint_column_usage在列级上没有相关信息。

如果constraint_column_usage具有像key_column_usage一样的ordinal_position列，则可以将其与该列连接。所以我做了一个ordinal_position到constraint_column_usage如下所示。

我无法确认手动创建的ordinal_position与key_column_usage的顺序完全相同。但我检查了一下，至少在我的箱子里是完全一样的顺序。

SELECT
    tc.table_schema, 
    tc.constraint_name, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_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
    JOIN (select row_number() over (partition by table_schema, table_name, constraint_name order by row_num) ordinal_position,
                 table_schema, table_name, column_name, constraint_name
          from   (select row_number() over (order by 1) row_num, table_schema, table_name, column_name, constraint_name
                  from   information_schema.constraint_column_usage
                 ) t
         ) AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
      AND ccu.ordinal_position = kcu.ordinal_position
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'mytable'