In PostgreSQL, I’m performing an UPSERT on 1000 rows, I’m using ON CONFLICT … DO NOTHING, if 1 row fails, do the other 999 get inserted?

I’m trying to understand how the ON CONFLICT clause works. What I want, is if an insert fails for any reason, I want it to do nothing with the row, but continue processing the other items in the bulk insert.

Here’s my scenario. I’m building a bulk insert query, with 1000 rows to be inserted. Currently, my query has:

INSERT INTO table (...,...,...) 
VALUES (...,...,...),(...,null..,...), (998 more) 
ON CONFLICT ON CONSTRAINT primary_key DO NOTHING

​x
 
INSERT INTO table (...,...,...) VALUES (...,...,...),(...,null..,...), (998 more) ON CONFLICT ON CONSTRAINT primary_key DO NOTHING​

What I want to know is can I do this:

INSERT INTO table (...,...,...) VALUES (...,...,...),(...,null..,...), (998 more) 
ON CONFLICT DO NOTHING

 
INSERT INTO table (...,...,...) VALUES (...,...,...),(...,null..,...), (998 more) ON CONFLICT DO NOTHING​

This would effectively take any conflict inserting a row (in my case a null constraint on a column) and just not insert it. I need to ensure that if there is 1 conflict for one row, that the other 999 rows will still be inserted.

UPDATE: here’s a demo showing the issue I’m encountering: https://dbfiddle.uk/?rdbms=postgres_9.5&fiddle=8947dbcd38e9136531b907d3c745c0f5

UPDATE 2: It looks like when you pass null values, it fails the entire bulk insert process.

FINAL UPDATE: Accepted the first answer because of that cool demo link – which helped me realize (and in the comments) that null isn’t captured, so I just need to change my table structure and rely on the primary key… so when duplicate nulls are added, the pkey constraint gets triggered. Thanks everyone!

Answer

All rows for which there is no conflict will be inserted, and the conflicting row will be ignored.

Consider this demo:

-- create the table and insert a record with id 1
create table t (id int primary key, info text);
insert into t values (1, 'old');
-- 1 rows affected

-- attempt to insert 5 'new' records with ids 1 .. 5
insert into t 
select n, 'new' from generate_series(1, 5) as n(n) on conflict do nothing;
-- 4 rows affected

-- check the results
select * from t;

 
-- create the table and insert a record with id 1create table t (id int primary key, info text);insert into t values (1, 'old');-- 1 rows affected​-- attempt to insert 5 'new' records with ids 1 .. 5insert into t select n, 'new' from generate_series(1, 5) as n(n) on conflict do nothing;-- 4 rows affected​-- check the resultsselect * from t;​

id | info
-: | :---
 1 | old 
 2 | new 
 3 | new 
 4 | new 
 5 | new

Advertisement

Answer