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


FAQ