PostgreSQL Oracle 兼容性之 - insert all into … 多表写入

背景

Oracle支持 insert all 的语法，同时往多个表插入。

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#i2125362

PostgreSQL可以通过CTE语法达到同样的效果。

https://www.postgresql.org/docs/11/static/queries-with.html

Oracle insert all into 例子

create table a(id int, c1 int, c2 int);  
  
create table b(id int, c1 int, c2 int);  
  
  
set autotrace on;  

insert all   
  into a (id,c1) values (id, col1)  
  into b (id,c2) values (id, col2)  
select rownum as id, trunc(dbms_random.value(0, 100)) as col1, trunc(dbms_random.value(0, 100)) as col2 from dual connect by level <=10000;  
  
20000 rows created.  

Execution Plan  
----------------------------------------------------------  
Plan hash value: 702343910  
  
-----------------------------------------------------------------------------------------  
| Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
-----------------------------------------------------------------------------------------  
|   0 | INSERT STATEMENT                 |      |     1 |    39 |     2   (0)| 00:00:01 |  
|   1 |  MULTI-TABLE INSERT              |      |       |       |            |          |  
|   2 |   INTO                           | A    |       |       |            |          |  
|   3 |   INTO                           | B    |       |       |            |          |  
|   4 |    VIEW                          |      |     1 |    39 |     2   (0)| 00:00:01 |  
|   5 |     COUNT                        |      |       |       |            |          |  
|*  6 |      CONNECT BY WITHOUT FILTERING|      |       |       |            |          |  
|   7 |       FAST DUAL                  |      |     1 |       |     2   (0)| 00:00:01 |  
-----------------------------------------------------------------------------------------  
  
Predicate Information (identified by operation id):  
---------------------------------------------------  
  
   6 - filter(LEVEL<=10000)  
  
  
Statistics  
----------------------------------------------------------  
        531  recursive calls  
        661  db block gets  
        205  consistent gets  
          0  physical reads  
     392344  redo size  
        823  bytes sent via SQL*Net to client  
        932  bytes received via SQL*Net from client  
          3  SQL*Net roundtrips to/from client  
          6  sorts (memory)  
          0  sorts (disk)  
      20000  rows processed  

PostgreSQL CTE例子

create table a(id int, c1 int, c2 int);  
  
create table b(id int, c1 int, c2 int);  

insert all   
  into a (id,c1) values (id, col1)  
  into b (id,c2) values (id, col2)  
select id, random() col1, random() col2 from generate_series(1,10000) t(id);  
  
ERROR:  42601: syntax error at or near "all"  
LINE 1: insert all   
               ^  
LOCATION:  scanner_yyerror, scan.l:1087  

with tmp as (select id, random() col1, random() col2 from generate_series(1,10000) t(id)),  
ins1 as (insert into a (id,c1) select id,col1 from tmp)  
insert into b (id,c2) select id,col2 from tmp;  
  
INSERT 0 10000  

postgres=# select count(*) from a;  
 count   
-------  
 10000  
(1 row)  
  
postgres=# select count(*) from b;  
 count   
-------  
 10000  
(1 row)  
  
  
postgres=# select * from a limit 10;  
 id | c1 | c2   
----+----+----  
  1 |  0 |     
  2 |  1 |     
  3 |  1 |     
  4 |  0 |     
  5 |  1 |     
  6 |  0 |     
  7 |  1 |     
  8 |  0 |     
  9 |  0 |     
 10 |  0 |     
(10 rows)  
  
postgres=# select * from b limit 10;  
 id | c1 | c2   
----+----+----  
  1 |    |  0  
  2 |    |  0  
  3 |    |  1  
  4 |    |  1  
  5 |    |  0  
  6 |    |  1  
  7 |    |  1  
  8 |    |  1  
  9 |    |  0  
 10 |    |  0  
(10 rows)  

digoal’s 大量PostgreSQL文章入口