Thread: ON CONFLICT DO NOTHING ignored on bulk insert
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.
Looking for a method to do bulk insert ignoring product foreign key mismatches.
ON CONFLICT clause specifies an alternative action to raising a unique violation or exclusion constraint violation error."On Tue, 25 May 2021 at 08:18, Andrus <kobruleht2@hot.ee> wrote:Looking for a method to do bulk insert ignoring product foreign key mismatches.
ON CONFLICT only works with unique constraints, it's not designed for what you're trying to use it for.Geoff
--
Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet