Re: a strange order by behavior

On Wed, Jun 22, 2011 at 8:57 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:

> 2011/6/22 Peter Eisentraut <peter_e(at)gmx(dot)net>:
> > On ons, 2011-06-22 at 02:39 -0700, Samuel Gendler wrote:
> >> Pavel suggested using a collation of ucs_basic, but I get an error
> >> when I
> >> try that on linux:
> >>
> >> $ createdb -U u1 --lc-collate=ucs_basic -E UTF-8 test
> >> createdb: database creation failed: ERROR: invalid locale name
> ucs_basic
> >
> > ucs_basic is a collation name, which is an SQL object. The argument of
> > createdb --lc-collate is an operating system locale name. You can't mix
> > the two, even though they are similar.
> >
>
> ok, what I can to select, when I would to use a C like default order?
>
>
You can specify 'order by f1 using ~<~' or 'order by f1 using ~>~' in your
query. If you want your queries to use an index when ordering or comparing
strings via LIKE, then you need to create a separate index that uses
varchar_pattern_ops instead of the default varchar_ops. The db will
automatically use the varchar_pattern_ops index when appropriate.
varchar_pattern_ops uses C-like collation.

create index t1_f1_pattern on t1 (f1 varchar_pattern_ops)