Oracle merge 与 PG新特性 UPSERT

        PostgreSQL 9.5于2016年1月7日正式发布，其中包含新特性“UPSERT”(INSERT, ON CONFLICT UPDATE), 即：插入数据，正常时写入，主键冲突时更新。 Oracle的 merge into  转化为 -- > PostgreSQL 的 INSERT INTO ...  ON CONFLICT .. update... 下面简单测试下： 一、Oracle数据库: 1、创建表t1、t2，插入数据： SQL> create table t1 (id int constraint idx_t1_id primary key,name varchar(20) constraint con_name not null); Table created. SQL> insert into t1 values(1,'jason'); 1 row created. SQL> insert into t1 values(2,'lucy'); 1 row created. SQL> commit; Commit complete. SQL> create table t2 (id int constraint idx_t2_id primary key,name varchar(20) constraint con_t2_name not null); Table created. SQL> SQL> insert into t2 values(1,'jason'); insert into t2 values(3,'jack'); insert into t2 values(4,'david'); insert into t2 values(5,'jacy'); insert into t2 values(6,'switty'); 1 row created. SQL> 1 row created. SQL> 1 row created. SQL> 1 row created. SQL> 1 row created. SQL> SQL> commit; Commit complete. SQL> select * from t1; ID NAME ---------- -------------------- 1 jason 2 lucy SQL> select * from t2; ID NAME ---------- -------------------- 1 jason 3 jack 4 david 5 jacy 6 switty 2、merge into 将表t2数据合并到t1中 SQL> MERGE INTO t1 USING (SELECT id,name FROM t2) t2 ON ( t1.id=t2.id) WHEN MATCHED THEN UPDATE SET T1.name= t2.name WHEN NOT MATCHED THEN INSERT (id,name) VALUES (t2.id,t2.name); 5 rows merged. SQL> select * from t1; ID NAME ---------- -------------------- 1 jason 2 lucy 3 jack 4 david 5 jacy 6 switty 6 rows selected. SQL> 二、PostgreSQL 数据库： 1、创建表t1、t2 : postgres=# create table t1 (id int constraint idx_t1_id primary key,name varchar(20) constraint con_name not null); CREATE TABLE postgres=# postgres=# postgres=# \d t1                         Table "public.t1" Column |         Type          | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- id     | integer               |           | not null | name   | character varying(20) |           | not null | Indexes:     "idx_t1_id" PRIMARY KEY, btree (id) postgres=# insert into t1 values(1,'jason'); INSERT 0 1 postgres=# insert into t1 values(2,'lucy'); INSERT 0 1 postgres=# select * from t1; id | name ----+-------   1 | jason   2 | lucy (2 rows) postgres=# create table t2 (id int constraint idx_t2_id primary key,name varchar(20) constraint con_t2_name not null); CREATE TABLE postgres=# insert into t2 values(1,'jason'); INSERT 0 1 postgres=# insert into t2 values(3,'jack'); INSERT 0 1 postgres=# insert into t2 values(4,'david'); INSERT 0 1 postgres=# insert into t2 values(5,'jacy'); INSERT 0 1 postgres=# insert into t2 values(6,'switty'); INSERT 0 1 postgres=# postgres=# postgres=# select * from t2; id |  name ----+--------   1 | jason   3 | jack   4 | david   5 | jacy   6 | switty (5 rows) 2、使用insert into ... ON CONFLICT do UPDATE ... postgres=# insert into t1 select id,name from t2 ON CONFLICT(id) do update set name=excluded.name where t1.id=excluded.id; INSERT 0 5 postgres=# select * from t1; id |  name ----+--------   2 | lucy   1 | jason   3 | jack   4 | david   5 | jacy   6 | switty (6 rows) 附： ------------------------------------------------------------------------------- 当有主键冲突时，也可以选择do nothing postgres=# insert into t1 select id,name from t2 ON CONFLICT(id) do update set name=excluded.name where t1.id=excluded.id; INSERT 0 5 postgres=# insert into t1 select id,name from t2 ON CONFLICT(id) do nothing; INSERT 0 0 postgres=#