On Thu, Sep 18, 2008 at 10:35 PM, Joe <[EMAIL PROTECTED]> 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) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
