New answers tagged insert
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 ...
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"
...
1
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
BEFORE INSERT
ON data
FOR EACH ROW
EXECUTE PROCEDURE
CREATE FUNCTION public._check_jsonb()...
1
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
3
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
values (1,2,3)
returning id --<< makes the generated ID available
), new_t1 as (
insert into ...
Top 50 recent answers are included
