PostgreSQLSQL高级技巧写入和冲突
【摘要】 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 实现
- 创建测试表test_0125:
CREATE TABLE test_0125 (
ID INT PRIMARY KEY, --需要确定id是唯一键
NAME VARCHAR,
CREATE_DATE TIMESTAMP
)
- 插入测试数据:
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)
- 插入测试数据:
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类似的功能。
- 通过函数实现:
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)
- 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)
- 对于记录不存在,保证只有一个session插入数据 这部分如有需要请参考引用博客: P
【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱:
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论(0)