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