SQL CTE Syntax to DELETE / INSERT rows

Question

What's the CTE syntax to delete from a table, then insert to the same table and return the values of the insert?

Operating on 2 hours of sleep and something doesn't look right (besides the fact that this won't execute):

WITH delete_rows AS (
   DELETE FROM <some_table> WHERE id = <id_value>
   RETURNING *
)
SELECT * FROM delete_rows
UNION
(
   INSERT INTO <some_table> ( id, text_field )
      VALUES ( <id_value>, '<text_field_value>' )
      RETURNING *
)

The expected behavior is to first clear all the records for an ID, then insert records for the same ID (intentionally not an upsert) and return those inserted records (not the deletions).

Answer 1

Your question update made clear that you cannot do this in a single statement.

Packed into CTEs of the same statement, both operations (INSERT and DELETE) would see the same snapshot of the table and execute virtually at the same time. I.e., the INSERT would still see all rows that you thought to be deleted already. The manual:

All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables.

You can wrap them as two independent statements into the same transaction - which doesn't seem strictly necessary either, but it would allow the whole operation to succeed / fail atomically:

BEGIN;

DELETE FROM <some_table> WHERE id = <id_value>;

INSERT INTO <some_table> (id, text_field)
VALUES ( <id_value>, '<text_field_value>')
RETURNING *;

COMMIT;

Now, the INSERT can see the results of the DELETE.