Postgresql unicode LOWER()

Question

The following query:

select lower('ALGODÓN'), upper('algodón')

Results in:

  lower  |  upper
---------+---------
 algodÓn | ALGODóN
(1 row)

Python, on the other hand, gets this right:

>>> 'ALGODÓN'.lower()
'algodón'

Is there a way to get postgres to convert case of non-ascii characters properly?

There's no column, the query above works as shown on a default install of postgres 13.5 — jstaab
– jstaab, Commented Feb 15, 2022 at 21:40
Then, you'll probably need to enforce a collation since the default collation is not what you need. From the manual "... If the expression is a constant, the collation is the default collation of the data type of the constant..." at postgresql.org/docs/14/collation.html — The Impaler
– The Impaler, Commented Feb 15, 2022 at 21:41
The world doesn't agree on how to sort and change cases, though there are ways to do it which will be more correct more often, so we need collations and locales. — Schwern
– Schwern, Commented Feb 15, 2022 at 21:58

Laurenz Albe · Accepted Answer · 2022-02-15 21:42:18Z

4

You are using the wrong collation. For example, with the C collation:

SELECT lower('ALGODÓN' COLLATE "C"), upper('algodón' COLLATE "C");

  lower  │  upper  
═════════╪═════════
 algodÓn │ ALGODóN
(1 row)

But with en_US.utf8 (Linux):

SELECT lower('ALGODÓN' COLLATE "en_US.utf8"), upper('algodón' COLLATE "en_US.utf8");

  lower  │  upper  
═════════╪═════════
 algodón │ ALGODÓN
(1 row)

The language-agnostic ICU collation gets it right too:

SELECT lower('ALGODÓN' COLLATE "und-x-icu"), upper('algodón' COLLATE "und-x-icu");

  lower  │  upper  
═════════╪═════════
 algodón │ ALGODÓN
(1 row)

answered Feb 15, 2022 at 21:42

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql unicode LOWER()

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related