Re: ORDER BY collation order

On Thu, Sep 18, 2008 at 10:35 PM, Joe <dev@freedomcircle.net> wrote:
> 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?

It's set at the time of init.d and can't be changed without a
dump/initdb/restore cycle.

You can see by typing
show lc_collate ;lc_collate
------------en_US

in psql.

>> 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"

Sorry, I mentioned the wrong operator before, it's ~>~ and ~<~ (asc
versus desc):

smarlowe=# create table col_test (a text);
CREATE TABLE
smarlowe=# insert into col_test (a) values ('"quoted"'),('Abc'),('123');
INSERT 0 3
smarlowe=# select * from col_test order by a;   a
----------123Abc"quoted"
(3 rows)

smarlowe=# select * from col_test order by a using ~<~;   a
----------"quoted"123Abc
(3 rows)

smarlowe=# select * from col_test order by a using ~>~;   a
----------Abc123"quoted"
(3 rows)