扩展到ollyc配方:

CREATE VIEW foreign_keys_view AS
SELECT
    tc.table_name, kcu.column_name,
    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
    JOIN information_schema.constraint_column_usage 
        AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY';

然后:

SELECT * FROM foreign_keys_view WHERE table_name='YourTableNameHere';

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

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

我升级了@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'

另一种方式:

WITH foreign_keys AS (
    SELECT
      conname,
      conrelid,
      confrelid,
      unnest(conkey)  AS conkey,
      unnest(confkey) AS confkey
    FROM pg_constraint
    WHERE contype = 'f' -- AND confrelid::regclass = 'your_table'::regclass
)
-- if confrelid, conname pair shows up more than once then it is multicolumn foreign key
SELECT fk.conname as constraint_name,
       fk.confrelid::regclass as referenced_table, af.attname as pkcol,
       fk.conrelid::regclass as referencing_table, a.attname as fkcol
FROM foreign_keys fk
JOIN pg_attribute af ON af.attnum = fk.confkey AND af.attrelid = fk.confrelid
JOIN pg_attribute a ON a.attnum = conkey AND a.attrelid = fk.conrelid
ORDER BY fk.confrelid, fk.conname
;

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

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的回复提供了更多的细节。