Re: ORDER BY collation order

On Thu, Sep 18, 2008 at 10:35 PM, Joe <dev(at)freedomcircle(dot)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
----------
123
Abc
"quoted"
(3 rows)

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

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