CODEWITHSUNDEEP
postgresqlsql-order-by
Bildsoe
Bildsoe

Reputation: 1340

Postgresql order by - danish characters is expanded

I'm trying to make a "order by" statement in a sql query work. But for some reason danish special characters is expanded in stead of their evaluating their value.

SELECT roadname FROM mytable ORDER BY roadname

The result:

The result in the middle should be the last.

The locale is set to danish, so it should know the value of the danish special characters.

Upvotes: 1

Views: 877

Answers (1)

araqnid
araqnid

Reputation: 133492

What is the collation of your database? (You might also want to give the PostgreSQL version you are using) Use "\l" from psql to see.

Compare and contrast:

steve@steve@[local] =# select * from (values('Abildlunden'),('Æblerosestien'),('Agern Alle 1')) x(word)
order by word collate "en_GB";
     word      
---------------
 Abildlunden
 Æblerosestien
 Agern Alle 1
(3 rows)

steve@steve@[local] =# select * from (values('Abildlunden'),('Æblerosestien'),('Agern Alle 1')) x(word)
order by word collate "da_DK";
     word      
---------------
 Abildlunden
 Agern Alle 1
 Æblerosestien
(3 rows)

The database collation is set when you create the database cluster, from the locale you have set at the time. If you installed PostgreSQL through a package manager (e.g. apt-get) then it is likely taken from the system-default locale.

You can override the collation used in a particular column, or even in a particular expression (as done in the examples above). However if you're not specifying anything (likely) then the database default will be used (which itself is inherited from the template database when the database is created, and the template database collation is fixed when the cluster is created)

If you want to use da_DK as your default collation throughout, and it's not currently your database default, your simplest option might be to dump the database, then drop and re-create the cluster, specifying the collation to initdb (or pg_createcluster or whatever tool you use to create the server)

BTW the question isn't well-phrased. PostgreSQL is very much not ignoring the "special" characters, it is correctly expanding "Æ" into "AE"- which is a correct rule for English. Collating "Æ" at the end is actually more like the unlocalised behaviour.

Collation documentation: http://www.postgresql.org/docs/current/static/collation.html

Upvotes: 6

Related Questions