Incorrect sort/collation/order with spaces in Postgresql 9.4

Question

I'm using Postgresql 9.4.5. When I go to psql and run \l I get

Encoding is UTF8
Collate is en_US.UTF-8 
cCtype is en_US.UTF-8

I have products table with a name column that has the following names:

T-700A Grouped
T-700 AGrouped
T-700A Halved
T-700 Whole

When I execute the following SQL in pql

SELECT name FROM products WHERE name LIKE '%T-700%' ORDER By name ASC;

I get the following output

T-700A Grouped
T-700 AGrouped
T-700A Halved
T-700 Whole

That sorting doesn't look natural. I expected to get

T-700 AGrouped
T-700 Whole
T-700A Grouped
T-700A Halved

It doesn't seem like Postgres is handling spaces the way I expected. Can anyone explain what is happening and suggest a way to fix this?

Yah, that looks like a similar issue. They don't really solve the issue in a global way though. I'd like to be able to setup the database in a way that all text columns behave the same way by default. — CodeSmith
– CodeSmith, Commented Dec 30, 2015 at 22:45

Community · Accepted Answer · 2017-05-23 12:32:23Z

31

On Unix/Linux SE, a friendly expert explained that what you see is the proper way to sort Unicode. Basically, the standard is trying to sort:

di Silva Fred                  di Silva Fred
di Silva John                  diSilva Fred
diSilva Fred                   disílva Fred
diSilva John         ->        di Silva John
disílva Fred                   diSilva John
disílva John                   disílva John

Now if spaces were as important as letters, the sort could not separate the various identical spellings of Fred and John. So what happens is that it first sorts without spaces. Then in a second pass, strings that are the same without whitespace are sorted. (This is a simplification, the real algorithm looks fairly complex, assigning whitespace, accents and non-printable characters various levels of precedence.)

You can bypass the Unicode collation by setting:

export LC_ALL=C

Or in Postgres by casting to byte array for sorting:

order by name::bytea

Or (from Kiln's answer) by specifying the C collation:

order by name collate "C"

Or by altering the default collation for the column:

alter table products alter column name type text collate "C";

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Dec 30, 2015 at 22:38

Andomar

239k55 gold badges387 silver badges412 bronze badges

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

4 Comments

Andomar Over a year ago

I've asked the question on the linux / unix stackexchange... perhaps a unix guru knows the answer :)

jdcookie Over a year ago

This just saved me, what a couple of hours it has been

Tomáš Cerha Over a year ago

Given solutions work for English, but what if I still need to sort byl locale dependent rules? I need unicode sorting respecting words. "On vacation" must come before "One".

Natan Over a year ago

Should note that this may have negative performance effect on the index usage. dba.stackexchange.com/questions/260267/…

Collectives™ on Stack Overflow

Incorrect sort/collation/order with spaces in Postgresql 9.4

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related