Re: sorting order

On Fri, 2002-03-01 at 10:10, Bertil Wergelius wrote:
> I have worked with a lot of different DBMSs. Ranging from MSSQL over Sybase
> SQL-anywhere to Interbase and most recently MySQL. With only the last mentioned
> has there been no problem that my language is Swedish and that I therefore want
> to use our own language's characters in correct sort order. MySQL gives a very
> pleasant experience when it comes to character sets, collation orders and all
> the rest; you don't have to care about it at all. That is perhaps because it is
> a swedish product (of Sweden and Finland).
> When I now start trying postgresql all the troubles with character sets come
> back. I've changed LANG to sv_SE and LC_COLLATE too, (in sysconfig/i18n and
> restarted) created the database with encoding LATIN1, but nothing helps.
> I can sort an ordinary textfile with cat textfile | sort. That sorts correctly
> so the collation must be correct, then why can't postgresql sort it correctly?

The locale must be set at the time you do initdb.  This is because
indexes will be corrupted if the locale changes after they have had
items added.

In contrib, there is a utility pg_controldata which will show you what
locale the database is using.  If it the wrong one, you  must dump your
database, destroy it and do initdb again under the correct locale.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "The LORD is my shepherd; I shall not want. He maketh
      me to lie down in green pastures: he leadeth me beside
      the still waters, he restoreth my soul...Surely
      goodness and mercy shall follow me all the days of my
      life; and I will dwell in the house of the LORD for
      ever."                    Psalms 23:1,2,6