CODEWITHSUNDEEP
postgresql
s16h
s16h

Reputation: 4855

Advance sequence (`nextval`) in PostgreSQL if a row is inserted

I'm conditionally inserting a row into the a PostgreSQL table and manually specifying the ID:

INSERT INTO foo (foo_id, foo_name)
SELECT 4, 'My Name'
WHERE NOT EXISTS (
    SELECT * FROM foo WHERE foo_id = 4
);

This won't increase the sequence that generates the next foo_id.

How can I run SELECT nextval('foo_foo_id_seq'::regclass); only if a row is actually inserted in the above query?

Upvotes: 3

Views: 3290

Answers (1)

user330315
user330315

Reputation:

You can supply a new value for the sequence using setval()

If you want to change the sequence to continue with the "forced" value, you can use setval() inside the select:

INSERT INTO foo (id, foo_name)
select *
from (
  VALUES (setval('foo_id_seq', 42), 'My Name')
) as t (id, foo_name)
WHERE NOT EXISTS (
    SELECT * FROM foo WHERE id = 42
);

If a row is inserted the sequence will continue with 43.

If the current sequence value is already bigger than 43 this will give you problems later if other transactions insert into the table and rely on the sequence.

If you want to advance the sequence from whatever value it currently has, you can use something like this:

INSERT INTO foo (id, foo_name)
select id, foo_name
from (
  VALUES (42, 'My Name', nextval('foo_id_seq'))
) as t (id, foo_name, nextval)
WHERE NOT EXISTS (
    SELECT * FROM foo WHERE id = 42
);

Upvotes: 2

Related Questions