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;