ON CONFLICT DO NOTHING ignored on bulk insert - Mailing list pgsql-general

From Andrus
Subject ON CONFLICT DO NOTHING ignored on bulk insert
Date
Msg-id 9d9b1cc1-e7f1-dbe7-2a8d-d2abc6316f29@hot.ee
Whole thread Raw
Responses Re: ON CONFLICT DO NOTHING ignored on bulk insert
List pgsql-general

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.

pgsql-general by date:

Previous
From: "Peter J. Holzer"
Date:
Subject: Re: The contents of the pg_timezone_names view bring some surprises
Next
From: Geoff Winkless
Date:
Subject: Re: ON CONFLICT DO NOTHING ignored on bulk insert