Re: Order by behaviour

[Stephan Szabo <sszabo@xxxxxxxxxxxxxxxxxxxxx>]

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.