Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 506E4B5DBDC for ; Wed, 22 Jun 2011 08:20:58 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 11633-07 for ; Wed, 22 Jun 2011 11:20:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pv0-f174.google.com (mail-pv0-f174.google.com [74.125.83.174]) by mail.postgresql.org (Postfix) with ESMTP id 8236BB5DBFE for ; Wed, 22 Jun 2011 08:20:51 -0300 (ADT) Received: by pvg12 with SMTP id 12so436427pvg.19 for ; Wed, 22 Jun 2011 04:20:51 -0700 (PDT) MIME-Version: 1.0 Received: by 10.68.41.163 with SMTP id g3mr202905pbl.431.1308741650878; Wed, 22 Jun 2011 04:20:50 -0700 (PDT) Received: by 10.68.55.167 with HTTP; Wed, 22 Jun 2011 04:20:50 -0700 (PDT) X-Originating-IP: [66.215.95.51] In-Reply-To: References: Date: Wed, 22 Jun 2011 04:20:50 -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=bcaec52e58e734129904a64b29da 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/81 X-Sequence-Number: 35547 --bcaec52e58e734129904a64b29da Content-Type: text/plain; charset=ISO-8859-1 On Wed, Jun 22, 2011 at 3:39 AM, Samuel Gendler wrote: > > > On Wed, Jun 22, 2011 at 3:01 AM, Pavel Stehule wrote: > >> Hello Peter >> >> >> > 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 >> >> isn't this a bug in collations? >> > > The more I read about this, the more this would appear to be the case. It > looks like the SQL standard has some baseline collations that are required > and it isn't at all clear how one would access those in postgres if the host > in question doesn't have those locale's defined on the host. UCS_BASIC is a > SQL collation, but doesn't appear to have an explicit definition on a > 'standard' linux host (CentOS 5, in my case). There is another SQL > collation called 'UNICODE' which is supposed to obey the Unicode Collation > Algorithm with the Default Unicode Collation Element Table defined in > Unicode10. It looks like that collation is relatively sensitive to > language-specific sort orders, though it isn't a required collation in the > sql standard. I suspect that it is the UNICODE collation which actually > would appear to be the most 'sensible' within the context of this discussion > - characters in expected order, spaces honoured, case sensitive. I have so > little experience with localization that I'm not sure if I'm reading this > all correctly, though. > > > My final bit of research before going to bed - If you are stuck with UTF-8 collation, you can do C-style collation in order by clauses like this: select * from t1 order by f1 using ~<~ select * from t1 order by f1 using ~>~ If you want it to be able to use an index for that (or you want to use LIKE in your where clause with that column), then you need to have an index which uses C-style collation. You can create an index on the column using text_pattern_ops or varchar_pattern_ops and then LIKE, <, and > comparisons will use that index and do comparisons like C collation, as will order by clauses with ~>~ and ~<~ create index t1_f1_pattern ON t1 (f1 varchar_pattern_ops); create index t1_f1 ON t1 (f1); If you have both of those indexes, postgresql will, apparently, do the correct thing based on which operations you ask of it in your query. The pattern_ops thing is mentioned in the docs (but on a page about indexes rather than on a page about varchar and text types) and I found the ~>~ operator in an ORDER BY clause in an old mailing list email. It may be in the docs, I'm not sure. Is there a documentation page or wiki page consisting of 'stuff that may surprise you in postgresql?' Such a page would be a handy place to point this kind of thing out to new users. Most users, even relatively experienced ones, probably haven't read the documentation cover to cover. I certainly hadn't. 9.1 allows collation to be specified on a per-column basis, but since all of the utf-8 collations ignore punctuation and space characters, you'll still have to do C-style collation to get alphabetical order that honors whitespace, punctuation, and case, but then non-ascii characters will be in the wrong order. So you can either have language sensitive sorting or punctuation- and case- sensitive sorting, but never both - at least not without defining a custom locale. Punting this defect to the OS and saying it has to be fixed there seems like a somewhat weak answer - at least without offering up a source for alternative locale definitions that do sorting according to UNICODE collation. I'm actually surprised that european users aren't complaining about this all the time, but maybe european users are used to seeing things ordered in a manner which doesn't honour the 'correct' ordering of accented characters. Actually, I wonder if the probable explanation of the lack of complaint is the fact that the kinds of fields most apps would tend to do alphabetical sorts on probably don't tend to have lots of punctuation other than spaces, so perhaps the language sensitive sorts are deemed sufficient because most people don't notice the funky behaviour with punctuation and whitespace while case-insensitive sort is probably desired most of the time. --bcaec52e58e734129904a64b29da Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

