Thread: sorting order
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?
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
Thank you Oliver, I deleted everything from /var/lib/pgsql/data (that is where my data files are kept by default) and then su postgres, export PGDATA=/var/lib/pgsql/data and then I ran initdb, restarted postgres, exit from postgres, changed to a normal user and created and populated my new database. The sort-order works fine. Do you by the way know about any gui-client for X (preferably KDE) except pgAccess which doesn't show our special-characters correct. Oliver Elphick wrote: > 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. > >