Count violated unique constraint caused by INSERT statement

Question

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?

Answer 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.

Answer 2

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.