On Wed, Jun 22, 2011 at 8:57 PM, Pavel Stehule
<pavel.stehule@gmail.com> wrote:
2011/6/22 Peter Eisentraut <peter_e@gmx.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.
>
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)