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

Question

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.

• user1822: This should work just fine. What is the error you get?
• Josh: @a_horse_with_no_name The order of the statements might be anything, so it might delete the inserted record, no?
• user1822: No, it will not. Why don't you simply try it? But why don't you use insert .. on conflict do update instead?
• Erwin Brandstetter: 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 ...
• Josh: @a_horse_with_no_name How do you know it won't? I thought order of CTEs was not guaranteed.

Answer 1

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:

• Why can't rows inserted in a CTE be updated in the same statement?

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:

• Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

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:

• Casting NULL type when updating multiple rows

Related:

• How to use RETURNING with ON CONFLICT in PostgreSQL?

---

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:

• How to create a temporary function in PostgreSQL?

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:

• What is the difference between prepared statements and SQL or PL/pgSQL functions, in terms of their purpose?

• user1822: "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.
• Erwin Brandstetter: @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.

Answer 2

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;

• Josh: But deletions might be empty, which would mean nothing gets inserted, right? @AG.
• AG.: Correct. Will insert whatever is deleted.