Re: Order by lower(column-alias) doesn't work... - Mailing list pgsql-general

On Thu, 28 May 2020 at 13:14, Andreas Joseph Krogh <andreas@visena.com> wrote:
> This works:
> select p.firstname, p.lastname, p.firstname || p.lastname as fullname from onp_crm_person p order by fullname;
>
> But this doesn't:
> select p.firstname, p.lastname, p.firstname || p.lastname as fullname from onp_crm_person p order by
lower(fullname);
> ERROR:  column "fullname" does not exist
> LINE 1: ... as fullname from onp_crm_person p order by lower(fullname);

Wrap the original query in either a CTE or a temporary table.
eg

=> SELECT REPLACE(name, '_', ' ') AS nm FROM subs ORDER BY lower(nm);
ERROR:  column "nm" does not exist
=> SELECT * FROM (SELECT REPLACE(name, '_', ' ') AS nm FROM subs) AS t
ORDER BY lower(nm);
[results]
=> WITH t AS (SELECT REPLACE(name, '_', ' ') AS nm FROM subs)   SELECT
* FROM t ORDER BY lower(nm);
[results]

Geoff