Hi!
Looking for a method to do bulk insert ignoring product foreign
key mismatches.
Only products which exist in product table should be added to
price list.
Tried update/truncate/insert
update pricelistnew set timestamp=to_char(now(),
'YYYYMMDDHH24MISS');
truncate pricelist;
insert into pricelist select * from pricelistnew on
conflict do nothing;
but got error
ERROR: insert or update on table "pricelist" violates foreign key
constraint "pricelist_product_fkey"
DETAIL: Key
(product)=(TMMEM0EM00691BDS ) is not present in table
"product".
insert with foreign key check
insert into pricelist
select * from pricelistnew
where product in (select product from product)
on conflict do nothing;
worked.
Why `on conflict do nothing` clause is ignored ?
How to add only products in product table without using check
product in (select product from product )
Price list has 300000 rows and this command takes several minutes
to complete on fast server. It locks tables so that other queries
running same time are delayed. How to do this bulk insert
efficiently ?
Posted also in
https://stackoverflow.com/questions/67683299/on-conflict-do-nothing-clause-is-ignored-on-insert
Andrus.
