DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:from:date:message-id:subject:to
	:cc:content-type:content-transfer-encoding;
	b=bQaKrHbMF86+JuJvLR96UHrktC06J5gYLy9rCw9chnGmXD/yVL8v2x5XKbAkKTNODu
	gMCaFJivhZjKvNoLPZYyoZfaycknWV8TEAdXBIDZ59S7PQoToOXXBBdmiULlBMFMcmDJ
	X/1cxphIOo8hBvUadKfaN2SeD8flMsQtNpWTk=
MIME-Version: 1.0
In-Reply-To: <BANLkTi=PXV+FrAJHHX56D41Xo4DzG7vADA@mail.gmail.com>
References: <BANLkTi=ScMyF7nHQ6nhbyYr9UDrq16U=9g@mail.gmail.com>
	<BANLkTi=PXV+FrAJHHX56D41Xo4DzG7vADA@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Wed, 22 Jun 2011 10:45:44 +0200
Message-ID: <BANLkTim9sFLt3e2+auF_oTr68UETiW83VA@mail.gmail.com>
Subject: Re: a strange order by behavior
To: Samuel Gendler <sgendler@ideasculptor.com>
Cc: Eyal Wilde <eyal@impactsoft.co.il>, pgsql-sql@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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 =C2=A0f1
>> 2 =C2=A0 =C2=A0 =C2=A0" "
>> 1 =C2=A0 =C2=A0 =C2=A0"a" =C2=A0 =C2=A0 =C2=A0 =C2=A0-- 2 comes before 1=
 because space is smaller then 'a'.
>> fine.
>> 4 =C2=A0 =C2=A0 =C2=A0" a" =C2=A0 =C2=A0 =C2=A0 -- now is see that 1 com=
es before 4 because space is
>> greater then 'a' !?
>> 3 =C2=A0 =C2=A0 =C2=A0"aa" =C2=A0 =C2=A0 =C2=A0 -- now again, 4 comes be=
fore 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?). =C2=A0I went to go t=
est that
> assertion by initializing a database with C collation and got some compla=
int
> about it being incompatible with my template1 template database. =C2=A0I
> initialized a db off of template0 and then got side tracked and you've on=
ly
> just reminded me of it. =C2=A0I was planning to test whether it is safe t=
o use
> UTF-8 for encoding but use C collation, and then maybe investigate other
> collations.
> This worked:
> createdb =C2=A0-E UTF-8 --lc-collate=3DC some_db
> so it should be easy enough to play around with it some. =C2=A0I'm not su=
re how
> to get a list of valid collations for any given charset, and it seems lik=
e C
> collation would generate somewhat strange results with non-ascii characte=
rs
> (french accented characters are supposed to alphabetize in some unexpecte=
d
> manner, I believe), so there must be something better - closer to UTF-8
> collation but without ignoring whitespace and such. =C2=A0A 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 wha=
t
> is represented by the en_US.UTF-8 collation or not. =C2=A0I've got no rea=
l
> experience with this stuff.
> It appears that there are differences regarding collation in recent versi=
ons
> - 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 ). =C2=A0I 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 us=
ing
> 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 collati=
on
> at all, so it's down to trial and error unless someone in the know speaks
> up.
> --sam
>
>