Thread: BUG #1972: index error with space character
The following bug has been logged online:
Bug reference: 1972
Logged by: Eduardo Soares
Email address: edurbs@gmail.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)
Eduardo Soares wrote: > 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. 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
---------- Forwarded message ---------- From: Eduardo RBS <edurbs@gmail.com> Date: 18/10/2005 13:31 Subject: Re: [BUGS] BUG #1972: index error with space character To: Richard Huxton <dev@archonet.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@archonet.com>: > Eduardo Soares wrote: > > 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. > > 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=3Den_GB.UTF-8 sort unsorted.txt > aa a > aaaa > aaab > aa b > > $ LC_COLLATE=3DC 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 > -- []'s Eduardo RBS http://linuxstok.sourceforge.net
Eduardo RBS <edurbs@gmail.com> writes:
> I need a locate that not ignores the space chracater and also sort
> accents like á or ã.
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