postgresql order lowercase before uppercase

Maybe check out using the UPPER/LOWER/INITCAP functions in the order by clause?

Bobb

This does not help in that case.

to me, collate 'C' ask for the raw ascii order which put caps before
because the hexa coding is lower
did you try any thing else (POSIX is same as 'C')

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Thu, Mar 18, 2021 at 4:18 PM basti <mailinglist@unix-solution.de> wrote:

Exactly.  "C" collation is the opposite of what he wants.

OP needs something like "de_DE".

On 3/18/21 10:36 AM, Marc Millas wrote:

to me, collate 'C' ask for the raw ascii order which put caps before
because the hexa coding is lower
did you try any thing else (POSIX is same as 'C')

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com <http://www.mokadb.com&gt;

On Thu, Mar 18, 2021 at 4:18 PM basti <mailinglist@unix-solution.de
<mailto:mailinglist@unix-solution.de>> wrote:

This does not help in that case.

On 18.03.21 15:45, Basques, Bob (CI-StPaul) wrote:

Maybe check out using  the UPPER/LOWER/INITCAP functions in the

order by clause?

Bobb

-----Original Message-----
From: basti <mailinglist@unix-solution.de

<mailto:mailinglist@unix-solution.de>>

Sent: Thursday, March 18, 2021 9:40 AM
To: pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>
Subject: postgresql order lowercase before uppercase

Think Before You Click: This email originated outside our organization.

Hello,
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

Best Regards

--
Angular momentum makes the world go 'round.

or, maybe, just nothing ?

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

On Thu, Mar 18, 2021 at 4:44 PM Ron <ronljohnsonjr@gmail.com> wrote:

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@CaseFirst=LowerFirst");

Then use

ORDER BY name COLLATE inv

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

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@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.

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

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.

On 19.03.21 10:12, basti wrote:

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
....

Laurenz' approach is sound, it just needs a little tweak to not trip up
on the "andere Marken" uppercase M. Try this:

select id,
marke.name from marke
ORDER BY
left(marke.name,1) <> left(lower(marke.name),1),
marke.name

Fiddle:
http://sqlfiddle.com/#!17/d9d83e/9

Cheers
Christian

--
Christian Ramseyer, netnea ag
Network Management. Security. OpenSource.
https://www.netnea.com

On Fri, 2021-03-19 at 10:12 +0100, basti wrote:

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.

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

Amm
andere Marken
Ani
Anti
Bra
Cali

You are allowed to adapt the query to your special needs.
But if you need to be spoonfed:

SELECT a.name
FROM foo as a
ORDER BY a.name = initcap(a.name), a.name;

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