X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 1E1C09FA159 for ; Thu, 17 Aug 2006 11:23:38 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 27047-07 for ; Thu, 17 Aug 2006 11:23:23 -0300 (ADT) X-Greylist: delayed 00:25:38.50845 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 6EE8C9FB335 for ; Thu, 17 Aug 2006 11:23:12 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from mail730.megamailservers.com (mail730.megamailservers.com [69.49.98.40]) by svr4.postgresql.org (Postfix) with ESMTP id 75B0B5AF8B3 for ; Thu, 17 Aug 2006 13:57:15 +0000 (GMT) X-Authenticated-User: paul.a.pnlassociates.com Received: from [69.3.2.106] (h-69-3-2-106.mclnva23.covad.net [69.3.2.106]) (authenticated bits=0) by mail730.megamailservers.com (8.13.6/8.13.1) with ESMTP id k7HDuu7p030898 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Thu, 17 Aug 2006 09:57:02 -0400 Message-ID: <44E475A7.3040401@pnlassociates.com> Date: Thu, 17 Aug 2006 09:56:55 -0400 From: "Paul B. Anderson" User-Agent: Thunderbird 1.5.0.5 (Macintosh/20060719) MIME-Version: 1.0 To: Erika Terahata Torres Prada e Silva - MPS Cc: pgsql-admin@postgresql.org Subject: Re: RES: Issue on Varchar Ordering References: <5519B95F9CA8B94DA8DE3F0A3337567201781A5F@wtdfl055.prevnet> In-Reply-To: <5519B95F9CA8B94DA8DE3F0A3337567201781A5F@wtdfl055.prevnet> Content-Type: multipart/alternative; boundary="------------040900030305060603030500" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.589 tagged_above=0 required=5 tests=HTML_30_40, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200608/202 X-Sequence-Number: 22745 This is a multi-part message in MIME format. --------------040900030305060603030500 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit I just experienced this. I changed the collating sequence to POSIX and sorting worked as I had expected. To change the collating sequence you will have to backup the database, issue initdb with the --lc-collate=POSIX and then restore the data. In my case, the encoding is still UTF8. Paul Erika Terahata Torres Prada e Silva - MPS wrote: > Peter, > > Thanks for answering. > > But it's not correct to my language pt_BR and it worked correctly with > PostgreSQL at Windows and FreeBSD. I have this problem only with Debian and > now I'm looking for some Debian related issue. > > > > -----Mensagem original----- > De: Peter Eisentraut [mailto:peter_e@gmx.net] > Enviada em: quinta-feira, 17 de agosto de 2006 10:04 > Para: pgsql-admin@postgresql.org > Cc: Erika Terahata Torres Prada e Silva - MPS > Assunto: Re: [ADMIN] Issue on Varchar Ordering > > Erika Terahata Torres Prada e Silva - MPS wrote: > >> I'm having the following issue: >> >> create table aluno (id serial, nome varchar(60)); >> >> rodrigo=# select * from aluno order by nome offset 35 limit 5; >> id | nome >> ----+----------------------------------- >> 36 | GABRIELA HELEDA DE SOUZA >> 37 | GABRIELA JACOBY NOS >> 38 | GABRIEL ALCIDES KLIM PERONDI >> 39 | GABRIELA LETICIA BATISTA NUNES >> 40 | GABRIEL ALEXANDRE DA SILVA MANICA >> (5 registros) >> >> The problem is that records with id 38 and 40 might come before the >> others. It seems that the spacing isn't considered on ordering. >> > > That is, for practical purposes, correct. > > May I suggest that you properly normalize your tables, by putting given > name and family name into separate columns. > > --------------040900030305060603030500 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit I just experienced this.  I changed the collating sequence to POSIX and sorting worked as I had expected.

To change the collating sequence you will have to backup the database, issue initdb with the --lc-collate=POSIX and then restore the data.  In my case, the encoding is still UTF8.

Paul


Erika Terahata Torres Prada e Silva - MPS wrote:
Peter, 

Thanks for answering.

But it's not correct to my language pt_BR and it worked correctly with
PostgreSQL at Windows and FreeBSD. I have this problem only with Debian and
now I'm looking for some Debian related issue.



-----Mensagem original-----
De: Peter Eisentraut [mailto:peter_e@gmx.net] 
Enviada em: quinta-feira, 17 de agosto de 2006 10:04
Para: pgsql-admin@postgresql.org
Cc: Erika Terahata Torres Prada e Silva - MPS
Assunto: Re: [ADMIN] Issue on Varchar Ordering

Erika Terahata Torres Prada e Silva - MPS wrote:
  
I'm having the following issue:

create table aluno (id serial, nome varchar(60));

rodrigo=# select * from aluno order by nome offset 35 limit 5;
 id |               nome
----+-----------------------------------
 36 | GABRIELA HELEDA DE SOUZA
 37 | GABRIELA JACOBY NOS
 38 | GABRIEL ALCIDES KLIM PERONDI
 39 | GABRIELA LETICIA BATISTA NUNES
 40 | GABRIEL ALEXANDRE DA SILVA MANICA
(5 registros)

The problem is that records with id 38 and 40 might come before the
others. It seems that the spacing isn't considered on ordering.
    

That is, for practical purposes, correct.

May I suggest that you properly normalize your tables, by putting given 
name and family name into separate columns.

  
--------------040900030305060603030500--