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

Solution 1:

You can change your query to use sequence functions like:

INSERTINTO upsert_test (id, name, description)
VALUES ((select nextval('upsert_test_id_seq')), 'thing1', 'test')
on conflict (id)
do updateset (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:

INSERTINTO upsert_test (id, name, description)
VALUES (COALESCE(:YOUR_ID_PARAM, (select nextval('upsert_test_id_seq'))), 'thing1', 'test')
on conflict (id)
do updateset (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.