Insert into a table only those records which doesn't exists found by a join-CodePudding

Below is the query which finds the matching record and returns those "main"."item"."id" which matches the values inside IN clause.

select main.item_vendor.item_id
from main.item_vendor
join main.item on item.id = main.item_vendor.item_id
WHERE "main"."item"."id" IN ('188646', '200000699')

Above query returns only one value which has record for 188646 and for other it is missing. Now I need to use above query and INSERT values in main.item_vendor table just for this 200000699.

How can I plugin below INSERT query into above join query so that it can insert only for those which doesn't exist. Meaning I want to insert in main.item_vendor table only for 200000699 item id since it doesn't exist.

INSERT INTO "main"."item_vendor" ("item_id", "vendor_id", audit_by, currency_id )
VALUES (200000699, ?, ?, ?);

CodePudding user response：

with wNotExists as (
  select main.item.id as notExistsId
    from main.item_vendor
    right join main.item on main.item.id = main.item_vendor.item_id
    where main.item_vendor.item_id is null
      and main.item.id IN ('188646', '200000699') -- comment this line for getting all "id" from "item" not exists in "item_vendor"
)
insert into main.item_vendor (item_id, vendor_id, audit_by, currency_id)
  select notExistsId, ?, ?, ?
    from wNotExists;

CodePudding user response：

You can use a CTE to determinen the item id and the insert iof the item_id doesn't exist

WITH CTE  as (
select item_vendor.item_id
from item_vendor
join item on item.id = item_vendor.item_id
WHERE "main"."item"."id" IN ('188646', '200000699'))
INSERT INTO "item_vendor" ("item_id", "vendor_id", audit_by, currency_id )
SELECT (item_id, 1, 1, 1)
FROM CTE c1
  WHERE NOT EXISTS ( SELECT 1 FROM item_vendor WHERE item_id = c1.item_id)

CodePudding user response：

There is no need for you to check if an id already exists. Put a unique index on item_id or make it the primary key. Postgres will automatically check if the value already exists. If you do nothing else the condition throws an exception, but you can alter this by extending the insert to contain the on conflict clause. You can then either update the existing values or to just ignore (silently) the offending incoming row. So for this case:

insert into "main"."item_vendor" ("item_id", "vendor_id", audit_by, currency_id )
     values ('188646', ?, ?, ?),('200000699', ?, ?, ?)
  on conflict (item_id) 
  do nothing;