5

I want to delete then insert with single statement using a CTE in Postgres. We could use an explicit transaction and 2 statements, but want to avoid that if possible. I don't know if there will be zero or 1+ rows deleted, so I don't think I can use a single WHERE EXISTS or WHERE NOT EXISTS to ensure that the delete statement in the CTE runs first. Here's something like what I envision:

WITH deletions AS (
   DELETE FROM foo WHERE a = 'abc' and b = 2 
)
INSERT INTO FOO (a, b, c) VALUES ('abc', 2, 'new value')

but I need a way to force the CTE to run first. There's no pk on the table that would create issues with attempting to update the same record twice in the same transaction.

8
  • This should work just fine. What is the error you get?
    – user1822
    May 15, 2020 at 20:17
  • @a_horse_with_no_name The order of the statements might be anything, so it might delete the inserted record, no?
    – Josh
    May 15, 2020 at 20:21
  • No, it will not. Why don't you simply try it? But why don't you use insert .. on conflict do update instead?
    – user1822
    May 15, 2020 at 20:28
  • Please lead with your version of Postgres and your table definition. (CREATE TABLE statement). The best answer heavily depends on existing table constraints. Data types may matter, too. Also, why so you want to delete and insert and why avoid two statements? Two statements may be the best solution ... May 15, 2020 at 20:42
  • @a_horse_with_no_name How do you know it won't? I thought order of CTEs was not guaranteed.
    – Josh
    May 15, 2020 at 21:39

2 Answers 2

5

Without a UNIQUE constraint standing in the way, your original query works just fine. See below.
I would throw in a separate CTE to provide all input values once:

WITH input(a, b, c) AS (VALUES ('abc', 2, 'new value'))  -- provide values once
   , del AS (
   DELETE FROM foo AS f
   USING  input i
   WHERE  f.a = i.a
   AND    f.b = i.b
   )
INSERT INTO foo (a, b, c)
TABLE input;

The DELETE cannot delete rows from the INSERT in the same statement, as both see the same snapshot of underlying tables. Meaning, the DELETE cannot see the rows entered by the INSERT in the other CTE. Both are executed at the same time, virtually. Related:

That's also the reason why this cannot work with a UNIQUE index on (a.b). Uniqueness is enforced at all times. The INSERT still sees rows being deleted in the other CTE. The obvious alternative would be an UPSERT. But that cannot be the case, since you mentioned that there is no PK and the DELETE can delete 0-n rows.

As a_horse commented: would work with a deferrable constraint, though. See:

But deferrable constraints are considerably more expensive and don't work with FK constraints, nor as arbiter in UPSERT statements ...

Note that a free-standing VALUES expression may require explicit type casts. See:

Related:


That said, I don't see how this is superior to a DELETE and a separate INSERT in a single transaction - which also works with UNIQUE constraints. You commented:

It's a lot easier to use db parameters with a single statement and our db library.

If the statement is used a lot, consider a function:

CREATE OR REPLACE FUNCTION f_foo_delins(_a text, _b int, _c text)  -- actual types
  RETURNS void LANGUAGE sql AS
$func$
   DELETE FROM foo
   WHERE  a = _a
   AND    b = _b;

   INSERT INTO foo ( a,  b,  c)
   VALUES          (_a, _b, _c);
$func$;

Then the call is simple:

SELECT f_foo_delins('abc', 3, 'new value');

If you only need it for some sessions, a temporary function might be an option. See:

Or a prepared statement with the above CTE:

PREPARE foo_delins(text, int, text) AS
WITH del AS (
   DELETE FROM foo
   WHERE  a = $1
   AND    b = $2
  )
INSERT INTO foo ( a,  b,  c)
VALUES          ($1, $2, $3);

Call:

EXECUTE foo_delins('abc', 4, 'new value');

Most languages have their own implementation of prepared statements using libpq ...

Prepared statement and function know their input types. Explicit type casts are not required (except special cases).

Related:

2
  • 1
    "That's also the reason why this cannot work with a UNIQUE index" - it would work with a unique constraint that's defined as deferrable though.
    – user1822
    May 15, 2020 at 21:44
  • @a_horse_with_no_name: Good point. stackoverflow.com/a/10035119/939860 A UNIQUE index is always non-deferrable. though. And deferrable constraints are considerably more expensive. May 15, 2020 at 22:05
0

you can return whatever is deleted inside CTE and insert it in the table.

WITH deletions AS (
    DELETE FROM foo WHERE a = 'abc' and b = 2 returning a,b
)
INSERT INTO FOO (a, b, c) 
    select a,b,'new value' from deletions;
2
  • But deletions might be empty, which would mean nothing gets inserted, right? @AG.
    – Josh
    May 15, 2020 at 20:20
  • Correct. Will insert whatever is deleted.
    – AG.
    May 16, 2020 at 22:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.