Count violated unique constraint caused by INSERT statement 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?
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.