Re: postgresql order lowercase before uppercase

Laurenz Albe <laurenz.albe@xxxxxxxxxxx> · Fri, 19 Mar 2021 08:33:18 +0100

On Thu, 2021-03-18 at 23:51 +0100, basti wrote:
> Am 18.03.21 um 17:19 schrieb Laurenz Albe:
> > On Thu, 2021-03-18 at 15:39 +0100, basti wrote:
> > > I need to as follow:
> > > 
> > > ande
> > > Amma
> > > Anit
> > > Anti
> > > Brac
> > > Cali
> > > ....
> > 
> > Create an ICU collation:
> > 
> >   CREATE COLLATION inv (PROVIDER = icu, LOCALE = "en-US@CaseFirst=LowerFirst");
> 
> I have try the inv collate, that does not work on PG 9.6 and 10.
> 
> Order is the same as without collate.
> 
> SELECT a.name
>     FROM foo as a
>     LEFT JOIN (SELECT name from foo WHERE name = 'lowercase name') as b
>     on a.name = b.name
>     ORDER by b.name,a.name
> 
> does it.
> 
> perhaps there is a better way?

Ah, I misunderstood what you want.

  SELECT a.name
  FROM foo as a
  ORDER BY a.name <> lower(a.name), a.name;

That works because FALSE < TRUE.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com