Re: postgresql order lowercase before uppercase

[basti <mailinglist@xxxxxxxxxxxxxxxx>]

On 19.03.21 08:33, Laurenz Albe wrote:
> 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

This does not work for me. The same result as normal order.

Amm
andere Marken
Ani
Anti
Bra
Cali

Test data attached.

Attachment: table.sql
Description: application/sql