The following bug has been logged online:

Bug reference: 1972
Logged by: Eduardo Soares
Email address: edurbs (AT) gmail (DOT) com
PostgreSQL version: 8.0.3
Operating system: Linux Fedora 4
Description: index error with space character
Details:

In above example the "AZTES Z" should be after the "AZTESA". It happens with
any encoding type. The DB not see the space character. The index shoulb see
the space and put "AZTES Z" together with "AZTES". In above list the
"AZTESA" should be the first.

Thanks for the help.

table=# insert into edu values ('AZTES Z');
INSERT 133634 1
table=# insert into edu values ('AZTESA');
INSERT 133635 1
table=# SELECT * FROM EDU ORDER BY NOME DESC;
nome
---------------
AZTES Z
AZTESA
AZTES
ÃNTES
ANTES
(8 registros)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Eduardo Soares wrote:
Sorting order is determined by your locale, and is different from your
encoding. For example, en_GB ignores spaces but C doesn't:

$ LC_COLLATE=en_GB.UTF-8 sort unsorted.txt
aa a
aaaa
aaab
aa b

$ LC_COLLATE=C sort unsorted.txt
aa a
aa b
aaaa
aaab

See "man locale" for details on how to find out what locales are setup
on your machine. See the documentation for details on how to set locale
on a database cluster.

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

---------- Forwarded message ----------
From: Eduardo RBS <edurbs (AT) gmail (DOT) com>
Date: 18/10/2005 13:31
Subject: Re: [BUGS] BUG #1972: index error with space character
To: Richard Huxton <dev (AT) archonet (DOT) com>


Hellow..
Thank you very much for the attention.
I need a locate that not ignores the space chracater and also sort
accents like =E1 or =E3.
I made configuration using locale... see it..

Using C is almost good because it sort correctly and see the spaces..
but it does not sort the portuguese accents.. note the last two
lines..
$ LC_COLLATE=3DC sort b.txt
aa a
aa z
aaaa
aaaz
aaz
eado
eza
=E9dina
=E9master

and with the "my" locale pt_BR it sort correctly the accents but
ignores the space chracater....
LC_COLLATE=3Dpt_BR.utf8 sort b.txt
aa a
aaaa
aaab
aaaz
aaz
aa z
eado
=E9dina
=E9master
eza

What i need should a merge of C and pt_BR.. i mean.. a locale that see
the spaces like C but sort accents like pt_BR..
I tried several others locales.. and only C see the space character.

Thanks for the attention.

--
[]'s
Eduardo RBS
http://linuxstok.sourceforge.net


2005/10/18, Richard Huxton <dev (AT) archonet (DOT) com>:

--
[]'s
Eduardo RBS
http://linuxstok.sourceforge.net

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Eduardo RBS <edurbs (AT) gmail (DOT) com> writes:
I'm afraid you'll have to learn how to build your own locale definition.
AFAIK, the "C" locale is the *only* common locale in which spaces aren't
second-class citizens.

I know that it is possible to write your own locale definition, but
this is not the place to ask about how. Try a glibc support forum.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster