On Mon, 2026-10-05 at 10:26 +0200, Carl.Weierstrass wrote:
> I want to delete some data from table test1 und protocol the deleted
> data in table test2:
>
> The solution with two statements
>
> begin;
> insert into test2
> select * from test1
> where 1 = 1;
>
> delete from test1
> where 1 = 1
> returning *; -- show deleted lines
> commit;
>
> works fine, but the more elegant version in one single statement results
> in a syntax error
>
> begin;
> insert into test2
> delete from test1
> where 1 = 1
> returning *; --return deleted lines
> commit;
>
> psql:test.sql:32: FEHLER: Syntaxfehler bei »delete«
> ZEILE 2: delete from test1
>
> Is this a bug or a feature?
That's incorrect syntax.
Try
WITH cte AS (
DELETE FROM test1
RETURNING *
)
INSERT INTO test2
SELECT * FROM cte;
Yours,
Laurenz Albe
