On Saturday, February 10, 2018, PG Doc comments form <
noreply@postgresql.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.
"""
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.
