Thread: Issue on Varchar Ordering

Issue on Varchar Ordering

From
Erika Terahata Torres Prada e Silva - MPS
Date:

I´m with the same problem at order by  at version 8.0.3 and O.S. Debian.

I´ve tried with locale C, UTF8, encoding LATIN1, UNICODE  and it did't work.

 

 


Pg-Hackers,

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.
The encoding (LATIN1) is correct according to Brazilian Portuguese, and the settings are listed below.

 

Re: Issue on Varchar Ordering

From
Peter Eisentraut
Date:
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.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/