> Thanks. How do I set the locale?
Short answer:
bash$ LANG=C initdb
Longer answer:
I find locale support in PostgreSQL confusing. The locale of the current user at the time of initialising the database
determinesthe collating order (e.g. is 'a' < 'A'). This in turns affect things like indexing (as described below).
Thelocale of the account that is running the current postmaster defines format conversions (e.g. the currency symbol
into_char(1,'L99')). The locale of the current process requesting a query defines how the user expects to see the
result,and is (of course) ignored by PostgreSQL.
To make matters worse: On Red Hat 7.1 (US boxed set) the default locale is set in /etc/sysconf/i18n to en_US, i.e.
AmericanEnglish. Fine, except some Americans may be surprised to learn that 'A' > 'a'. When you start the postgresql
servicewithout doing an initdb first, the script will helpfully copy i18n to ~postgres/initdb.i18n so you have a
recordof the locale (and can re-set it). Except it never uses this file. In fact, the postmaster service runs in the
defaultlocale *for root*. A previous version of RH did go through the trouble of sourcing initdb.i18n in
.bash_profile,but it does nothing for postmaster which is started as su -l postgres -s /bin/sh ...
In order to start postmaster on RH7.1 with a locale that is different from the default (actually: different from root)
youshould create ~postgres/.profile with the content
PGLIB=/usr/lib/pgsql
PGDATA=/var/lib/pgsql/data
export PGLIB PGDATA
[ -f $PGDATA/../initdb.i18n ] && source $PGDATA/../initdb.i18n
You may, of course, have to change the path. You may want to add that last line to your ~postgres/.bash_profile as
well,in order to avoid future confusion....
If you dont have initdb.i18n, just delete the content of the data directory and start the postgres service. The result
shouldlook something like
LANG="en_US"
export LANG LC_ALL LC_CTYPE LC_COLLATE LC_NUMERIC LC_CTYPE LC_TIME
for a default installation, but of course you want LANG="C"
Hope this makes sense to somebody....e-mail if I've managed to confuse anybody, including myself :-)
Allan.
