PostgreSQLSQL高级技巧写入和冲突

举报
xcc-2022 发表于 2022/07/04 21:25:26 2022/07/04
【摘要】 PostgreSQL 9.5 中引入了一项新功能,UPSERT(insert on conflict do),当插入遇到约束错误时,直接返回或者改为执行UPDATE,通过UPSERT就可以实现不存在则插入、存在则更新的功能;语法如下:Command: INSERT Description: create new rows in a table Syntax: [ WITH [ RECU...

PostgreSQL 9.5 中引入了一项新功能,UPSERT(insert on conflict do),当插入遇到约束错误时,直接返回或者改为执行UPDATE,通过UPSERT就可以实现不存在则插入、存在则更新的功能;

语法如下:

Command: INSERT  
Description: create new rows in a table  
Syntax:  
[ WITH [ RECURSIVE ] with_query [, ...] ]  
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]  
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }  
    [ ON CONFLICT [ conflict_target ] conflict_action ]  
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]  
  
where conflict_target can be one of:  
  
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]  
    ON CONSTRAINT constraint_name  
  
and conflict_action is one of:  
  
    DO NOTHING  
    DO UPDATE SET { column_name = { expression | DEFAULT } |  
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |  
                    ( column_name [, ...] ) = ( sub-SELECT )  
                  } [, ...]  
              [ WHERE condition ]  

> 9.5以上版本通过 insert on conflict do 实现

  1. 创建测试表test_0125:
CREATE TABLE test_0125 ( 
	ID INT PRIMARY KEY, --需要确定id是唯一键
	NAME VARCHAR, 
	CREATE_DATE TIMESTAMP 
)
  1. 插入测试数据:
INSERT INTO test_0125 (id, name, create_date) VALUES (1, '张三', now());
INSERT INTO test_0125 (id, name, create_date) VALUES (2, '李四', now());
INSERT INTO test_0125 (id, name, create_date) VALUES (3, '王五', now());

插入数据:

id | name | create_date
----±-----±---------------------------
1 | 张三 | 2021-01-25 10:56:38.106356
2 | 李四 | 2021-01-25 10:56:38.109347
3 | 王五 | 2021-01-25 10:56:38.110345
(3 rows)

  1. 插入测试数据:
INSERT INTO test_0125 (id, name, create_date) VALUES (1, '赵六', now()) ON conflict (id) DO UPDATE SET name = '赵六', create_date = now() RETURNING *;

测试结果:

postgres=# INSERT INTO test_0125 (id, name, create_date) VALUES (1, ‘赵六’, now()) ON conflict (id) DO UPDATE SET name = ‘赵六’, create_date = now() RETURNING *;
id | name | create_date
----±-----±---------------------------
1 | 赵六 | 2021-01-25 11:08:45.608024
(1 row)

INSERT 0 1

> 9.5以下版本可以通过函数,或者WITH语法来实现UPSERT类似的功能。

  1. 通过函数实现:

CREATE OR REPLACE FUNCTION f_insert_test(t_id INT, t_name VARCHAR, t_date TIMESTAMP) RETURNS INT AS
$$
DECLARE res int := t_id;
BEGIN 
	UPDATE test_0125 SET name = t_name, create_date = t_date WHERE id = t_id;
	IF NOT FOUND THEN
		INSERT INTO test_0125 (id, name, create_date) VALUES (t_id, t_name, t_date);
	END IF;
  EXCEPTION WHEN OTHERS THEN RETURN 0;
	RETURN res;
END;
$$LANGUAGE plpgsql STRICT;

插入测试数据:

SELECT f_insert_test(2, '孙七', now()::timestamp);

结果如下:
id | name | create_date
----±-----±---------------------------
3 | 王五 | 2021-01-25 10:56:38.110345
1 | 赵六 | 2021-01-25 11:08:45.608024
2 | 孙七 | 2021-01-25 11:14:09.704528
(3 rows)

  1. WITH语句实现:
WITH upsert AS ( 
	UPDATE test_0125 SET name = '周八', create_date = now() WHERE ID = 3 RETURNING * 
) 
INSERT INTO test_0125 SELECT 3, '周八', now()
WHERE NOT EXISTS ( SELECT 1 FROM upsert WHERE ID = 3 );

查询结果:
id | name | create_date
----±-----±---------------------------
2 | 孙七 | 2021-01-25 11:21:32.011392
1 | 赵六 | 2021-01-25 11:21:38.899602
3 | 周八 | 2021-01-25 11:21:43.66127
(3 rows)

  1. 对于记录不存在,保证只有一个session插入数据 这部分如有需要请参考引用博客: P
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。