0

I want to use insert multiple rows using a single insert statement in Postgres.

Here, the catch is that if insert fails for single row, all other successful inserts are being rollbacked. Is there a way to avoid the rollback and make the query return list of failed rows.

Else, I end up in writing a loop of insert statements. I am using Node pg module. Is there a recommended way of achieving my requirement if postgres doesn't support this.

Edit - 1

insert into test(name, email) values ('abcd', 'abcd@a'), ('efgh', 'blah'), (null, 'abcd') ON CONFLICT DO NOTHING;

ERROR:  null value in column "name" violates not-null constraint
DETAIL:  Failing row contains (9, null, abcd).

After above query, select statement returns 0 rows. I am looking for a solution wherein, the first two rows get inserted.

krisnik
  • 1,406
  • 11
  • 18

1 Answers1

1

Sounds like the failure you're talking about is hitting some sort of unique constraint? Take a look at PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values for a question related to insert... on conflict usage.

For example if you do INSERT INTO X VALUES (...100 rows...) ON CONFLICT DO NOTHING; Any duplicates that collide with the primary key will just be ignored. The alternate to DO NOTHING is to do an UPDATE on conflict.

EDIT to match newly stated question. On conflict does not help with null constraint violations. You can do a WITH clause and select only the values without null properties. Here's a sample I just tested in Postgres:

create extension if not exists pgcrypto;
create table tmp (id uuid primary key default gen_random_uuid());

with data_set_to_insert as (
    select x.id from (values (null), (gen_random_uuid())) x(id) -- alias x can be anything, does not matter what it is
)
insert into tmp(id) select id from data_set_to_insert where id is not null returning *;
Catalyst
  • 3,143
  • 16
  • 20