Why 'A' > 'a' is true in one of my Posgresql databases?

Question

In one of my PostgreSQL databases, the following statement returns true! The other works fine.

select 'A'>'a'

LC_COLLATE and LC_TYPE in all of my databases is en_US.UTF-8. client_encoding is UTF8

My database version is 13.2. OS is Linux.

In Unicode order, 'A' shall be smaller than 'a', but it isn't. Any configuration I shall look at?

Is it OS dependent? The other database that works correctly is running on MacOS.

--

select key from json_each('{"A":1, "a":2}') order by key => a and then A!

More weird: select key from json_each('{"A":1, "a":2, ":": 3, ":a": 4}') order by key => :, a, :a and then A

--

SQL Fiddle the same weird behavior, but DB Fiddle is correct (but its collation is C).

Does this answer your question? [Alphanumeric case in-sensitive sorting in postgres](https://stackoverflow.com/questions/15427529/alphanumeric-case-in-sensitive-sorting-in-postgres) — O. Jones, Jan 08 '22 at 14:31
No, the question you pointed to is about how to do case sensitive and insensitive. But, my question is that my installation seems violate the basic string comparison rule! — Tom Yeh, Jan 08 '22 at 14:37
Returns false here. BTW, what does `select 'B'>'a'` give you? — wildplasser, Jan 08 '22 at 15:21

Bohemian · Answer 1 · 2022-01-08T15:58:15.250

1

The character type, defined by LC_CTYPE is probably en_US.utf8, which is not the same as UTF-8.

Its a non-IANA character type that supports lowercase letters being before uppercase.

Oddly, SQLFiddle is also afflicted, likewise returning true for 'A' > 'a'

edited Jan 08 '22 at 15:58

answered Jan 08 '22 at 15:53

Bohemian

`en_US.utf8` is the same as `en_US.UTF-8`, right? I verified with another test server running on Linux. It returns `true` too! – Tom Yeh Jan 08 '22 at 16:02
4

No, utf8 and UTF-8 are different. See https://serverfault.com/questions/616744 – Bohemian Jan 08 '22 at 16:05

1 Answers1