PostgreSQL：生成带动态WHERE NOT EXISTS条件的可重运行插入脚本

我完全理解你的痛点——Magnolia自动生成的schema没法手动整理唯一约束列，还要写可重复的INSERT不报错，用PostgreSQL的系统目录查询就能完美解决这个问题。

下面是一个可以直接运行的PostgreSQL查询，它会自动遍历所有表，为每个表生成包含WHERE NOT EXISTS条件的INSERT语句，条件会自动使用表的主键列；如果没有主键，就用非表达式的唯一索引列：

WITH table_unique_columns AS (
    -- 获取每个表的主键列
    SELECT
        c.relname AS table_name,
        array_agg(a.attname ORDER BY conkey) AS unique_columns,
        'PRIMARY KEY' AS constraint_type
    FROM pg_constraint con
    JOIN pg_class c ON con.conrelid = c.oid
    JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(con.conkey)
    WHERE con.contype = 'p'
    GROUP BY c.relname, con.contype

    UNION ALL

    -- 获取每个表的非表达式唯一索引列（排除已取过的主键）
    SELECT
        c.relname AS table_name,
        array_agg(a.attname ORDER BY idx.indkey) AS unique_columns,
        'UNIQUE INDEX' AS constraint_type
    FROM pg_index idx
    JOIN pg_class c ON idx.indrelid = c.oid
    JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum = ANY(idx.indkey)
    WHERE idx.indisunique = true
      AND idx.indisprimary = false
      -- 只保留基于实际列的唯一索引，排除表达式索引
      AND idx.indisexpression = false
    GROUP BY c.relname, idx.indisunique, idx.indisprimary
),
-- 为每个表选择优先级最高的唯一标识（主键优先，无主键则取第一个唯一索引）
table_preferred_unique AS (
    SELECT
        table_name,
        unique_columns,
        row_number() OVER (PARTITION BY table_name ORDER BY constraint_type) AS rn
    FROM table_unique_columns
)
SELECT
    format(
        'INSERT INTO %I (%s) VALUES (%s) WHERE NOT EXISTS (SELECT 1 FROM %I WHERE %s);',
        tp.table_name,
        array_to_string(array_agg(a.attname ORDER BY a.attnum), ', '),
        array_to_string(array_agg('%L' ORDER BY a.attnum), ', '), -- %L是值占位符，后续替换为实际值
        tp.table_name,
        array_to_string(array_agg(format('%I = %%L', uc), ORDER BY array_position(tp.unique_columns, uc)), ' AND ')
    ) AS insert_script
FROM table_preferred_unique tp
JOIN pg_attribute a ON a.attrelid = (SELECT oid FROM pg_class WHERE relname = tp.table_name)
-- 只处理普通业务列，排除系统列和已删除列
WHERE a.attnum > 0 AND NOT a.attisdropped
AND tp.rn = 1 -- 仅保留每个表的首选唯一约束
GROUP BY tp.table_name, tp.unique_columns
ORDER BY tp.table_name;

关键逻辑说明： #

1. table_unique_columns CTE：
   • 从pg_constraint系统表抓取所有主键列
   • 从pg_index抓取非主键的唯一索引列，过滤掉表达式类索引（确保是基于实际列的约束）
2. table_preferred_unique CTE：
   • 为每个表排序唯一约束优先级：主键>唯一索引，只保留优先级最高的那组列
3. 最终语句生成：
   • 用format()函数自动拼接完整INSERT语句，%I用于处理带特殊字符的表/列名，%L作为值的占位符
   • 自动生成WHERE NOT EXISTS的冲突判断条件，匹配选中的唯一列

使用提示： #

• 运行查询后会得到每个表的INSERT模板，把%L替换成你实际要插入的对应列值即可
• 如果某张表既无主键也无唯一索引，查询会自动跳过它（无法生成重复判断条件），这类表建议手动添加唯一约束或者单独处理

更高效的替代方案（PostgreSQL 9.5+） #

如果你的数据库版本支持，INSERT ... ON CONFLICT比WHERE NOT EXISTS性能更优，尤其适合并发场景。只需把最后生成语句的format部分改成：

format(
    'INSERT INTO %I (%s) VALUES (%s) ON CONFLICT (%s) DO NOTHING;',
    tp.table_name,
    array_to_string(array_agg(a.attname ORDER BY a.attnum), ', '),
    array_to_string(array_agg('%L' ORDER BY a.attnum), ', '),
    array_to_string(tp.unique_columns, ', ')
) AS insert_script

这个版本会在主键/唯一列冲突时直接跳过插入，和WHERE NOT EXISTS效果一致，但执行效率更高。

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