postgresql upsert 使用范例

--pg支持 update from 语法 

postgres=# create table t1 as select n id,'rudy'||n as name from generate_series(1,3) n;

SELECT 10

postgres=# select * from t1;

 id |  name  

----+--------

  1 | rudy1

  2 | rudy2

  3 | rudy3

(3 rows)

--创建表

postgres=# create table t2 as select * from t1 where 1=0;

SELECT 0

postgres=# insert into t2 values(1,'rudy'),(1,'rudy2'),(1,'rudy3');

INSERT 0 3

postgres=# select * from t1,t2 where t1.id=t2.id;

 id | name  | id | name  

----+-------+----+-------

  1 | rudy1 |  1 | rudy

  1 | rudy1 |  1 | rudy2

  1 | rudy1 |  1 | rudy3

(3 rows)

--注意此时的 update from 类似于对t1表做了distint操作，故只更新一条 

postgres=# update t1 set name=t2.name from t2 where t1.id=t2.id;   

UPDATE 1

postgres=# select distinct t1.id,t1.name from t1,t2 where t1.id=t2.id;                         

 id | name 

----+------

  1 | rudy