title: Postgresql sorting language specific characters (collation)
body: <p>I, as a speaker of language, which has several non-common characters like ÕÜÖÄ, think that if i create an app, which allows user to save content in estonian to database, then this app should also be able to sort data correctly according to language settings.</p>
  
  <p>Also, as a spear of very small language, like estonian is, have to consider, that other people might use the app and want sorting in their language. Several european languages also have their own mix of special characters. </p>
  
  <p>Its even worse, if you include languages like russian into the mix - they have completely different characters.</p>
  
  <p>Now since i know the django site can display all those special characters just fine, i'm only worried about sorting, which goes on database level. So how can i ensure, that objects are read from database and sorted into correct, language specific order.</p>
  
  <p>Example case:
  If i create database with utf8 encoding and with default collation(english utf8), then table containing values</p>
  
  <pre><code>alan
  oskar
  ölen
  älan
  öskar
  </code></pre>
  
  <p>Will be sorted (by "select * from test order by nimi asc;") to:</p>
  
  <pre><code>alan
  älan
  ölen
  oskar
  öskar
  </code></pre>
  
  <p>Which is incorrect. If i create database with POSIX collation, then same query results in:</p>
  
  <pre><code>alan
  oskar
  älan
  ölen
  öskar
  </code></pre>
  
  <p>This SEEMS to be correct, cause if you throw few more names (õlan, ålan) into the mix, then the results are:</p>
  
  <pre><code>alan
  oskar
  älan
  ålan
  õlan
  ölen
  öskar
  </code></pre>
  
  <p>Cause, according to estonian alphabet, they should be:</p>
  
  <pre><code>alan
  oskar
  õlan
  älan
  ölen
  öskar
  ålan (cause å is not in estonian alphabet)
  </code></pre>
  
  <p>I am using postgresql 9.1.9 and i understand that collation support is available since 9.1 (<a href="http://www.postgresql.org/docs/9.1/interactive/collation.html" rel="noreferrer">http://www.postgresql.org/docs/9.1/interactive/collation.html</a>). </p>
  
  <p>Is that what i should use?</p>
  
  <pre><code>SELECT nimi COLLATE "et_EE" FROM test ORDER BY nimi ASC;
  </code></pre>
  
  <p>Fails with error ERROR:  collation "et_EE.utf8" for encoding "UTF8" does not exist. Why is that? 
  Do i have to create this collation rule or something? 
  Is this the way to go - to specify collation rule with every query, if i want results in correct order?</p>
  
  <p>Alan</p>
  
  <p>Edit :1 to answer question:
  my locale -a shows:</p>
  
  <pre><code>C
  C.UTF-8
  en_AG
  en_AG.utf8
  en_AU.utf8
  en_BW.utf8
  en_CA.utf8
  en_DK.utf8
  en_GB.utf8
  en_HK.utf8
  en_IE.utf8
  en_IN
  en_IN.utf8
  en_NG
  en_NG.utf8
  en_NZ.utf8
  en_PH.utf8
  en_SG.utf8
  en_US.utf8
  en_ZA.utf8
  en_ZM
  en_ZM.utf8
  en_ZW.utf8
  et_EE
  et_EE.iso88591
  et_EE.utf8
  POSIX
  zh_CN.utf8
  zh_SG.utf8
  
  sudo locale-gen et_EE.UTF-8
  [sudo] password for alan: 
  Generating locales...
    et_EE.UTF-8... up-to-date
  Generation complete.
  sudo locale-gen et_EE
  Generating locales...
    et_EE.ISO-8859-1... up-to-date
  Generation complete.
  </code></pre>
  
  <p>SQL still fails though</p>
  
  <p>Edit2: I think i figured it out. I had to create the collation itself in postgresql too (<a href="http://www.postgresql.org/docs/9.1/static/sql-createcollation.html" rel="noreferrer">http://www.postgresql.org/docs/9.1/static/sql-createcollation.html</a>):</p>
  
  <pre><code>CREATE COLLATION "et_EE" (LOCALE = "et_EE.utf8")
  </code></pre>
  
  <p>So i guess i have to create those shell locale files, that you told me about, zero323, and then create collations to database and then i'm all set.</p>