Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 1AE20B5DBC1 for ; Thu, 23 Jun 2011 01:34:42 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 05811-01 for ; Thu, 23 Jun 2011 04:34:35 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pz0-f46.google.com (mail-pz0-f46.google.com [209.85.210.46]) by mail.postgresql.org (Postfix) with ESMTP id 8B2A6B5D91F for ; Thu, 23 Jun 2011 01:34:35 -0300 (ADT) Received: by pzk9 with SMTP id 9so944982pzk.19 for ; Wed, 22 Jun 2011 21:34:34 -0700 (PDT) MIME-Version: 1.0 Received: by 10.68.5.135 with SMTP id s7mr863131pbs.96.1308803674346; Wed, 22 Jun 2011 21:34:34 -0700 (PDT) Received: by 10.68.55.167 with HTTP; Wed, 22 Jun 2011 21:34:34 -0700 (PDT) X-Originating-IP: [66.215.95.51] In-Reply-To: References:

<1308773527.10498.7.camel@vanquo.pezone.net> Date: Wed, 22 Jun 2011 21:34:34 -0700 Message-ID: Subject: Re: a strange order by behavior From: Samuel Gendler To: Pavel Stehule Cc: Peter Eisentraut , Eyal Wilde , pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=bcaec517a7c217132204a6599ae1 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201106/87 X-Sequence-Number: 35553 --bcaec517a7c217132204a6599ae1 Content-Type: text/plain; charset=ISO-8859-1 On Wed, Jun 22, 2011 at 8:57 PM, Pavel Stehule wrote: > 2011/6/22 Peter Eisentraut : > > 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) --bcaec517a7c217132204a6599ae1 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Wed, Jun 22, 2011 at 8:57 PM, Pavel S= tehule <pav= el.stehule@gmail.com> wrote:

2011/6/22 Peter Eisentraut <peter_e@g= mx.net>:

> On ons, 2011-06-22 at 02:39 -0700, S= amuel 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=3Ducs_basic -E UTF-8 test
>> createdb: database creation failed: ERROR: =A0invalid locale name = ucs_basic
>
> ucs_basic is a collation name, which is an SQL object. =A0The argument= of
> createdb --lc-collate is an operating system locale name. =A0You can&#= 39;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. =A0If you= want your queries to use an index when ordering or comparing strings via L= IKE, then you need to create a separate index that uses varchar_pattern_ops= instead of the default varchar_ops. =A0The db will automatically use the v= archar_pattern_ops index when appropriate. =A0varchar_pattern_ops uses C-li= ke collation.

create index t1_f1_pattern on t1 (f1 varchar_pattern_op= s)

=A0

--bcaec517a7c217132204a6599ae1--