CODEWITHSUNDEEP
sqlpostgresql
Bob Perry
Bob Perry

Reputation: 59

Upsert/on conflict with serial primary key

The upsert works using on conflict but the datatype of id is serial meaning I want to have it auto-generated/incremented. If I do the insert without specifying id the insert works fine.

The problem I have is combining the two. To get the key auto incremented I do not pass the id into the insert but if I do not pass id then the update will never fire. I cannot pass null to id as it is non-null field.

In below example - I run the query first time and it does insert and second time it does update but I cannot figure out how to pass 'nothing' to insert so the identity key still works on insert. I can put DEFAULT in the insert but then I cannot pass a real id value if there is one.

CREATE TABLE public.upsert_test
(
    id INTEGER NOT NULL DEFAULT nextval('upsert_test_id_seq'::regclass),
    name character varying(20) COLLATE pg_catalog."default",
    description character varying(20) COLLATE pg_catalog."default",
    CONSTRAINT upsert_test_pkey PRIMARY KEY (id)
)

INSERT INTO upsert_test (id, name, description)
VALUES (1, 'thing1', 'test')
on conflict (id)
do update set (name , description) = ('thing_updated','test-updated')   
where upsert_test.id = 1;

Upvotes: 3

Views: 4421

Answers (1)

Rohit
Rohit

Reputation: 2152

You can change your query to use sequence functions like:

INSERT INTO upsert_test (id, name, description)
VALUES ((select nextval('upsert_test_id_seq')), 'thing1', 'test')
on conflict (id)
do update set (name , description) = ('thing_updated','test-updated')   
where upsert_test.id = (select currval('upsert_test_id_seq'));

Note this may not be threadsafe, for eg if second call to this sql is executed before select currval('upsert_test_id_seq') in first call, then the update may fail in first query.

Update after more information from op

You can change the query to like this:

INSERT INTO upsert_test (id, name, description)
VALUES (COALESCE(:YOUR_ID_PARAM, (select nextval('upsert_test_id_seq'))), 'thing1', 'test')
on conflict (id)
do update set (name , description) = ('thing_updated','test-updated')   
where upsert_test.id = :YOUR_ID_PARAM;

Note I added the coalesce function so if your parameter is null then use sequence nextval. Also, the update now also uses your parameter.

Upvotes: 1

Related Questions