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

注意事项:

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;

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

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

注意事项:

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提示符上发出\d+ tablename，除了显示表列的数据类型外，它还会显示索引和外键。

我写了一个解决方案，喜欢和经常使用。代码在http://code.google.com/p/pgutils/。看这些小疙瘩。foreign_keys视图。

不幸的是，输出太冗长，这里不包括。但是，你可以在数据库的公共版本上尝试一下，就像这样:

$ psql -h unison-db.org -U PUBLIC -d unison -c 'select * from pgutils.foreign_keys;

这至少适用于8.3版本。如果需要的话，我预计会在未来几个月内对其进行更新。

莉丝