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.

Answer 2

UNION accepts SELECTs but not INSERTs.

Suppose the table with non-unique id is created as:

CREATE TABLE some_table (id int, text_field text);
INSERT INTO some_table VALUES (1, 'a'),(1, 'b');

So the initial request can look like this one:

WITH delete_rows AS (DELETE FROM some_table WHERE id = 1 RETURNING *),
    insert_rows AS (
      INSERT INTO some_table (id, text_field) VALUES (1, 'c'),(1, 'd'),(1, 'e') RETURNING *
    )
(SELECT 'deleted', * FROM delete_rows)
UNION ALL
(SELECT 'inserted', * FROM insert_rows);

?column? | id | text_field 
----------+----+------------
 deleted  |  1 | a
 deleted  |  1 | b
 inserted |  1 | c
 inserted |  1 | d
 inserted |  1 | e
(5 rows)

Or if deleted rows aren't needed:

WITH delete_rows AS (DELETE FROM some_table WHERE id = 1),
    insert_rows AS (
      INSERT INTO some_table (id, text_field) VALUES (1, 'c'),(1, 'd'),(1, 'e') RETURNING *
    )
SELECT * FROM insert_rows;

or just:

WITH delete_rows AS (DELETE FROM some_table WHERE id = 1)
    INSERT INTO some_table (id, text_field) VALUES (1, 'c'),(1, 'd'),(1, 'e') RETURNING *;

 id | text_field 
----+------------
  1 | c
  1 | d
  1 | e
(3 rows)

Answer 3

A related (& IMO better) answer, akin to the "Logged DelSert" is this, a logged "SelUp" :

    -- setups
    DROP TABLE IF EXISTS _zx_t1 ;

    CREATE TEMP TABLE 
      IF NOT EXISTS 
         _zx_t1 
         ( id bigint
         , fld2 bigint
         , UNIQUE (id)
         );
    -- unique records
    INSERT INTO _zx_t1 SELECT 1, 99;
    INSERT INTO _zx_t1 SELECT 2, 98;


    WITH 
      _cte_sel_row AS 
       (   SELECT                 -- start unit of work with read
              id as _b4_id        -- fields need to be aliased 
             ,fld2 as _b4_fld2    -- to prevent ambiguous column errors
           FROM _zx_t1 
           WHERE id = 2
           FOR UPDATE 
       )
     , _cte_sel_up_ret AS           -- we're in the same UOW
       (  UPDATE _zx_t1             -- actual table
           SET fld2 = _b4_fld2 + 1  -- some actual work
          FROM  _cte_sel_row    
            WHERE id = _b4_id
               AND fld2 < _b4_fld2 + 1  -- gratuitous but illustrates the point 
          RETURNING id as _aft_id, fld2 as _aft_fld2
         ) 
    SELECT  
          _cte_sel_row._b4_id
         ,_cte_sel_row._b4_fld2         -- before
         ,_cte_sel_up_ret._aft_id  
         ,_cte_sel_up_ret._aft_fld2     -- after
       FROM _cte_sel_up_ret  
          INNER JOIN _cte_sel_row  
           ON TRUE AND _cte_sel_row._b4_id = _cte_sel_up_ret._aft_id
    ;

 RESULT: 
           _b4_id | _b4_fld2 | _aft_id | _aft_fld2 
          --------+----------+---------+-----------
                2 |      209 |       2 |       210

See also: https://rob.conery.io/2018/08/13/transactional-data-operations-in-postgresql-using-common-table-expressions/

Answer 4

Elaborating on skif1979's "DelSert" CTE method, the "Logged DelSert:"

-- setups
DROP TABLE IF EXISTS _zx_t1 ;

CREATE TEMP TABLE 
  IF NOT EXISTS 
     _zx_t1 
     ( id bigint
     , fld2 bigint
     , UNIQUE (id)
     );
-- unique records
INSERT INTO _zx_t1 SELECT 1, 99;
INSERT INTO _zx_t1 SELECT 2, 98;


WITH 
  _cte_del_row AS 
   (   DELETE 
       FROM _zx_t1 
       WHERE id = 2 
     RETURNING id as _b4_id, fld2 as _b4_fld2 -- returns complete deleted row
   )
 , _cte_delsert AS
     (  INSERT 
       INTO _zx_t1 
       SELECT DISTINCT 
          _cte_del_row._b4_id
        , _cte_del_row._b4_fld2 + 1 
        from (SELECT null::integer AS _zunk) _zunk  -- skif1979's trick here
             LEFT OUTER JOIN _cte_del_row         -- clever LOJ magic  
             ON TRUE                              -- LOJ cartesian product
        RETURNING id as _aft_id , fld2 as _aft_fld2 -- return newly "delserted" rows
       )
  SELECT * -- returns before & after snapshots from CTE's
  FROM 
   _cte_del_row
   , _cte_delsert ; 

 RESULT: 
           _b4_id | _b4_fld2 | _aft_id | _aft_fld2 
          --------+----------+---------+-----------
                2 |      209 |       2 |       210

AFAICT these all occur linearly w/in a unit of work, akin to a journaled or logged update.

• Workable for

  • Child records
  • OR Schema w/ no FK
  • OR FK w/ cascading deletes

• Not workable for

  • Parent records w/ FK & no cascading deletes

Answer 5

CREATE TABLE test_table (value TEXT UNIQUE);
INSERT INTO test_table SELECT 'value 1';
INSERT INTO test_table SELECT 'value 2';

WITH delete_row AS (DELETE FROM test_table WHERE value='value 2' RETURNING 0)
  INSERT INTO test_table
    SELECT DISTINCT 'value 2' 
    FROM (SELECT 'dummy') dummy
    LEFT OUTER JOIN delete_row ON TRUE
    RETURNING *;

The query above handles the situations when DELETE deletes 0/1/some rows.