Re: Locale, encoding, sort order confusion

John Gunther wrote:
> >Pretty much any locale (say, en_US for you) with a matching character
> >set should work. Unless you go out of your way, this should be the
> >default setting.
>
> >>I've tried a half dozen time-consuming configs without
> >>success.
>
> >Like what?
>
> For example, with all LC_* parameters set to "en_US.UTF8", I get the
> following incorrect "order:by":
>
> Béarn
> Bécancour
> Beaupré

Did you initdb with locale en_US.UTF8, and also createdb with encoding
UTF8? While you can certainly choose mismatching values in createdb and
initdb, you shouldn't because it doesn't work. See the docs here:

http://www.postgresql.org/docs/8.1/static/multibyte.html

Important: Although you can specify any encoding you want for a
database, it is unwise to choose an encoding that is not what is
expected by the locale you have selected. The LC_COLLATE and
LC_CTYPE settings imply a particular encoding, and locale-dependent
operations (such as sorting) are likely to misinterpret data that is
in an incompatible encoding.

Since these locale settings are frozen by initdb, the apparent
flexibility to use different encodings in different databases of a
cluster is more theoretical than real. It is likely that these
mechanisms will be revisited in future versions of PostgreSQL.

> Incidentally, in psql via a putty.exe session, the only character set
> translation I can find that displays the accented characters is CP437. Does
> this seem right?

You should probably set client_encoding in the psql session (using
\encoding) if you want to change the charset in putty.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support