这是我目前使用的，它将列出一个表，它的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')

最快的验证直接在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"

我自己的贡献。目标是备份所有外键的定义:

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

你可以使用PostgreSQL系统目录。也许您可以查询pg_constraint来请求外键。 您还可以使用信息模式

只需替换'您的表名'在下面的查询与您的表名。

简短但贴心的赞，如果对你有用的话。

select * from information_schema.key_column_usage
where constraint_catalog=current_catalog and table_name='your_table_name'
and position_in_unique_constraint notnull;

SELECT conrelid::regclass AS table_name, 
       conname AS foreign_key, 
       pg_get_constraintdef(oid) 
FROM   pg_constraint 
WHERE  contype = 'f' 
AND    connamespace = 'public'::regnamespace   
ORDER  BY conrelid::regclass::text, contype DESC;

https://soft-builder.com/how-to-list-all-foreign-keys-in-postgresql-database