针对数据写入时有主键冲突的情况，INSERT ON CONFLICT语法可以将冲突主键的INSERT行为转换为UPDATE行为，从而实现冲突主键的覆盖写入。该特性又称UPSERT覆盖写，与MySQL的REPLACE INTO类似。
语法
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ [ AS ] alias ] [ ( column_name [, ...] ) ]
[ OVERRIDING { SYSTEM | USER} VALUE ]
{ DEFAULT VALUES |
VALUES ( { expression | DEFAULT } [, ...] ) [, ...] |
query |
VALUES record |
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
}
[ ON CONFLICT [ conflict_target ] conflict_action ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
其中 conflict_target 可以是以下之一：
( { index_column_name | ( index_expression ) } [ COLLATE collation ]
[ opclass ]
[, ...] ) [ WHERE index_predicate ]
ON CONSTRAINT constraint_name
并且 conflict_action 是以下之一：
DO NOTHING
DO UPDATE SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT }
[, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]
[ WHERE condition ]
ON CONFLICT子句可以实现覆盖写入。该子句由conflict_target和conflict_action组成。
| 参数 | 说明 |
|---|---|
| conflict_target | conflict_action取值为Do Update时，conflict_target需要指定用来定义冲突的主键列或唯一索引列 conflict_action取值为Do Nothing时，conflict_target可省略 |
| conflict_action | DO NOTHING：如果conflict_target指定的列有冲突，则丢弃待插入的数据 DO UPDATE：如果conflict_target指定的列有冲突，则按照后面的UPDATE子句进行数据覆盖 |
示例
CREATE TABLE user_logins (
user_id int PRIMARY KEY,
login_cnt int,
last_login_time date
); --记录用户登录信息的表
test=# insert into user_logins values (1,1,sysdate);
INSERT 0 1
test=# insert into user_logins values (1,2,sysdate); --插入主键重复的数据报错
错误: 重复键违反唯一约束"user_logins_pkey"
描述: 键值"(user_id)=(1)" 已经存在
冲突时做更新操作：
test=# INSERT INTO user_logins VALUES
test-# (1,2,sysdate),(2,1,sysdate)
test-# ON conflict(user_id)
test-# DO UPDATE SET login_cnt = user_logins.login_cnt + EXCLUDED.login_cnt;
INSERT 0 2
test=# select * from user_logins;
user_id | login_cnt | last_login_time
---------+-----------+---------------------
1 | 3 | 2022-10-14 15:02:45
2 | 1 | 2022-10-14 15:06:47
(2 行记录)
user_logins.login_cnt 原表中的值
EXCLUDED.login_cnt insert语句中的值
冲突时不做处理：
test=# INSERT INTO user_logins VALUES
test-# (1,2,sysdate),(3,1,sysdate)
test-# ON conflict(user_id)
test-# DO NOTHING;
INSERT 0 1
test=# select * from user_logins;
user_id | login_cnt | last_login_time
---------+-----------+---------------------
1 | 3 | 2022-10-14 15:02:45
2 | 1 | 2022-10-14 15:06:47
3 | 1 | 2022-10-14 15:09:52
(3 行记录)