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
), input_count as (
select count(*) as source_count
), new_rows as (
INSERT INTO table1 (pkey, col1, col2)
ON CONFLICT DO NOTHING
Insert not needed for to count:
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"
here are a couple of ideas.
One does the constraint check in a function.
the second modifies the table, creates a trigger to add in the missing data and creates a new index on the three fields that have to be checked
CREATE TRIGGER check_jsonb
FOR EACH ROW
CREATE FUNCTION public._check_jsonb()...
If the uniqueness constraint is the only issue (and I'm interested to learn more about why in the discussion above), here's an idea:
remove the uniqueness constraint
when you do reads (selects), do order by id asc limit 1 so that you ignore duplicates
have some sort of parallel process going through the table periodically and removing duplicates
Assuming the ID in the master table is generated (e.g. because it's defined as generated always as identity or serial) you can do those inserts in a single statement:
with new_master as (
insert into master_table (column_one, column_two, column_tree
returning id --<< makes the generated ID available
), new_t1 as (
insert into ...