Обсуждение: update returning order by syntax error question
Hi, postgresql-9.6.15 I just tried something like: select * from (update tblname t set ... where ... returning ...) order by ...; assuming it would work but it didn't. That's OK. I found on stackoverflow that a CTE can be used to do it: with u as (update tblname t set ... where ... returning ...) select * from u order by ...; What surprises me is the syntax error: ERROR: syntax error at or near "t" LINE 2: tblname t ^ If the syntax was invalid because an update returning statement can't appear in a from clause, I'd expect the error to be at the token "update". It's almost as if the parser sees "update" as a possible table name (rather than a reserved word) and "tblname" as the alias for that table and it's expecting a comma or left/right/full etc. when it seess the "t". Anyone care to explain why the error is what it is? It's no big deal. I'm just curious. cheers, raf
On Thu, Sep 12, 2019 at 5:45 AM raf <raf@raf.org> wrote: > ERROR: syntax error at or near "t" > LINE 2: tblname t This works on 9.6.9 and 11.4: luca=> with u as ( update t_all set id = 5 where id <= 5 returning *) select * from u; id | ref_id ----+-------- 5 | 1 (1 row) luca=> select version(); version -------------------------------------------------------------------------------------------------- PostgreSQL 9.6.9 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.2.0-8ubuntu3.2) 7.2.0, 64-bit (1 row) However, I know for sure that UPDATE has some restrictions on the table aliasing (at least, they are not used as for a SELECT), so the problem could be in the real query you are executing. It works with or without the order by. Luca
raf <raf@raf.org> writes: > It's almost as if the parser sees "update" as a possible > table name (rather than a reserved word) and "tblname" > as the alias for that table and it's expecting a comma > or left/right/full etc. when it seess the "t". No, it's *exactly* as if that. UPDATE is an unreserved keyword so it's fully legitimate as a table name. If you made the syntax be what the grammar is expecting: regression=# select * from (update t cross join update t2); ERROR: relation "update" does not exist LINE 1: select * from (update t cross join update t2); ^ regards, tom lane
On 9/12/19 6:44 AM, Tom Lane wrote: > raf <raf@raf.org> writes: >> It's almost as if the parser sees "update" as a possible >> table name (rather than a reserved word) and "tblname" >> as the alias for that table and it's expecting a comma >> or left/right/full etc. when it seess the "t". > > No, it's *exactly* as if that. UPDATE is an unreserved > keyword so it's fully legitimate as a table name. > If you made the syntax be what the grammar is expecting: > > regression=# select * from (update t cross join update t2); > ERROR: relation "update" does not exist > LINE 1: select * from (update t cross join update t2); I am not following. PostgreSQL 11.5 The OP had: with u as (update tblname t set ... where ... returning ...) select * from u order by ...; I tested with: WITH u AS ( UPDATE up_test t SET col_2 = col_2 + 1 WHERE id < 3 RETURNING * ) SELECT * FROM u; BEGIN id | col1 | col_2 ----+------+------- 1 | t | 2 2 | NULL | 3 (2 rows) I'm missing something. > ^ > > regards, tom lane > > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 9/12/19 6:44 AM, Tom Lane wrote: >> No, it's *exactly* as if that. UPDATE is an unreserved >> keyword so it's fully legitimate as a table name. > I am not following. Sure, the WITH thing works too. The point is that given "SELECT ... FROM (UPDATE ...)", there is a workable parse path where UPDATE is treated as a table name. So if you try to put an UPDATE command there, the syntax error isn't thrown till a couple tokens later, where the table-name syntax no longer matches. The OP's question was about why the error was thrown where it was, not about how to do this correctly. regards, tom lane