Re: ON CONFLICT DO NOTHING ignored on bulk insert
Are you wanting to minimize the locking time, or ensure the whole process completes as soon as possible? If the prior, you can insert into a temp table like the real one (which is maybe what pricelistnew is already), delete the rows where they don't have a valid reference, and then insert all remaining rows.
Re: ON CONFLICT DO NOTHING ignored on bulk insert
https://www.postgresql.org/docs/11/sql-insert.html#SQL-ON-CONFLICT "The optional ON CONFLICT clause specifies an alternative action to raising a *unique violation or exclusion constraint violation* error." El mar, 25 de may. de 2021 a la(s) 03:29, Geoff Winkless ( pgsqlad...@geoff.dj) escribió: > On Tue, 25 May 2021 at 08:18, Andrus 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 > -- Cordialmente, Ing. Hellmuth I. Vargas S. Esp. Telemática y Negocios por Internet Oracle Database 10g Administrator Certified Associate EnterpriseDB Certified PostgreSQL 9.3 Associate
Re: ON CONFLICT DO NOTHING ignored on bulk insert
On Tue, 25 May 2021 at 08:18, Andrus 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
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(), 'MMDDHH24MISS'); 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 30 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.
