PostgreSQL 实现 Oracle 多表插入语句

Oracle 数据库提供了一个多表插入功能，也就是 INSERT ALL 语句。这个功能可以方便数据仓库中的 ETL 操作，基于不同逻辑将数据插入一个或者多个不同的表中。

PostgreSQL 被称为开源领域的 Oracle，虽然没有提供 INSERT ALL 语句，但是可以通过递归查询（WITH 语句）实现类似的功能。

首先创建一个源数据表和三个目标表：

CREATE TABLE src_table(
  id INTEGER NOT NULL PRIMARY KEY,
  name VARCHAR(10) NOT NULL
);
INSERT INTO src_table VALUES (1, '张三');
INSERT INTO src_table VALUES (2, '李四');
INSERT INTO src_table VALUES (3, '王五');

CREATE TABLE tgt_t1 AS
SELECT * FROM src_table WHERE FALSE;

CREATE TABLE tgt_t2 AS
SELECT * FROM src_table WHERE FALSE;

CREATE TABLE tgt_t3 AS
SELECT * FROM src_table WHERE FALSE;

无条件的多表插入语句

Oracle 中的 INSERT ALL 语句可以将数据输入插入一个或者多个表中，因此也被称为多表插入语句。第一种形式的 INSERT ALL 语句是无条件的插入语句，源数据中的每一行数据都会被插入到每个目标表中。

-- Oracle 语法
INSERT ALL
  INTO tgt_t1(id, name) VALUES(id, name)
  INTO tgt_t2(id, name) VALUES(id, name)
  INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

执行以上多表插入语句之后，三个目标表中都生成了 3 条记录。

PostgreSQL 可以使用以下 WITH 语句实现无条件的多表插入语句：

WITH s AS (
  SELECT id, name
  FROM src_table
)
,t1 AS (
  INSERT INTO tgt_t1(id, name)
  SELECT *
  FROM s
),
t2 AS (
  INSERT INTO tgt_t2(id, name)
  SELECT *
  FROM s
),
t3 AS (
  INSERT INTO tgt_t3(id, name)
  SELECT *
  FROM s
)
SELECT 1;

SELECT * FROM tgt_t1;
ID|NAME  |
--|------|
 1|张三  |
 2|李四  |
 3|王五  |

SELECT * FROM tgt_t2;
ID|NAME  |
--|------|
 1|张三  |
 2|李四  |
 3|王五  |

SELECT * FROM tgt_t3;
ID|NAME  |
--|------|
 1|张三  |
 2|李四  |
 3|王五  |

其中，s 代表了数据源，t1 实现了 tgt_t1 的数据插入，依此类推。最终通过一个 WITH 语句实现了三个表的插入操作。

💡虽然很多数据库都提供了通用表表达式（WITH 语句），但是目前只有 PostgreSQL 可以将 DML 语句的结果定义为一个通用表表达式。

有条件的多表插入语句

Oracle 提供的另一种形式的 INSERT ALL 语句是有条件的插入语句，可以将满足不同条件的数据插入不同的表中。例如：

-- Oracle 语法
INSERT ALL
  WHEN id <= 1 THEN
    INTO tgt_t1(id, name) VALUES(id, name)
  WHEN id BETWEEN 1 AND 2 THEN
    INTO tgt_t2(id, name) VALUES(id, name)
  ELSE
    INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

以上语句在 tgt_t1 中插入了 1 条数据，因为 id 小于等于 1 的记录只有 1 个。tgt_t2 中插入了 2 条数据，包括 id 等于 1 的记录。也就是说，前面的 WHEN 子句不会影响后续的条件判断，每个条件都会单独进行判断。tgt_t3 中插入了 1 条数据，ELSE 分支只会插入不满足前面所有条件的数据。

PostgreSQL 实现以上插入逻辑的方法如下：

WITH s AS (
  SELECT id, name
  FROM src_table
)
,t1 AS (
  INSERT INTO tgt_t1(id, name)
  SELECT *
  FROM s
  WHERE id <= 1
  RETURNING id
),
t2 AS (
  INSERT INTO tgt_t2(id, name)
  SELECT *
  FROM s
  WHERE id BETWEEN 1 AND 2
  RETURNING id
),
t3 AS (
  INSERT INTO tgt_t3(id, name)
  SELECT *
  FROM s
  WHERE id NOT IN (SELECT id FROM t1
                   UNION ALL 
                   SELECT id FROM t2)
)
SELECT 1;

t1 定义中的查询条件限制了 id 小于等于 1 的记录，同时使用 RETURNING 子句返回了插入 t1 中的所有 id；t2 也采用了类似的处理方式；t3 通过子查询插入了不满足 t1 和 t2 插入条件的其他数据。

💡RETURNING 子句可以返回 DML 语句操作修改的数据，它也是 PostgreSQL 专有的功能。

有条件的 INSERT FIRST 语句

Oracle 还提供了有条件的 INSERT FIRST 语句，它的原理和 CASE 表达式类似，只会执行第一个满足条件的插入语句，然后继续处理源数据中的其他记录。例如：

INSERT FIRST
  WHEN id <= 1 THEN
    INTO tgt_t1(id, name) VALUES(id, name)
  WHEN id BETWEEN 1 AND 2 THEN
    INTO tgt_t2(id, name) VALUES(id, name)
  ELSE
    INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;

以上语句和上一个示例的差别在于源数据中的每个记录只会插入一次，tgt_t2 中不会插入 id 等于 1 的数据。

PostgreSQL 模拟以上 INSERT FIRST 语句的方法如下：

WITH s AS (
  SELECT id, name
  FROM src_table
)
,t1 AS (
  INSERT INTO tgt_t1(id, name)
  SELECT *
  FROM s
  WHERE id <= 1
  RETURNING id
),
t2 AS (
  INSERT INTO tgt_t2(id, name)
  SELECT *
  FROM s
  WHERE id BETWEEN 1 AND 2
  AND id NOT IN (SELECT id FROM t1)
  RETURNING id
),
t3 AS (
  INSERT INTO tgt_t3(id, name)
  SELECT *
  FROM s
  WHERE id NOT IN (SELECT id FROM t1
                   UNION ALL 
                   SELECT id FROM t2)
)
SELECT 1;