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).

Felker

Insert and delete do not seem to be connected, but you want to return all rows from both operations? – Appreciation

@ErwinBrandstetter could really care less about the delete, but wasn't sure that was good syntax, was going to deal with that after I could get the operations to perform in order – Felker

Well, that last bit may be a problem. Please update your question defining how exactly you expect both to perform "in order". – Appreciation

Thanks Erwin, updated; you are correct – Felker

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.

Appreciation

@vol7ron: Well, UNION evaluates from left to right (see last chapter here: https://mcmap.net/q/1622209/-select-first-record-if-none-match). You can also reference what you get from the RETURNING clause in the next CTE to force an order of evaluation. But CTEs always see the same snapshot of underlying tables. – Appreciation

So there could exist a race condition with another query, there's no table-level or constraint reservation, but within the query, it would act sequentially as expected, correct? Just want to make sure even though it's left to right, that it doesn't operate in parallel, or jump ahead until one operation is finished. – Felker

@vol7ron: Not sure what you are getting at. If there is any overlap between DELETE and INSERT and there are unique constraints, you cannot use two data-modifying CTEs of a single statement for this. You need to delete in a separate statement first, as per my answer. – Appreciation

I'm suspecting that union is the workaround and does enable that behavior; that they may be different snapshots, or at least the same snapshot is processed by both, in order from left-to-right (the reason why your query worked before the edit) – Felker

I added a link to documentation. You don't have to take my word. Read at the source. – Appreciation

yes, I saw that like and read those paragraphs. I also know from talking to some of the developers that the documentation doesn't always include the full scale of information. I think @depesz has written a blog post a few years ago about race conditions, most likely related to UPSERT queries (I forget), but that passage in the link you posted is not clear when you consider the combination of a UNION; regardless, thanks for all the attention and your help (as always) – Felker

Addition: I think you should also include your first query (before the edit), unless you think there is something inherently wrong. It is a good example of how to show the records affected and inserted. – Felker

Well, the first query is wrong for the purpose. Curious readers can see it in the edit history. – Appreciation

Answer 2

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.

Federalese

Answer 3

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

Sackett

Answer 4

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/

Sackett

Answer 5

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)

Sgraffito