From: | "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Joe <dev(at)freedomcircle(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: ORDER BY collation order |
Date: | 2008-09-19 14:26:00 |
Message-ID: | dcc563d10809190726g4852834fi6f5ecf741fae4509@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Joe | 2008-09-21 14:58:47 | Re: ORDER BY collation order |
Previous Message | Petr Jelinek | 2008-09-19 13:59:12 | Re: ORDER BY collation order |