From: Samuel Gendler <sgendler@ideasculptor.com>
To: Eyal Wilde <eyal@impactsoft.co.il>
Cc: pgsql-sql@postgresql.org
Subject: Re: a strange order by behavior
Date: Wed, 22 Jun 2011 01:43:20 -0700
Message-ID: <BANLkTi=PXV+FrAJHHX56D41Xo4DzG7vADA@mail.gmail.com> (raw)
In-Reply-To: <BANLkTi=ScMyF7nHQ6nhbyYr9UDrq16U=9g@mail.gmail.com>
References: <BANLkTi=ScMyF7nHQ6nhbyYr9UDrq16U=9g@mail.gmail.com>
On Tue, Jun 21, 2011 at 3:37 AM, Eyal Wilde <eyal@impactsoft.co.il> wrote:
> the database collation is: en_US.UTF-8
>
> drop table t1;
> create table t1 (recid int ,f1 varchar(20));
> insert into t1 values (1,'a');
> insert into t1 values (2,' ');
> insert into t1 values (3,'aa');
> insert into t1 values (4,' a');
> select * from t1 order by f1
>
> result:
>
> recid f1
> 2 " "
> 1 "a" -- 2 comes before 1 because space is smaller then 'a'.
> fine.
> 4 " a" -- now is see that 1 comes before 4 because space is
> greater then 'a' !?
> 3 "aa" -- now again, 4 comes before 3 because space is smaller
> the 'a' !?!
>
>
I seem to recall a thread here about it ignoring spaces entirely in that
collation (and maybe ignoring capitalization, too?). I went to go test that
assertion by initializing a database with C collation and got some complaint
about it being incompatible with my template1 template database. I
initialized a db off of template0 and then got side tracked and you've only
just reminded me of it. I was planning to test whether it is safe to use
UTF-8 for encoding but use C collation, and then maybe investigate other
collations.
This worked:
createdb -E UTF-8 --lc-collate=C some_db
so it should be easy enough to play around with it some. I'm not sure how
to get a list of valid collations for any given charset, and it seems like C
collation would generate somewhat strange results with non-ascii characters
(french accented characters are supposed to alphabetize in some unexpected
manner, I believe), so there must be something better - closer to UTF-8
collation but without ignoring whitespace and such. A quick google search
reveals that there is some kind of standard for unicode collation (
http://www.unicode.org/reports/tr10/ ) and I have no idea if that is what is
represented by the en_US.UTF-8 collation or not. I've got no real
experience with this stuff.
It appears that there are differences regarding collation in recent versions
- the beta docs for 9.1 show that you can set collation on individual
operations or differently for different columns (
http://www.postgresql.org/docs/9.1/static/collation.html ). I see nothing
like that in 8.4 docs.
It definitely looks like we both need to have a read of the localization
chapter of the docs for our database version as there is a bunch of stuff in
there that I was surprised to read when I just did a quick scan - like using
anything but C or posix is much slower and can produce incorrect results in
a 'like' query
It looks like the docs prior to 9.1beta have no real reference to collation
at all, so it's down to trial and error unless someone in the know speaks
up.
--sam
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sgendler@ideasculptor.com, eyal@impactsoft.co.il
Subject: Re: a strange order by behavior
In-Reply-To: <BANLkTi=PXV+FrAJHHX56D41Xo4DzG7vADA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox