New answers tagged

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