CODEWITHSUNDEEP
sqlpostgresqlupsertknex.js
Ashwani K
Ashwani K

Reputation: 7990

Capture the result of multiple upsert statement into one output for Postgresql

I am running multiple upsert statements in a single run. The queries can be add or update depending on the data. Is is possible to know if a new insert occurred.

test_unique is a unique constraint for column1 and column2 put together.

insert into test(column1,column2) values(1,1) ON CONFLICT ON CONSTRAINT test_unique DO NOTHING;
insert into test(column1,column2) values(1,2) ON CONFLICT ON CONSTRAINT test_unique DO NOTHING;
insert into test(column1,column2) values(1,1) ON CONFLICT ON CONSTRAINT test_unique DO NOTHING;

If we take above queries

Any client library when running these queries as a batch will capture the output of last query and will return 0 rows.

Is there any way to know even a single insert irrespective of its position in the queries?

Thanks

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can use returning:

with i1 as (
      insert into test(column1,column2) values(1,1)
          ON CONFLICT ON CONSTRAINT test_unique DO NOTHING
          returning *
     ),
     i2 as (
      insert into test(column1,column2) values(1,2)
          ON CONFLICT ON CONSTRAINT test_unique DO NOTHING
          returning *
     ),
     i3 as (
      insert into test(column1,column2) values(1,1)
          ON CONFLICT ON CONSTRAINT test_unique DO NOTHING
          returning *
    )
select id from i1 union all
select id from i2 union all
select id from i3;

Upvotes: 2

Related Questions