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