Re: help understanding collation order

raf <raf@raf.org> writes:
> the behaviour i expect (and see on macosx-10.6.6) is:

>    id |     name
>   ----+---------------
>     4 | CLARK
>     2 | CLARK, PETER
>     3 | CLARKE
>     1 | CLARKE, DAVID

> the behaviour i don't expect but see anyway (on debian-5.0) is:

>    id |     name
>   ----+---------------
>     4 | CLARK
>     3 | CLARKE
>     1 | CLARKE, DAVID
>     2 | CLARK, PETER

> the "good" server has lc_messages='en_AU' and the
> "bad" server has lc_messages="en_AU.utf8" which may
> be relevant

No, not particularly.  Sort order is determined by lc_collate
not lc_messages.  Unfortunately it's entirely possible that OSX
will give you a different sort order than Linux even for similarly
named lc_collate settings.  About the only lc_collate setting that
really behaves the same everywhere, guaranteed, is "C" ... and that
might or might not do what you want.  (C locale does satisfy the
above example but it's hard to be sure what you want in general;
and if you are using any non-ASCII characters, C locale will more
than likely not be very satisfactory.)

            regards, tom lane