Tulio wrote:
> Let me expand the collate situation. I´m from Perú and I have turned
> everything in postgresql.conf as 'es_PE.UTF-8' even the
> default_text_search_config = 'pg_catalog.spanish'. Even my Ubuntu 12.04
> works in English I have es_PE locale too.
> if I do
>
> SELECT * FROM pru order by dad,mum,name;
>
> I get:
>
> id | dad | mum | name
> ----+------+----------+-------
> 2 | leon | mendoza | juan
> 6 | leon | valencia | josie
> 5 | león | mendoza | jua
> 3 | león | valárd | jose
> 1 | león | valencia | josé
> 7 | león | valencia | josie
> 4 | león | válencia | jos
> (7 rows)
>
> Which is a wrong order (collation) in Spanish and I don´t understand why.
Maybe you misunderstood what it means to ORDER BY multiple
columns. In your query, the rows are ordered by "dad",
then all rows where "dad" is the same are ordered by "mum",
and finally all rows where "dad" and "mum" are the same
are ordered by "name".
It is explained in the documentation:
http://www.postgresql.org/docs/current/static/queries-order.html
"When more than one expression is specified, the later values
are used to sort rows that are equal according to the earlier values."
> But, I noticed that if I do:
>
> SELECT * FROM pru order by dad || mum || name;
>
> I get the correct order:
>
> id | dad | mum | name
> ----+------+----------+-------
> 5 | león | mendoza | jua
> 2 | leon | mendoza | juan
> 3 | león | valárd | jose
> 4 | león | válencia | jos
> 1 | león | valencia | josé
> 6 | leon | valencia | josie
> 7 | león | valencia | josie
> (7 rows)
>
>
> Is this the correct way to order in Postgresql and if it´s not Does anyone
> have an idea and could please explain it to me?
This is not PostgreSQL-specific behaviour, it is defined in the
SQL standard and works like this on all database systems I know.
You can use the ORDER BY clause you propose if you prefer this
ordering.
But would you really order 'leon', 'mendoza', 'juan' before
'leo', 'zara', 'juan'?
Yours,
Laurenz Albe