update returning order by syntax error question

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

update returning order by syntax error question

raf-6
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



Reply | Threaded
Open this post in threaded view
|

Re: update returning order by syntax error question

Luca Ferrari-2
On Thu, Sep 12, 2019 at 5:45 AM raf <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: update returning order by syntax error question

Tom Lane-2
In reply to this post by raf-6
raf <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: update returning order by syntax error question

Adrian Klaver-4
On 9/12/19 6:44 AM, Tom Lane wrote:

> raf <[hidden email]> 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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: update returning order by syntax error question

Tom Lane-2
Adrian Klaver <[hidden email]> 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