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=#