How to not increment sequence when conflict of insert occurs

Question

When a conflict occurs on insert into a table with an auto incrementing id column, the sequence gets bumped causing a gap in the id range, which for me is undesirable.

Here is a simplified version of my situation:

create table tab1 (
    id serial,
    col1 int,
    col2 int,
    col3 int,
    constraint const1 unique (col1, col2)
);

In a stored proc:

insert into tab1 (col1, col2, col3)
values (1, 2, 3)
on conflict on constraint const1
do update set col3 = excluded.col3

If there's a collision, the insert ... on conflict ... update works fine, except the next value from the sequence is burned.

Without doing an exists() check first, is there a way to not burn the next value from the sequence using just a single statement?

Note: There is no chance of a race condition of concurrent updates for the same conflict key.

Answer 1

There's no way to avoid the increment of the sequence, because it happens before the conflict is detected.

Here's the work around I used:

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;