DISTINCT ON (col) col_alias – alias not working

Question

I am trying to run this command:

SELECT DISTINCT ON (_id) test FROM creator_map.infos;

which is giving me the error:

ERROR: column "test" does not exist

but according to the following tutorial, the test should be an alias

SELECT
 DISTINCT ON
 (column_1) column_1_alias,
 column_2
FROM
 tbl_name
ORDER BY
 column_1,
 column_2;

Source.

How do I get the distinct to become an alias?

Answer 1

You can't give on (...) an alias because it's not expression that "returns" a name. It's like trying to give an IN condition an alias where a in (1,2,3) as foo

If in doubt, read the manual. It does not mention an alias there.

You can only define an alias for an expression in the actual select list, and the (...) is not part of the select list, but part of the distinct on expression.

I think it's just a matter of bad wording on that tutorial page, and should read:

select distinct on (column1) 
          column1 as column_1_alias,
          column2
from ....