Re: postgresql order lowercase before uppercase

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
>> ....
>>
>> I have try ORDER by name COLLATE 'C';
>>
>> but this order as follow:
>>
>> Amma
>> Anit
>> Anti
>> Brac
>> Cali
>> ....
>> ande
>
> Create an ICU collation:
>
> CREATE COLLATION inv (PROVIDER = icu, LOCALE = "en-US(at)CaseFirst=LowerFirst");
>
> Then use
>
> ORDER BY name COLLATE inv
>
> Yours,
> Laurenz Albe
>

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?
There is only one name in lowercase and this one must be the first one.