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.
2 Answers 2
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:
-
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– user18222020年05月15日 21:44:08 +00:00Commented 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.Erwin Brandstetter– Erwin Brandstetter2020年05月15日 22:05:08 +00:00Commented May 15, 2020 at 22:05
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;
insert .. on conflict do update
instead?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 ...