Upsert records in postgresql

I am working with postgresql database and I have below update query which works fine -

    'UPDATE "main"."item_vendor"\n' +
    '    SET\n' +
    '      "vendor_id" = $1, "audit_by" = $2\n' +
    '    \n' +
    '  FROM "main"."item"\n' +
    '\n' +
    '    \n' +
    '  WHERE \n' +
    '  "main"."item"."id" IN ($3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)\n' +
    ' AND "main"."item".id = "main"."item_vendor".item_id\n',
  values: [
    '28',        17355915,
    '188646',    '188647',
    '188648',    '188649',
    '188650',    '188749',
    '188750',    '188751',
    '188752',    '188753',
    '200000691', '200000693',
    '200000695', '200000697',
    '200000699'
  ]

Now I need to convert above query so that it can insert as well if records doesn't exist. What is the right way to change my above query to upsert query (update if row exists and if it doesn't then just insert it)? I am using postgresql 14 version.

Confusion I have is - How do I convert above query into below which does the upsert after reading the documentation.

INSERT INTO ... SELECT ... ON CONFLICT (key) DO UPDATE

Any help will be greatly appreciated.

Solution

Extract the select. It needs to be converted to a join with the updated table. It's a left join so we get all the records from item, even if they're not in item_vendor.

select item.vendor_id, item.audit_by, item.id
from item
left join item_vendor on item.id = item_vendor.item_id
where item.id IN ($3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)

Turn it into an insert...select.

insert into item_vendor(vendor_id, audit_by, item_id)
select item.vendor_id, item.audit_by, item.id
from item
left join item_vendor on item.id = item_vendor.item_id
where item.id IN ($3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)

Then add the on conflict clause. In order to tell if a record exists, you need to key on something unique in item_vendor. Usually this is the primary key. I'm just going to guess what that is. The values of the rejected insert are all in the pseudo table excluded.

insert into item_vendor(vendor_id, audit_by, item_id)
select item.vendor_id, item.audit_by, item.id
from item
left join item_vendor on item.id = item_vendor.item_id
where item.id IN ($3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17)
on conflict(vendor_id) do
    update set vendor_id = excluded.vendor_id, audit_by = excluded.audit_by

Demonstration