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 ? Using PostgreSQL 13.2, compiled by Visual C++ build 1900, 64-bit
Posted also in https://stackoverflow.com/questions/67683299/on-conflict-do-nothing-clause-is-ignored-on-insert
Andrus.