Postgresql – Want to delete then insert with single statement using a CTE in Postgres

ctepostgresql

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.

Best Answer

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:

Related Question