以下是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;

PSQL就是这样做的，如果你用:

psql -E

它将准确地显示执行了哪些查询。在查找外键的情况下，它是:

SELECT conname,
  pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '16485' AND r.contype = 'f' ORDER BY 1

在这种情况下，16485是我正在寻找的表的oid -你可以通过将你的表名转换为regclass来获得它:

WHERE r.conrelid = 'mytable'::regclass

如果表名不是唯一的(或者是搜索路径中的第一个)，则对表名进行模式限定:

WHERE r.conrelid = 'myschema.mytable'::regclass

您可以通过information_schema表来实现这一点。例如:

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 information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';

现有的答案都没有给出我想要的结果。这是我的(庞大的)查询，用于查找有关外键的信息。

注意事项:

The expressions used to generate from_cols and to_cols could be vastly simplified on Postgres 9.4 and later using WITH ORDINALITY rather than the window-function-using hackery I'm using. Those same expressions are relying on the query planner not altering the returned order of results from UNNEST. I don't think it will, but I don't have any multiple-column foreign keys in my dataset to test with. Adding the 9.4 niceties eliminates this possibility altogether. The query itself requires Postgres 9.0 or later (8.x didn't allow ORDER BY in aggregate functions) Replace STRING_AGG with ARRAY_AGG if you want an array of columns rather than a comma-separated string.

-

SELECT
    c.conname AS constraint_name,
    (SELECT n.nspname FROM pg_namespace AS n WHERE n.oid=c.connamespace) AS constraint_schema,

    tf.name AS from_table,
    (
        SELECT STRING_AGG(QUOTE_IDENT(a.attname), ', ' ORDER BY t.seq)
        FROM
            (
                SELECT
                    ROW_NUMBER() OVER (ROWS UNBOUNDED PRECEDING) AS seq,
                    attnum
                FROM
                    UNNEST(c.conkey) AS t(attnum)
            ) AS t
            INNER JOIN pg_attribute AS a ON a.attrelid=c.conrelid AND a.attnum=t.attnum
    ) AS from_cols,

    tt.name AS to_table,
    (
        SELECT STRING_AGG(QUOTE_IDENT(a.attname), ', ' ORDER BY t.seq)
        FROM
            (
                SELECT
                    ROW_NUMBER() OVER (ROWS UNBOUNDED PRECEDING) AS seq,
                    attnum
                FROM
                    UNNEST(c.confkey) AS t(attnum)
            ) AS t
            INNER JOIN pg_attribute AS a ON a.attrelid=c.confrelid AND a.attnum=t.attnum
    ) AS to_cols,

    CASE confupdtype WHEN 'r' THEN 'restrict' WHEN 'c' THEN 'cascade' WHEN 'n' THEN 'set null' WHEN 'd' THEN 'set default' WHEN 'a' THEN 'no action' ELSE NULL END AS on_update,
    CASE confdeltype WHEN 'r' THEN 'restrict' WHEN 'c' THEN 'cascade' WHEN 'n' THEN 'set null' WHEN 'd' THEN 'set default' WHEN 'a' THEN 'no action' ELSE NULL END AS on_delete,
    CASE confmatchtype::text WHEN 'f' THEN 'full' WHEN 'p' THEN 'partial' WHEN 'u' THEN 'simple' WHEN 's' THEN 'simple' ELSE NULL END AS match_type,  -- In earlier postgres docs, simple was 'u'nspecified, but current versions use 's'imple.  text cast is required.

    pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM
    pg_catalog.pg_constraint AS c
    INNER JOIN (
        SELECT pg_class.oid, QUOTE_IDENT(pg_namespace.nspname) || '.' || QUOTE_IDENT(pg_class.relname) AS name
        FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
    ) AS tf ON tf.oid=c.conrelid
    INNER JOIN (
        SELECT pg_class.oid, QUOTE_IDENT(pg_namespace.nspname) || '.' || QUOTE_IDENT(pg_class.relname) AS name
        FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid
    ) AS tt ON tt.oid=c.confrelid
WHERE c.contype = 'f' ORDER BY 1;

从最流行的答案改进查询

因为对于postgresql 12+ information_schema是非常慢的

它帮助了我:

SELECT sh.nspname AS table_schema,
  tbl.relname AS table_name,
  col.attname AS column_name,
  referenced_sh.nspname AS foreign_table_schema,
  referenced_tbl.relname AS foreign_table_name,
  referenced_field.attname AS foreign_column_name
FROM pg_constraint c
    INNER JOIN pg_namespace AS sh ON sh.oid = c.connamespace
    INNER JOIN (SELECT oid, unnest(conkey) as conkey FROM pg_constraint) con ON c.oid = con.oid
    INNER JOIN pg_class tbl ON tbl.oid = c.conrelid
    INNER JOIN pg_attribute col ON (col.attrelid = tbl.oid AND col.attnum = con.conkey)
    INNER JOIN pg_class referenced_tbl ON c.confrelid = referenced_tbl.oid
    INNER JOIN pg_namespace AS referenced_sh ON referenced_sh.oid = referenced_tbl.relnamespace
    INNER JOIN (SELECT oid, unnest(confkey) as confkey FROM pg_constraint) conf ON c.oid = conf.oid
    INNER JOIN pg_attribute referenced_field ON (referenced_field.attrelid = c.confrelid AND referenced_field.attnum = conf.confkey)
WHERE c.contype = 'f'

在PostgreSQL提示符上发出\d+ tablename，除了显示表列的数据类型外，它还会显示索引和外键。