Ignore duplication constraints when bulk inserting into Postgresql or MySQL

Question

When issuing a bulk insert statement, how can we ignore (supress) the exception thrown by DB and/or JDBC driver ?

Lets say I want to bulk insert some Users and I have an Id as a unique key

INSERT INTO users
(id,name,age,email,pass_code) 
VALUES 
(1,'Mark',18,'[email protected]',123),
(2,'Zak',18,'[email protected]',123),
(3,'Djigi',18,'[email protected]',123),

(1,'James Petkov',18,'[email protected]',123), --Duplicated by id ? 

(4,'Kinkinikin',18,'[email protected]',123),
(5,'A bula bula ',18,'[email protected]',123),
(6,'Shakazulo',18,'[email protected]',123);

How to tell the engine MySQL/PostgreSQL to continue inserting the remaining records ?

Is this supported in SQL at all ?

Then don't make this ID the primary key of your table. Since it is in duplicate anyway, let MySQL assign an ID. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 28, 2016 at 12:23
is your question more about continuing insertions when errors are encountered along the way? — Martin Serrano
– Martin Serrano, Commented Nov 28, 2016 at 12:26
@TimBiegeleisen The ideas is not about generating ids but rather to suppress the exception when inserting duplicate records. — Adelin
– Adelin, Commented Nov 28, 2016 at 12:42
Are you using MySQL or Postgres? The error is there for a reason. If not primary key then don't read it in as such. — Tim Biegeleisen
– Tim Biegeleisen, Commented Nov 28, 2016 at 12:45
@TimBiegeleisen What I am asking, generally, if you are doing bulk insert and you have duplicate record regardless of which key is duplicated how can you continue the operation and skip the duplicated key, that's it :) — Adelin
– Adelin, Commented Nov 28, 2016 at 12:48

Laurenz Albe · Accepted Answer · 2016-11-28 12:28:40Z

1

In PostgreSQL, you can ignore the failing rows with

INSERT ... ON CONFLICT (id) DO NOTHING;

A more general solution is to run each INSERT separately and ignore errors.

answered Nov 28, 2016 at 12:28

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Ignore duplication constraints when bulk inserting into Postgresql or MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related