bug or feature
bug or feature
От:
"Carl.Weierstrass" <carl.weierstrass@googlemail.com>
Дата:
Hi, 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? Charly
Re: bug or feature
От:
Laurenz Albe <laurenz.albe@cybertec.at>
Дата:
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
Re: bug or feature
От:
"David G. Johnston" <david.g.johnston@gmail.com>
Дата:
On Monday, October 5, 2026, Carl.Weierstrass <carl.weierstrass@googlemail.com> wrote:
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?
If you make something up that isn’t documented it shouldn’t work. You can call the inability to interpret random stuff you send to the server a feature if you need an apply a label.
You might want to look at the WITH clause of those commands to see how to go about combining multiple distinct commands into one execution correctly.
David J.
Re: bug or feature
От:
Adrian Klaver <adrian.klaver@aklaver.com>
Дата:
On 10/5/26 7:32 AM, David G. Johnston wrote: > On Monday, October 5, 2026, Carl.Weierstrass > > wrote: > > 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? > > > If you make something up that isn’t documented it shouldn’t work. You > can call the inability to interpret random stuff you send to the server > a feature if you need an apply a label. > > You might want to look at the WITH clause of those commands to see how > to go about combining multiple distinct commands into one execution > correctly. In particular: https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING > > David J. > -- Adrian Klaver adrian.klaver@aklaver.com