CODEWITHSUNDEEP
sqlpostgresqlsql-updatesql-insertknex.js
Orbit
Orbit

Reputation: 2385

Postgres upsert without incrementing serial IDs?

Consider the following table:

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

I'd like to do the following:

  1. Create a record with the given body if it doesn't already exist.
  2. Return the id of the newly created record, or the id of the existing record if a new record was not created.
  3. 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?

Upvotes: 3

Views: 2499

Answers (1)

GMB
GMB

Reputation: 222492

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.

Upvotes: 4

Related Questions