Re: Order by behaviour

On Wed, 28 Mar 2007, Carlos H. Reimer wrote:

> Hi,
>
> We have a PostgreSQL 8.0.6 cluster configured with lc_collate=pt_BR.UTF-8
> and when we run the following SELECT:
> SELECT substr(nomerazao,1,4),
> ascii(substr(nomerazao,1,1)),
> ascii(substr(nomerazao,2,1))
> from spunico.unico order by nomerazao;
>
> is returning:
>
> substr | ascii | ascii
> --------+-------+-------
> | 32 | 0
> | 32 | 0
> 1000 | 49 | 48
> 1.DI | 49 | 46
> 1 R | 49 | 176
> 2M C | 50 | 77
> 3A.G | 51 | 65
> A. A | 65 | 46
> AABA | 65 | 65
> A.A. | 65 | 46
> A.AG | 65 | 46
> A.A. | 65 | 46
> A.A. | 65 | 46
> ABAS | 65 | 66
> ABAS | 65 | 66
> ABAT | 65 | 66
> A.B. | 65 | 46
> A.B. | 65 | 46
> ABCC | 65 | 66
> A.B. | 65 | 46
> A.B. | 65 | 46
>
> Are not the lines out of order or is it a normal behaviour for a server with
> lc_collate=pt_BR.UTF-8?

Many collations ignore spaces and symbols on the first pass, so, for
example you might have "A Z" > "AB" despite the fact that a space has a
lower value than a B.