On Wed, Jun 22, 2011 at 3:39 AM, Samuel = Gendler <= sgendler@ideasculptor.com> wrote:


On Wed, Jun 22, 2011 a= t 3:01 AM, Pavel Stehule <pavel.stehule@gmail.com> wro= te:
Hello Peter


> Pavel suggested using a collation of ucs_basic, but I get an error whe= n 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

isn't this a bug in collations?

The more I read about this, the more this would appear to be the c= ase. It looks like the SQL standard has some baseline collations that are r= equired and it isn't at all clear how one would access those in postgre= s if the host in question doesn't have those locale's defined on th= e host. UCS_BASIC is a SQL collation, but doesn't appear to have an exp= licit definition on a 'standard' linux host (CentOS 5, in my case).= =A0There is another SQL collation called 'UNICODE' which is suppos= ed to obey the Unicode Collation Algorithm with the Default Unicode Collati= on Element Table defined in Unicode10. =A0It looks like that collation is r= elatively sensitive to language-specific sort orders, though it isn't a= required collation in the sql standard. =A0I suspect that it is the UNICOD= E collation which actually would appear to be the most 'sensible' w= ithin the context of this discussion - characters in expected order, spaces= honoured, case sensitive. I have so little experience with localization th= at I'm not sure if I'm reading this all correctly, though.

=A0

My final bit of research before going to bed -= =A0

If you are stuck with UTF-8 collation, you can= do C-style collation in order by clauses like this:

select * from t1 order by f1 using ~<~
select * from t1 o= rder by f1 using ~>~

If you want it to be able = to use an index for that (or you want to use LIKE in your where clause with= that column), then you need to have an index which uses C-style collation.= =A0You can create an index on the column using text_pattern_ops or varchar= _pattern_ops and then LIKE, <, and > comparisons will use that index = and do comparisons like C collation, as will order by clauses with ~>~ a= nd ~<~

create index t1_f1_pattern ON t1 (f1 varchar_pattern_op= s);
create index t1_f1 ON t1 (f1);

If yo= u have both of those indexes, postgresql will, apparently, do the correct t= hing based on which operations you ask of it in your query. =A0The pattern_= ops thing is mentioned in the docs (but on a page about indexes rather than= on a page about varchar and text types) and I found the ~>~ operator in= an ORDER BY clause in an old mailing list email. =A0It may be in the docs,= I'm not sure. =A0Is there a documentation page or wiki page consisting= of 'stuff that may surprise you in postgresql?' =A0Such a page wou= ld be a handy place to point this kind of thing out to new users. =A0Most u= sers, even relatively experienced ones, probably haven't read the docum= entation cover to cover. =A0I certainly hadn't.

9.1 allows collation to be specified on a per-column ba= sis, but since all of the utf-8 collations ignore punctuation and space cha= racters, you'll still have to do C-style collation to get alphabetical = order that honors whitespace, punctuation, and case, but then non-ascii cha= racters will be in the wrong order. So you can either have language sensiti= ve sorting or punctuation- and case- sensitive sorting, but never both - at= least not without defining a custom locale. Punting this defect to the OS = and saying it has to be fixed there seems like a somewhat weak answer - at = least without offering up a source for alternative locale definitions that = do sorting according to UNICODE collation. =A0

I'm actually surprised that european users aren'= ;t complaining about this all the time, but maybe european users are used t= o seeing things ordered in a manner which doesn't honour the 'corre= ct' ordering of accented characters. =A0Actually, I wonder if the proba= ble explanation of the lack of complaint is the fact that the kinds of fiel= ds most apps would tend to do alphabetical sorts on probably don't tend= to have lots of punctuation other than spaces, so perhaps the language sen= sitive sorts are deemed sufficient because most people don't notice the= funky behaviour with punctuation and whitespace while case-insensitive sor= t is probably desired most of the time.
--bcaec52e58e734129904a64b29da--