当插入到具有自动递增id列的表中时发生冲突时,序列会被打乱,导致id范围出现间隙,这对我来说是不可取的。
以下是我的情况的简化版本:
create table tab1 (
id serial,
col1 int,
col2 int,
col3 int,
constraint const1 unique (col1, col2)
);
在存储过程中:
insert into tab1 (col1, col2, col3)
values (1, 2, 3)
on conflict on constraint const1
do update set col3 = excluded.col3
如果发生冲突,则insert ... on conflict ... update
工作正常,但序列中的下一个值会被烧录。
如果不首先进行exists()
检查,是否有一种方法可以而不是仅使用一条语句从序列中烧出下一个值?
注意:对于同一个冲突密钥,不可能出现并发更新的竞争条件。
无法避免序列的增量,因为它发生在检测到冲突之前。
以下是我使用的工作方法:
insert into tab1 (col1, col2, col3)
select x.*
from (select 1 a, 2 b, 3 c) x
left join tab1 o on o.col1 = x.a and o.col2 = x.b
where o.col1 is null
returning tab1.id into _id;
if _id is null then
update tab1 set
col3 = 3
where col1 = 1
and col2 = 2;
end if;