Re: help understanding collation order

Tom Lane wrote:

> raf <raf(at)raf(dot)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

thanks. "C" will have to do, i suppose.
that and/or re-sort in the client.

cheers,
raf

p.s. if anyone in debian locale land is listening,
'E' does not sort before ','. what were you thinking? :-)