How to Ignore error in batch insert Postgresql

Solution

You could insert using the WHERE NOT EXISTS clause.

For example, consider a test table with a numeric id as primary key and a textual name.

Code

db=> CREATE TABLE test(id BIGSERIAL PRIMARY KEY, name TEXT);
CREATE TABLE

-- Insertion will work - empty table
db=> INSERT INTO test(id, name) 
     SELECT 1, 'Partner number 1' 
     WHERE NOT EXISTS (SELECT 1,2 FROM test WHERE id=1);
INSERT 0 1

-- Insertion will NOT work - duplicate id
db=> INSERT INTO test(id, name) 
     SELECT 1, 'Partner number 1' 
     WHERE NOT EXISTS (SELECT 1,2 FROM test WHERE id=1);    
INSERT 0 0

-- After two insertions, the table contains only one row
db=> SELECT * FROM test;
 id |       name
----+------------------
  1 | Partner number 1
(1 row)

Difference from ON CONFILCT

Quoting the documentation:

ON CONFLICT can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error.

The action can be DO NOTHING, or a DO UPDATE. The second approach is often referred to as Upsert - a portmanteau of Insert and Update.

Technically WHERE NOT EXISTS is equivalent to ON CONFILCT DO NOTHING. See the query plans for a deeper dive.