Re: initdb in 8.3

Tim Tassonis wrote:
> Hi
> 
> I just recently compiled and installed 8.3.1 on a System that has UTF-8 
> as the default characterset in the environment. Copied the binaries, run 
> initdb without parameters, the usual stuff.
> 
> As you probably are all aware of, this results now in a cluster that 
> will only allow you to create UTF-8 databases. I have read some posts 
> regarding this topic where it is explained that allowing LATIN1 on a 
> cluster initialized with UTF-8 will give you problems anyway etc and you 
> have to use locale=C to be able to create databases with different 
> charactersets.

You can only have one locale per installation (initdb'd cluster).
You can create a database with any encoding that is compatible with that 
locale (LATIN1, LATIN9, UTF-8, etc).

Now, the locale controls (amongst other things) all your sorting. If you 
choose a locale of "C" you get a simple binary sorting. By default PG 
tries to match whatever sorting you have set up on your operating-system.

See the difference below:

richardh@server3:db$ LANG=C sort /tmp/words.txt
  apple
  berry
Apple
apple
apples
the apple

richardh@server3:db$ LANG=en_GB.UTF-8 sort /tmp/words.txt
apple
  apple
Apple
apples
  berry
the apple

I think someone is looking at per-database locales for 8.4 - the issue 
is more tricky than you might think because you need to worry about 
system catalogue sort-order.

-- 
   Richard Huxton
   Archonet Ltd