0

I would like to add rows from table2 into table1 and see how many conflicts of the type "duplicate key value violates unique constraint" result from this in PostgreSQL. pkey is a primary key, so there a unique constraint on it. Unfortunately and apparently, conflict_action does not permit incrementing a counter.

INSERT INTO table1 (pkey, col1, col2)
  SELECT pkey, col1, col2 FROM table2
  ON CONFLICT ...;

So is there another (elegant) way for tackling this either by calculating or retrieving that count?

0
1

Insert not needed for to count:

WITH
cte1 AS ( SELECT COUNT(*) cnt FROM table1 ),
cte2 AS ( SELECT COUNT(*) cnt FROM table2 ),
cte3 AS ( SELECT COUNT(*) cnt FROM table1 JOIN table2 USING (pkey) )
SELECT cte1.cnt "Records in table1",
       cte2.cnt "Records in table2",
       cte3.cnt "Conflicts count",
       cte2.cnt - cte3.cnt "Potential inserts count"
FROM cte1, cte2, cte3;

And insert only non-conflicted records - it will be less expensive.

1

If you really want to get the count once the INSERT is finished, you could do something like this.

with source_data as (
  SELECT pkey, col1, col2 
  FROM table2
), input_count as (
  select count(*) as source_count
  from source_data
), new_rows as (
  INSERT INTO table1 (pkey, col1, col2)
  select *
  from source_data
  ON CONFLICT DO NOTHING
  returning pkey
)
select source_count - count(*) as not_inserted
from new_rows
  cross join input_count;

It's not going to be very efficient though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.