Thread: sorting order

sorting order

From
Bertil Wergelius
Date:
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?




Re: sorting order

From
Oliver Elphick
Date:
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


Re: sorting order

From
Bertil Wergelius
Date:
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.
>
>