Re: Functions in sort order - undocumented restriction

On Saturday, February 10, 2018, PG Doc comments form <noreply(at)postgresql(dot)org>
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/9.5/static/queries-order.html
> Description:
>
> This works:
>
> select distinct o.bid, organisation, posttown, replace(case when phone =''
> then null else trim(phone) end, ' ', ''), phone, o.active, website, email,
> (select count(*) from notes as n where n.bid = o.bid) as nn from
> organisations as o right join notes as n on o.bid = n.bid where true order
> by replace(case when phone ='' then null else trim(phone) end, ' ', '')
> nulls last ;
>
> This does not work:
>
> select distinct (o.bid), organisation, posttown, replace(case when
> postcode
> ='' then null else trim(postcode) end, ' ', '') as pc, phone, o.active,
> website, email, (select count(*) from notes as n where n.bid = o.bid) as nn
> from organisations as o right join notes as n on o.bid = n.bid where true
> order by replace(case when phone ='' then null else trim(phone) end, ' ',
> '') nulls last ;
> ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select
> list

I suppose that error message should say "FOR SELECT DISTINCT ON, ORDER BY
..."

> LINE 1: ...n notes as n on o.bid = n.bid where true order by replace(ca...
>
> The documentation does not explain the restriction,

It is explained in the SELECT command documentation.

"""
The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).
The ORDER BY clause will normally contain additional expression(s) that
determine the desired precedence of rows within each DISTINCT ON group.
"""

https://www.postgresql.org/docs/9.5/static/sql-select.html

>
> nor that, or why, you
> cannot use 'as' to rename the field and then cite the renamed version.
>

You can and it does with limitations.

"Note that an output column name has to stand alone, that is, it cannot be
used in an expression"

You are correct that the "why" behind some of the decisions and limitations
is not always covered in great detail.

I suppose that the sorting docs could cover DSTINCT ON, it just needs
someone willing to write it up. Since the SELECT docs cover the material
it is a usability issue as opposed to a correctness one. Also, that
chapter is somewhat of an introductory piece and distinct on is somewhat of
an intermediate query concept. Adding it there might be considered too
much information at that point in the docs. Given the directness of the
error and the coverage in the command reference the status quo has merit.

David J.