Postgresql使用WITH和Ignore erros在多个表中插入

基础概念

WITH (Common Table Expressions, CTEs): CTEs 是一种临时的结果集，可以在查询中被引用多次。它们可以提高查询的可读性和性能，特别是在处理复杂查询时。

IGNORE ERRORS: 在 PostgreSQL 中，IGNORE ERRORS 是一种处理插入操作中错误的方法。当插入数据时，如果某些行因为违反约束（如唯一性约束）而无法插入，使用 IGNORE ERRORS 可以忽略这些错误，继续插入其他行。

优势

• CTEs:
  • 提高查询的可读性。
  • 允许在查询中重复使用子查询结果。
  • 可以用于递归查询。
• IGNORE ERRORS:
  • 在批量插入数据时，可以忽略一些预期的错误，提高数据插入的效率。
  • 减少因为单个错误导致整个插入操作失败的风险。

类型

• CTEs:
  • 普通 CTEs: 用于简单的子查询。
  • 递归 CTEs: 用于处理层次结构数据或递归查询。
• IGNORE ERRORS:
  • 在 INSERT 语句中使用 ON CONFLICT DO NOTHING 或 ON CONFLICT DO UPDATE 结合 IGNORE ERRORS。

应用场景

• CTEs:
  • 复杂查询的分解。
  • 递归查询，如组织结构树。
• IGNORE ERRORS:
  • 批量插入数据时，忽略一些预期的唯一性约束错误。
  • 数据迁移或数据同步时，处理目标表中已存在的数据。

示例代码

假设我们有两个表 table1 和 table2，我们希望在插入数据时忽略唯一性约束错误。

-- 创建示例表
CREATE TABLE table1 (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE
);

CREATE TABLE table2 (
    id SERIAL PRIMARY KEY,
    description TEXT UNIQUE
);

-- 插入数据，忽略唯一性约束错误
WITH data_to_insert AS (
    SELECT 1 AS id, 'Alice' AS name, 'Description 1' AS description
    UNION ALL
    SELECT 2 AS id, 'Bob' AS name, 'Description 2' AS description
    UNION ALL
    SELECT 3 AS id, 'Alice' AS name, 'Description 3' AS description -- 重复的 name 和 description
)
INSERT INTO table1 (id, name)
SELECT id, name FROM data_to_insert
ON CONFLICT (name) DO NOTHING;

INSERT INTO table2 (id, description)
SELECT id, description FROM data_to_insert
ON CONFLICT (description) DO NOTHING;

参考链接

• PostgreSQL CTEs
• PostgreSQL INSERT with ON CONFLICT

常见问题及解决方法

问题: 使用 IGNORE ERRORS 时，某些行仍然无法插入。 原因: 可能是因为违反了其他类型的约束，如外键约束或检查约束。 解决方法: 检查并确保所有约束都符合插入的数据，或者调整约束条件。

问题: CTEs 导致查询性能下降。 原因: 复杂的 CTEs 可能会导致查询优化器难以生成高效的执行计划。 解决方法: 简化 CTEs 或使用临时表替代 CTEs，以提高查询性能。

通过以上方法，可以有效地处理 PostgreSQL 中的多表插入操作，并解决常见的插入错误问题。