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
LINE 1: ...n notes as n on o.bid = n.bid where true order by replace(ca...
The documentation does not explain the restriction, nor that, or why, you
cannot use 'as' to rename the field and then cite the renamed version.