针对数据写入时有主键冲突的情况,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子句进行数据覆盖 |
示例
CREATETABLE user_logins (
user_id intPRIMARY KEY,
login_cnt int,
last_login_time date
); --记录用户登录信息的表
test=# insertinto user_logins values (1,1,sysdate);
INSERT01
test=# insertinto user_logins values (1,2,sysdate); --插入主键重复的数据报错
错误: 重复键违反唯一约束"user_logins_pkey"
描述: 键值"(user_id)=(1)" 已经存在
冲突时做更新操作:
test=# INSERTINTO user_logins VALUES
test-# (1,2,sysdate),(2,1,sysdate)
test-# ON conflict(user_id)
test-# DO UPDATESET login_cnt = user_logins.login_cnt + EXCLUDED.login_cnt;
INSERT02
test=# select*from user_logins;
user_id | login_cnt | last_login_time
---------+-----------+---------------------
1 |3 |2022-10-1415:02:45
2 |1 |2022-10-1415:06:47
(2 行记录)
user_logins.login_cnt 原表中的值
EXCLUDED.login_cnt insert语句中的值
冲突时不做处理:
test=# INSERTINTO user_logins VALUES
test-# (1,2,sysdate),(3,1,sysdate)
test-# ON conflict(user_id)
test-# DO NOTHING;
INSERT01
test=# select*from user_logins;
user_id | login_cnt | last_login_time
---------+-----------+---------------------
1 |3 |2022-10-1415:02:45
2 |1 |2022-10-1415:06:47
3 |1 |2022-10-1415:09:52
(3 行记录)