PostgreSQL merge insert(upsert/insert into on conflict) ÈçºÎÇø·ÖÊý¾ÝÊÇINSERT»¹ÊÇUPDATE

±³¾°
ʹÓÃinsert into on conflict updateÓï·¨£¬¿ÉÒÔÖ§³ÖUPSERTµÄ¹¦ÄÜ£¬µ«Êǵ½µ×ÕâÌõSQLÊDzåÈëµÄ»¹ÊǸüеÄÄØ£¿ÈçºÎÅжÏ

ͨ¹ýxmax×ֶεÄÖµÊÇ·ñ²»Îª0£¬¿ÉÒÔÅжϣ¬Èç¹ûÊÇUPDATE£¬XMAXÀïÃæ»áÌî³ä¸üÐÂÊÂÎñºÅ¡£

×¢ÒâÖ±½ÓÓÃUPDATEÓï¾ä¸üеĻ°£¬XMAX»áдÈë0£¬ÒòΪÊÇа汾£¬¶øÀÏ°æ±¾ÉÏXMAX»áÌîÈë¸üÐÂÊÂÎñºÅ¡£

Àý×Ó
1 insert on conflict
postgres=# create table t(id int primary key, info text, crt_time timestamp);
CREATE TABLE
postgres=# insert into t values (1,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time returning xmax;

xmax

0  

(1 row)

INSERT 0 1
postgres=# insert into t values (1,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time returning xmax;

xmax

160369640
(1 row)

INSERT 0 1
postgres=# select xmin,xmax,* from t;

(1 row)

postgres=# insert into t values (1,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time returning xmax;

xmax

160369641
(1 row)

INSERT 0 1
postgres=# select xmin,xmax,* from t;

(1 row)

postgres=# insert into t values (2,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time returning xmax;

xmax

0  

(1 row)

INSERT 0 1
postgres=# select xmin,xmax,* from t;

(2 rows)

postgres=# select ctid,xmin,xmax,* from t;

(2 rows)

postgres=# insert into t values (2,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time returning ctid,xmin,xmax,*;

(1 row)

INSERT 0 1
postgres=# select ctid,xmin,xmax,* from t;

(2 rows)
2 Ö±½Óupdate
postgres=# update t set info='a' returning xmin,xmax,ctid,*;

(2 rows)

UPDATE 2
3 update »Ø¹ö
postgres=# begin;
BEGIN
postgres=# update t set info='a' returning xmin,xmax,ctid,*;

(2 rows)

UPDATE 2
postgres=# rollback;
ROLLBACK
postgres=# select ctid,xmin,xmax,* from t;

(2 rows)
4 delete »Ø¹ö
postgres=# begin;
BEGIN
postgres=# delete from t returning ctid,xmin,xmax,*;

(2 rows)

DELETE 2
postgres=# rollback;
ROLLBACK
postgres=# select ctid,xmin,xmax,* from t;

(2 rows)
С½á
1¡¢insert into on conflict do update£¬·µ»Øxmax²»µÈÓÚ0£¬±íʾupdate£¬µÈÓÚ0±íʾinsert¡£

2¡¢Ö±½Óupdate£¬²¢Ìá½»£¬Ìá½»µÄ¼Ç¼ÉÏxmaxΪ0¡£

3¡¢Ö±½Óupdate£¬²¢»Ø¹ö£¬ÀÏ°æ±¾ÉϵÄXMAX²»Îª0£¬±íʾ¸üиÃÐеÄÊÂÎñºÅ¡£

4¡¢Ö±½ÓDELETE£¬²¢»Ø¹ö£¬ÀÏ°æ±¾ÉϵÄXMAX²»Îª0£¬±íʾɾ³ý¸ÃÐеÄÊÂÎñºÅ¡£

ctid±íʾÐкÅ, xmin±íʾINSERT¸Ã¼Ç¼µÄÊÂÎñºÅ£¬xmax±íʾɾ³ý¸Ã¼Ç¼(updateʵ¼ÊÉÏÊÇɾ³ýÀÏ°æ±¾ÐÂÔöа汾£¬ËùÒÔÀÏ°æ±¾ÉÏxmaxÓÐÖµ)µÄÊÂÎñºÅ¡£

²Î¿¼
¡¶Greenplum & PostgreSQL UPSERT udf ʵÏÖ - 2 batchÅúÁ¿Ä£Ê½¡·

¡¶Greenplum & PostgreSQL UPSERT udf ʵÏÖ - 1 µ¥ÐÐģʽ¡·

¡¶PostgreSQL ¶àÖغ¬ÒåÊý×é¼ìË÷ÓëÌõ¼þ¹ýÂË (±êÇ©1:ÊôÐÔ, ±êÇ©n:ÊôÐÔ) - °üÀ¨UPSERT²Ù×÷ÈçºÎÐÞ¸ÄÊý×é¡¢×·¼ÓÊý×éÔªËØ¡·

¡¶HTAPÊý¾Ý¿â PostgreSQL ³¡¾°ÓëÐÔÄܲâÊÔÖ® 22 - (OLTP) merge insert|upsert|insert on conflict|ºÏ²¢Ð´Èë¡·

¡¶PostgreSQL upsert¹¦ÄÜ(insert on conflict do)µÄÓ÷¨¡·

¡¶PostgreSQL ÈçºÎʵÏÖupsertÓëоÉÊý¾Ý×Ô¶¯·ÖÀë¡·

¡¶[תÔØ]postgresql 9.5°æ±¾Ö®Ç°ÊµÏÖupsert¹¦ÄÜ¡·

¡¶upsert - PostgreSQL 9.4 pending patch : INSERT...ON DUPLICATE KEY IGNORE¡·