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

注意事项:

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;

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

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

我认为你想要的和@ollyc写的很接近的是:

SELECT
tc.constraint_name, 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' AND ccu.table_name='YourTableNameHere';

这将列出所有使用指定表作为外键的表

$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;

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

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)

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