如何用单条SQL查询对比两个PostgreSQL数据库的表（含PK、FK）

单库表结构查询（含PK/FK） #

以下查询返回所有用户自定义基表的列信息，包含主键、外键的完整约束详情，允许数据重复（如同一列属于多个约束时生成多行）：

SELECT
    t.table_catalog AS db_name,
    t.table_schema AS schema_name,
    t.table_name,
    c.column_name,
    c.data_type,
    c.is_nullable,
    c.character_maximum_length,
    c.numeric_precision,
    c.numeric_scale,
    -- 主键约束信息
    tc_pk.constraint_name AS pk_constraint_name,
    CASE WHEN kcu_pk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END AS is_primary_key,
    -- 外键约束信息
    tc_fk.constraint_name AS fk_constraint_name,
    CASE WHEN kcu_fk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END AS is_foreign_key,
    fkcu.referenced_table_schema AS referenced_schema_name,
    fkcu.referenced_table_name AS referenced_table_name,
    fkcu.referenced_column_name AS referenced_column_name
FROM
    information_schema.tables t
JOIN
    information_schema.columns c 
        ON t.table_catalog = c.table_catalog 
        AND t.table_schema = c.table_schema 
        AND t.table_name = c.table_name
LEFT JOIN
    information_schema.table_constraints tc_pk 
        ON t.table_catalog = tc_pk.table_catalog 
        AND t.table_schema = tc_pk.table_schema 
        AND t.table_name = tc_pk.table_name
        AND tc_pk.constraint_type = 'PRIMARY KEY'
LEFT JOIN
    information_schema.key_column_usage kcu_pk 
        ON tc_pk.constraint_catalog = kcu_pk.constraint_catalog 
        AND tc_pk.constraint_schema = kcu_pk.constraint_schema 
        AND tc_pk.constraint_name = kcu_pk.constraint_name
        AND c.column_name = kcu_pk.column_name
LEFT JOIN
    information_schema.table_constraints tc_fk 
        ON t.table_catalog = tc_fk.table_catalog 
        AND t.table_schema = tc_fk.table_schema 
        AND t.table_name = tc_fk.table_name
        AND tc_fk.constraint_type = 'FOREIGN KEY'
LEFT JOIN
    information_schema.key_column_usage kcu_fk 
        ON tc_fk.constraint_catalog = kcu_fk.constraint_catalog 
        AND tc_fk.constraint_schema = kcu_fk.constraint_schema 
        AND tc_fk.constraint_name = kcu_fk.constraint_name
        AND c.column_name = kcu_fk.column_name
LEFT JOIN
    information_schema.constraint_column_usage fkcu 
        ON tc_fk.constraint_catalog = fkcu.constraint_catalog 
        AND tc_fk.constraint_schema = fkcu.constraint_schema 
        AND tc_fk.constraint_name = fkcu.constraint_name
WHERE
    t.table_type = 'BASE TABLE'
    AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY
    t.table_schema, t.table_name, c.ordinal_position;

跨库对比查询（单条SQL） #

如果要直接对比两个数据库（假设目标库名为target_db，需先安装dblink扩展），可通过以下查询合并两个库的结构数据，方便对比：

-- 确保dblink扩展已安装
CREATE EXTENSION IF NOT EXISTS dblink;

SELECT
    'source_db' AS db_identifier,
    *
FROM (
    -- 单库查询逻辑（去掉db_name字段）
    SELECT
        t.table_schema AS schema_name,
        t.table_name,
        c.column_name,
        c.data_type,
        c.is_nullable,
        c.character_maximum_length,
        c.numeric_precision,
        c.numeric_scale,
        tc_pk.constraint_name AS pk_constraint_name,
        CASE WHEN kcu_pk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END AS is_primary_key,
        tc_fk.constraint_name AS fk_constraint_name,
        CASE WHEN kcu_fk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END AS is_foreign_key,
        fkcu.referenced_table_schema AS referenced_schema_name,
        fkcu.referenced_table_name AS referenced_table_name,
        fkcu.referenced_column_name AS referenced_column_name
    FROM
        information_schema.tables t
    JOIN
        information_schema.columns c 
            ON t.table_catalog = c.table_catalog 
            AND t.table_schema = c.table_schema 
            AND t.table_name = c.table_name
    LEFT JOIN
        information_schema.table_constraints tc_pk 
            ON t.table_catalog = tc_pk.table_catalog 
            AND t.table_schema = tc_pk.table_schema 
            AND t.table_name = tc_pk.table_name
            AND tc_pk.constraint_type = 'PRIMARY KEY'
    LEFT JOIN
        information_schema.key_column_usage kcu_pk 
            ON tc_pk.constraint_catalog = kcu_pk.constraint_catalog 
            AND tc_pk.constraint_schema = kcu_pk.constraint_schema 
            AND tc_pk.constraint_name = kcu_pk.constraint_name
            AND c.column_name = kcu_pk.column_name
    LEFT JOIN
        information_schema.table_constraints tc_fk 
            ON t.table_catalog = tc_fk.table_catalog 
            AND t.table_schema = tc_fk.table_schema 
            AND t.table_name = tc_fk.table_name
            AND tc_fk.constraint_type = 'FOREIGN KEY'
    LEFT JOIN
        information_schema.key_column_usage kcu_fk 
            ON tc_fk.constraint_catalog = kcu_fk.constraint_catalog 
            AND tc_fk.constraint_schema = kcu_fk.constraint_schema 
            AND tc_fk.constraint_name = kcu_fk.constraint_name
            AND c.column_name = kcu_fk.column_name
    LEFT JOIN
        information_schema.constraint_column_usage fkcu 
            ON tc_fk.constraint_catalog = fkcu.constraint_catalog 
            AND tc_fk.constraint_schema = fkcu.constraint_schema 
            AND tc_fk.constraint_name = fkcu.constraint_name
    WHERE
        t.table_type = 'BASE TABLE'
        AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
) source_data

