posgres upsert with unknown/random id from client?

Question

Hello i'm trying to insert/update some rows with upsert but the id on the insert part is unknown.

what i mean is this

INSERT INTO task (id,name,description,completed)
VALUES
--update
(20,'Do stuff','Stuff to do...',false), 
(21,'Do stuff','Stuff to do...',false), 
--insert
(unknown_or_random_id,'Do stuff','Stuff to do...',false), 
(unknown_or_random_id,'Do stuff','Stuff to do...',false), 
ON CONFLICT (id) DO UPDATE
SET name= excluded.name etc....

basically the array i get from the client contains random ids for the new items that i have to insert, how do i handle that?

by random i mean something like "01asd", "02asdc", "03asdce" etch...

Answer 1

Not knowing the id on insert is totally irrelevant. The only question is does the value id already exist. You just need to basic "insert... on conflict (xx) do update ..."
See Demo. Note: the demo uses distinct on (id). This is strictly because the ids are randomly generated, with a high chance of generating the same id at least twice, but "on conflict" throws an exception when the if the input stream itself contains a duplicate.

insert into task( id, name, description) 
   select id, name, description 
     from <table name>            -- or VALUES (...)
    on conflict (id)  
       do update 
          set name = excluded.name
            , description = excluded.description
            , completed = true;