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
    '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

这是我目前使用的，它将列出一个表，它的fkey约束[删除表子句，它将列出当前目录中的所有表]:

SELECT

    current_schema() AS "schema",
    current_catalog AS "database",
    "pg_constraint".conrelid::regclass::text AS "primary_table_name",
    "pg_constraint".confrelid::regclass::text AS "foreign_table_name",

    (
        string_to_array(
            (
                string_to_array(
                    pg_get_constraintdef("pg_constraint".oid),
                    '('
                )
            )[2],
            ')'
        )
    )[1] AS "foreign_column_name",

    "pg_constraint".conindid::regclass::text AS "constraint_name",

    TRIM((
        string_to_array(
            pg_get_constraintdef("pg_constraint".oid),
            '('
        )
    )[1]) AS "constraint_type",

    pg_get_constraintdef("pg_constraint".oid) AS "constraint_definition"

FROM pg_constraint AS "pg_constraint"

JOIN pg_namespace AS "pg_namespace" ON "pg_namespace".oid = "pg_constraint".connamespace

WHERE
    --fkey and pkey constraints
    "pg_constraint".contype IN ( 'f', 'p' )
    AND
    "pg_namespace".nspname = current_schema()
    AND
    "pg_constraint".conrelid::regclass::text IN ('whatever_table_name')

$1 ('my_schema')是模式，$2 ('my_table')是表名:

SELECT ss.conname constraint_name, a.attname column_name, ss.refnamespace fk_table_schema, ss.reflname fk_table_name, af.attname fk_column_name
    FROM  pg_attribute a, pg_attribute af,
        (SELECT r.oid roid, c.conname, rf.relname reflname, information_schema._pg_expandarray(c.conkey) x,
                nrf.nspname refnamespace, rf.oid rfoid, information_schema._pg_expandarray(cf.confkey) xf
            FROM pg_namespace nr, pg_class r, pg_constraint c,
                pg_namespace nrf, pg_class rf, pg_constraint cf
            WHERE nr.oid = r.relnamespace
                AND r.oid = c.conrelid
                AND rf.oid = cf.confrelid
                AND c.conname = cf.conname
                AND nrf.oid = rf.relnamespace
                AND nr.nspname = $1
                AND r.relname = $2) ss
    WHERE ss.roid = a.attrelid AND a.attnum = (ss.x).x AND NOT a.attisdropped
        AND ss.rfoid = af.attrelid AND af.attnum = (ss.xf).x AND NOT af.attisdropped
    ORDER BY ss.conname, a.attname;

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

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

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