UNION ALL

SELECT
    'target_db' AS db_identifier,
    *
FROM dblink('dbname=target_db', $$
    -- 目标库的同结构查询逻辑
    SELECT
        t.table_schema AS schema_name,
        t.table_name,
        c.column_name,
        c.data_type,
        c.is_nullable,
        c.character_maximum_length,
        c.numeric_precision,
        c.numeric_scale,
        tc_pk.constraint_name AS pk_constraint_name,
        CASE WHEN kcu_pk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END AS is_primary_key,
        tc_fk.constraint_name AS fk_constraint_name,
        CASE WHEN kcu_fk.column_name IS NOT NULL THEN 'YES' ELSE 'NO' END AS is_foreign_key,
        fkcu.referenced_table_schema AS referenced_schema_name,
        fkcu.referenced_table_name AS referenced_table_name,
        fkcu.referenced_column_name AS referenced_column_name
    FROM
        information_schema.tables t
    JOIN
        information_schema.columns c 
            ON t.table_catalog = c.table_catalog 
            AND t.table_schema = c.table_schema 
            AND t.table_name = c.table_name
    LEFT JOIN
        information_schema.table_constraints tc_pk 
            ON t.table_catalog = tc_pk.table_catalog 
            AND t.table_schema = tc_pk.table_schema 
            AND t.table_name = tc_pk.table_name
            AND tc_pk.constraint_type = 'PRIMARY KEY'
    LEFT JOIN
        information_schema.key_column_usage kcu_pk 
            ON tc_pk.constraint_catalog = kcu_pk.constraint_catalog 
            AND tc_pk.constraint_schema = kcu_pk.constraint_schema 
            AND tc_pk.constraint_name = kcu_pk.constraint_name
            AND c.column_name = kcu_pk.column_name
    LEFT JOIN
        information_schema.table_constraints tc_fk 
            ON t.table_catalog = tc_fk.table_catalog 
            AND t.table_schema = tc_fk.table_schema 
            AND t.table_name = tc_fk.table_name
            AND tc_fk.constraint_type = 'FOREIGN KEY'
    LEFT JOIN
        information_schema.key_column_usage kcu_fk 
            ON tc_fk.constraint_catalog = kcu_fk.constraint_catalog 
            AND tc_fk.constraint_schema = kcu_fk.constraint_schema 
            AND tc_fk.constraint_name = kcu_fk.constraint_name
            AND c.column_name = kcu_fk.column_name
    LEFT JOIN
        information_schema.constraint_column_usage fkcu 
            ON tc_fk.constraint_catalog = fkcu.constraint_catalog 
            AND tc_fk.constraint_schema = fkcu.constraint_schema 
            AND tc_fk.constraint_name = fkcu.constraint_name
    WHERE
        t.table_type = 'BASE TABLE'
        AND t.table_schema NOT IN ('pg_catalog', 'information_schema')
$$) AS target_data(
    schema_name text,
    table_name text,
    column_name text,
    data_type text,
    is_nullable text,
    character_maximum_length integer,
    numeric_precision integer,
    numeric_scale integer,
    pk_constraint_name text,
    is_primary_key text,
    fk_constraint_name text,
    is_foreign_key text,
    referenced_schema_name text,
    referenced_table_name text,
    referenced_column_name text
)
ORDER BY
    schema_name, table_name, column_name, db_identifier;

关键说明 #

• 结果允许重复行：同一列同时属于主键和外键、或表有多个主键列时，会生成多条记录，满足非规范化的对比需求。
• 跨库查询需确保当前用户有权限访问目标库，且dblink扩展已安装。
• 自动排除系统表（pg_catalog、information_schema），仅返回用户自定义基表。

内容的提问来源于stack exchange，提问作者JukeboxHero