Postgres upsert without incrementing serial IDs?

Question

Consider the following table:

CREATE TABLE key_phrase(
    id SERIAL PRIMARY KEY NOT NULL,
    body TEXT UNIQUE
)

I'd like to do the following:

Create a record with the given body if it doesn't already exist.
Return the id of the newly created record, or the id of the existing record if a new record was not created.
Ensure the serial id is not incremented on conflicts.

I've tried a few methods, the most simple including basic usage of DO NOTHING:

INSERT INTO key_phrase(body) VALUES ('example') ON CONFLICT DO NOTHING RETURNING id

However, this will only return an id if a new record is created.

I've also tried the following:

WITH ins AS (
    INSERT INTO key_phrase (body)
    VALUES (:phrase)
    ON     CONFLICT (body) DO UPDATE
    SET    body = NULL
    WHERE  FALSE     
    RETURNING id
)
SELECT id FROM ins
UNION  ALL
SELECT id FROM key_phrase
WHERE  body = :phrase
LIMIT  1;

This will return the id of a newly created record or the id of the existing record. However, it causes the serial primary to be bumped, causing gaps whenever a new record is created.

So how can one perform a conditional insert (upsert) that fulfills the 3 requirements mentioned earlier?

Similar https://stackoverflow.com/questions/34708509/how-to-use-returning-with-on-conflict-in-postgresql — snakecharmerb, Dec 20 '20 at 07:50
Whatever you try, using a serial will always give you a chance of having gabs in the numbering. There is no way to avoid this, unless you keep on checking and changing the underlying sequence. From the manual: Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. — Frank Heikens, Dec 20 '20 at 11:17

GMB · Answer 1 · 2020-12-20T21:00:15.593

I suspect that you are looking for something like:

with 
    data as (select :phrase as body),
    ins as (
        insert into key_phrase (body)
        select body 
        from data d
        where not exists (select 1 from key_phrase kp where kp.body = d.body)
        returning id
    )
select id from ins
union all
select kp.id 
from key_phrase kp
inner join data d on d.body = kp.body

The main difference with your original code is that this uses not exists to skip already inserted phrases rather than on conflict. I moved the declaration of the parameter to a CTE to make things easier to follow, but it doesn't have to be that way, we could do:

with 
    ins as (
        insert into key_phrase (body)
        select body 
        from (values(:phrase)) d(body)
        where not exists (select 1 from key_phrase where body = :phrase)
        returning id
    )
select id from ins
union all
select kp.id from key_phrase where body = :phrase

Not using on conflict will reduce the number of sequences that are burned. It should be highlighted, however, that there is no way to guarantee that serials will consistently be sequential. There could be gaps for other reasons. This is by design; the purpose of serials is to guarantee uniqueness, not "sequentiallity". If you really want an auto-increment with no holes, the consider row_number() and a view.

Thank you for your answer. The second query would error due to the missing `data` declaration, wouldn't it? I've tested the first query and it appears to "work" - an **id** is returned irrespective of whether or not a new record is created and the serial id is not incremented (I understand that Postgres does not guarantee sequential ids). How does the first query work, exactly? — Orbit, Dec 20 '20 at 20:36
@Orbit: I fixed the second query. The trick is `not exists`, which explicitly eliminates duplicates *before* attempting to insert. — GMB, Dec 20 '20 at 21:03

Postgres upsert without incrementing serial IDs?

1 Answers1