选择的答案不为我工作，所以张贴我的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 

最快的验证直接在bash答案完全基于这个答案

IFS='' read -r -d '' sql_code << EOF_SQL_CODE
      SELECT
      o.oid
      , 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
      , ROW_NUMBER () OVER (ORDER BY o.oid) as rowid
      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 1=1
      AND o.contype = 'f'
      AND o.conrelid IN (SELECT oid FROM pg_class c WHERE c.relkind = 'r')
EOF_SQL_CODE

psql -d my_db -c "$sql_code"

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

注意事项:

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;

我认为你想要的和@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';

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

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

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的答案，目前在顶部。 我同意@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'