Thread

1. ON CONFLICT DO NOTHING ignored on bulk insert

   Andrus <kobruleht2@hot.ee> — 2021-05-25T07:18:55Z

   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.
   
   

2. Re: ON CONFLICT DO NOTHING ignored on bulk insert

   Geoff Winkless <pgsqladmin@geoff.dj> — 2021-05-25T08:29:17Z

   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
   

3. Re: ON CONFLICT DO NOTHING ignored on bulk insert

   Hellmuth Vargas <hivs77@gmail.com> — 2021-05-25T13:47:29Z

   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 (
   pgsqladmin@geoff.dj) escribió:
   
   > 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
   >
   
   
   -- 
   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
   

4. Re: ON CONFLICT DO NOTHING ignored on bulk insert

   Michael Lewis <mlewis@entrata.com> — 2021-05-25T15:41:42Z

   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.