BUG #17232: DISTINCT ON does not allow AS
The following bug has been logged on the website:
Bug reference: 17232
Logged by: Kyle Lahnakoski
Email address: kyle@lahnakoski.com
PostgreSQL version: 14.0
Operating system: windows
Description:
The following SQL has an unexpected syntax error
SELECT DISTINCT ON (col) AS col, col2 FROM test
The AS clause is usually used to indicate alias, but it does not work in
this case
```
postgres=#
postgres=# SELECT version();
version
------------------------------------------------------------
PostgreSQL 14.0, compiled by Visual C++ build 1914, 64-bit
(1 row)
postgres=# SELECT DISTINCT ON (col) AS col, col2 FROM test;
ERROR: syntax error at or near "AS"
LINE 1: SELECT DISTINCT ON (col) AS col, col2 FROM test;
```
On Friday, October 15, 2021, PG Bug reporting form <noreply@postgresql.org>
wrote:
The following bug has been logged on the website:
Bug reference: 17232
Logged by: Kyle Lahnakoski
Email address: kyle@lahnakoski.com
PostgreSQL version: 14.0
Operating system: windows
Description:The following SQL has an unexpected syntax error
SELECT DISTINCT ON (col) AS col, col2 FROM test
The AS clause is usually used to indicate alias, but it does not work in
this case
No it doesn’t. Why do you think it should? The columns in the ON parens
are not output so they don’t ned aliases.
David J.
On 2021-Oct-15, PG Bug reporting form wrote:
The following SQL has an unexpected syntax error
SELECT DISTINCT ON (col) AS col, col2 FROM test
Well, the "ON (col)" part is not an output column -- it's just the
specification of the column that you want distinctness over. I suspect
you mean
SELECT DISTINCT ON (col) col AS col, col2 FROM test
... which looks like a pretty bogus thing to do, since you have no say
on *which* values of col2 are you going to get for each distinct value
of col. But ... it gives *some* result, at least.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Pensar que el espectro que vemos es ilusorio no lo despoja de espanto,
sólo le suma el nuevo terror de la locura" (Perelandra, C.S. Lewis)