I am writing a large, multi-step CTE for performance reasons.
In one query, data must be moved from one table to another, but the quantity of rows moved is uncertain and could be zero.
In a subsequent table, the origin from the previous query is deleted but must be after the previous query is completed.
Finally, rows must be written in place of the deleted rows after the second query above is completed.
In the first two queries, I am using RETURNING to enforce execution order.
In the second query, I'm determining that the first query is completed by this subquery
(SELECT COUNT(*) FROM first_query) >= 0
In the the third query, I'm determining that the second query is completed by this subquery
SELECT EXISTS (SELECT 1 FROM second_query)
Is the subquery to determine that the first query has completed correct?
Is the subquery to determine that the second query, which must return rows, has completed optimal for accuracy, precision, and performance?
Using the above subqueries to enforce execution order is giving duplicate key value violations.
WITH copy_to_other_table AS ( INSERT INTO other_table (column_a, column_b) SELECT column_a, column_b FROM main_table WHERE column_a = $1::bigint RETURNING * ), main_table_deleted AS ( DELETE FROM main_table WHERE column_a = $1::bigint AND (SELECT COUNT(*) FROM copy_to_other_table) >= 0 RETURNING * ) INSERT INTO main_table (column_a, column_b) SELECT column_a, column_b FROM another_table WHERE column_a = $1::bigint AND EXISTS (SELECT 1 FROM main_table_deleted)
It is the final query that is violating the unique constraint.