Functions in sort order - undocumented restriction

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: andrew(at)grillet(dot)co(dot)uk
Subject: Functions in sort order - undocumented restriction
Date: 2018-02-10 11:40:30
Message-ID: 151826283029.1443.7699948679319699778@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs

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.

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message Alvaro Herrera 2018-02-10 11:59:37 Re: Documentation of EXCEPT ALL may have a bug
Previous Message PG Doc comments form 2018-02-10 08:27:10 Engineering