当前位置: 首页 > 工具软件 > CitusDB > 使用案例 >

CitusDB UPSERT

秦飞航
2023-12-01

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值。

转载于:https://www.cnblogs.com/zeppelin/p/7081832.html

 类似资料:

相关阅读

相关文章

相关问答