Skip site navigation (1) Skip section navigation (2)

Re: ORDER BY collation order

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 (view raw or flat)
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)

In response to

Responses

pgsql-sql by date

Next:From: JoeDate: 2008-09-21 14:58:47
Subject: Re: ORDER BY collation order
Previous:From: Petr JelinekDate: 2008-09-19 13:59:12
Subject: Re: ORDER BY collation order

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group