Re: ORDER BY collation order

Hi Scott,

Scott Marlowe wrote:
> no, not encoding, locale, such as en_US or C determine sort order.
>

OK, so I guess you're saying that whatever was in the LC_COLLATE
environment variable at the time the template0 database was created
determines the collation/sort order? Is that stored and visible somewhere?

> You can use varchar_pattern_ops and ~*~ operator.
>
> Search for those in the docs.
>

What I found
(http://www.postgresql.org/docs/8.2/static/indexes-opclass.html), talks
about creating an index with varchar_pattern_ops but that presumably
won't affect an ORDER BY result. I'm not quite sure where to find the
"~*~" operator, although I did find similar ones in 9.7 Pattern
Matching. In any case, I'm not sure how an operator helps in changing
an ORDER BY result from

"quoted"
123
Abc

to

123
Abc
"quoted"

It's even trickier than this simple example, because on Debian which is
using the en_US locale, the double quotes are disregarded for ordering
purposes, e.g.,

Medical
"Meet"
Message

Joe