CODEWITHSUNDEEP
postgresqlutf-8localizationpostgresql-9.2
31 bit
31 bit

Reputation: 335

How to sort Norwegian text on Postgres

I'm having trouble sorting Norwegian text column in Postgres. My environment:

db=# select version();
PostgreSQL 9.2.14 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

the database is hosted on RedHat Openshift.

when running >locale command I get:

**edit

db=#\l
          Name           |    Owner     | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------------------+--------------+----------+-------------+-------------+-----------------------
 db                      | myadminUser  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

this is what I tried, this sql shows the default sort is incorrect:

db=# select * from unnest(ARRAY['a','b','c','d','A','B','C','å','ø','z','Z','Ø']) as t1 order by t1;

result: a A å b B c C d ø Ø z Z

(I think this sort order is even wrong for English, Capital 'A' should come before 'a' isn't it ?)

then I tried :

db=# CREATE COLLATION nor (LOCALE = 'nn_NO.utf8');

and the same statement from before:

db=# select * from unnest(ARRAY['a','b','c','d','A','B','C','å','ø','z','Z','Ø']) as t1 order by t1 collate nor;

Now the result is: A a B b C c d Z z Ø ø å

This looked really good, I thought I'm done.. BUT then I tried:

db=# select * from unnest(ARRAY['aaaa','bbbb','cccc','dddd','AAAA','BBBB','CCCC','åååå','øøøø','zzzz','ZZZZ','ØØØØ']) as t1 order by t1 collate nor;

result: BBBB bbbb CCCC cccc dddd ZZZZ zzzz ØØØØ øøøø AAAA aaaa åååå

what am I doing wrong here ?

Upvotes: 5

Views: 1015

Answers (1)

Adrián Fernández
Adrián Fernández

Reputation: 36

The order is correct. In Norwegian "aa" is the spelling of "å" and it should go at the end.

Source: https://en.wikipedia.org/wiki/%C3%85

Correct alphabetization in Danish and Norwegian places Å as the last letter in the alphabet, the sequence being Æ, Ø, Å. This is also true for the alternative spelling "Aa". Unless manually corrected, sorting algorithms of programs localised for Danish or Norwegian will place e.g., Aaron after Zorro.

Upvotes: 2

Related Questions