Re: Order by lower(column-alias) doesn't work...
På torsdag 28. mai 2020 kl. 15:26:42, skrev Tom Lane mailto:t...@sss.pgh.pa.us>>: Andreas Joseph Krogh writes: > Is there a way to define "sorting-rules" on custom-types so that I can have > ORDER BY and PG will pick my custom odering? You'd have to write your own type, which would be a lotta work :-(. A possible partial answer is to define the composite type as firstname citext, lastname citext, other-fields-here and then the regular composite-type comparison rule would give you approximately what you said you wanted ... but only approximately. regards, tom lane Hm, ok. I think the most non-intrusive way for me is to craft a sub-select producing the "varchar-string-flattened" so I can order by that alias. -- Andreas Joseph Krogh
Re: Order by lower(column-alias) doesn't work...
Andreas Joseph Krogh writes: > Is there a way to define "sorting-rules" on custom-types so that I can have > ORDER BY and PG will pick my custom odering? You'd have to write your own type, which would be a lotta work :-(. A possible partial answer is to define the composite type as firstname citext, lastname citext, other-fields-here and then the regular composite-type comparison rule would give you approximately what you said you wanted ... but only approximately. regards, tom lane
Re: Order by lower(column-alias) doesn't work...
På torsdag 28. mai 2020 kl. 14:50:54, skrev Geoff Winkless mailto:pgsqlad...@geoff.dj>>: On Thu, 28 May 2020 at 13:14, Andreas Joseph Krogh 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 [..] Yea, I was hoping to avoid that, as the query is generated and rewriting it is a pain... Is there a way to define "sorting-rules" on custom-types so that I can have ORDER BY and PG will pick my custom odering? -- Andreas Joseph Krogh
Re: Order by lower(column-alias) doesn't work...
On Thu, 28 May 2020 at 13:14, Andreas Joseph Krogh 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
