From: Pavel Stehule <pavel.stehule@gmail.com>
To: Samuel Gendler <sgendler@ideasculptor.com>
Cc: Eyal Wilde <eyal@impactsoft.co.il>
Cc: pgsql-sql@postgresql.org
Subject: Re: a strange order by behavior
Date: Wed, 22 Jun 2011 10:45:44 +0200
Message-ID: <BANLkTim9sFLt3e2+auF_oTr68UETiW83VA@mail.gmail.com> (raw)
In-Reply-To: <BANLkTi=PXV+FrAJHHX56D41Xo4DzG7vADA@mail.gmail.com>
References: <BANLkTi=ScMyF7nHQ6nhbyYr9UDrq16U=9g@mail.gmail.com>
<BANLkTi=PXV+FrAJHHX56D41Xo4DzG7vADA@mail.gmail.com>
Hello
a equalent of C collate for UTF8 is ucs_basic
Regards
Pavel Stehule
2011/6/22 Samuel Gendler <sgendler@ideasculptor.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: pavel.stehule@gmail.com, sgendler@ideasculptor.com, eyal@impactsoft.co.il
Subject: Re: a strange order by behavior
In-Reply-To: <BANLkTim9sFLt3e2+auF_oTr68UETiW83VA@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