CitusDB的upsert功能

postgresql9.5 版本支持 "UPSERT" 特性，这个特性支持 INSERT 语句定义 ON CONFLICT DO UPDATE/IGNORE 属性，当插入 SQL 违反约束的情况下定义动作，而不抛出错误。

环境

citus62_96（默认安装的postgresql9.6）

$ psql -V

psql (PostgreSQL) 9.6.3

测试

$ sudo -i -u postgres psql

postgres=# create table test(id int,name text,city text);

CREATE TABLE

# 创建唯一约束

postgres=# ALTER TABLE test ADD CONSTRAINT name_city UNIQUE (name, city);

ALTER TABLE

# 创建以name做hash的分片表

postgres=# select create_distributed_table('test','name');

 create_distributed_table

--------------------------

(1 row)

postgres=# insert into test values (1,'li','beijing');

INSERT 0 1

postgres=# INSERT INTO test (id, name, city) VALUES (1,'li','shanghai') on CONFLICT (name, city) DO UPDATE SET id = test.id + EXCLUDED.id;

INSERT 0 1

postgres=# select * from test;

 id | name |   city

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

  1 | li   | beijing

  1 | li   | shanghai

(2 rows)

postgres=# INSERT INTO test (id, name, city) VALUES (2,'li','shanghai') on CONFLICT (name, city) DO UPDATE SET id = test.id + EXCLUDED.id;

INSERT 0 1

postgres=# select * from test;

 id | name |   city

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

  1 | li   | beijing

  3 | li   | shanghai

(2 rows)

备注：定义 ON CONFLICT属性后，已有的用户只需更新id值（可以从上看出，只有2个约束name，city都相等时，才会更新id值，否则会增加一行）。EXCLUDED 为试图插入的值。

postgres=# create table test1(id int,name text,city text);

CREATE TABLE

postgres=# ALTER TABLE test1 ADD CONSTRAINT name UNIQUE (name);

ALTER TABLE

postgres=# select create_distributed_table('test1','name');

 create_distributed_table

--------------------------

(1 row)

postgres=# insert into test1 values (1,'li','beijing');

INSERT 0 1

postgres=# INSERT INTO test1 (id, name, city) VALUES (2,'wang','nanjing') on CONFLICT (name) DO UPDATE SET id = test1.id + EXCLUDED.id;

INSERT 0 1

postgres=# select * from test1;

 id | name |  city

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

  1 | li   | beijing

  2 | wang | nanjing

(2 rows)

postgres=# INSERT INTO test1 (id, name, city) VALUES (1,'wang','nan') on CONFLICT (name) DO UPDATE SET id = test1.id + EXCLUDED.id;

INSERT 0 1

postgres=# select * from test1;

 id | name |  city

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

  1 | li   | beijing

  3 | wang | nanjing

(2 rows)

备注：可以从上看出，只要满足约束name相同，id值被更新，其他字段city若不相同则数据库中还是原来city值。

巴特西

CitusDB UPSERT

CitusDB的upsert功能

环境

测试

最新文章

热门文章