Re: spanish locale question

Thank you Laurenz for your answer.

I think I understand the meaning of ORDER BY my problem is the
understanding of COLLATE functionality. I thought that if I collate in es.PE
(Spanish) the "o" has the exact value of "ó" so if I

INSERT INTO pru VALUES (8, 'leo','zara', 'juan');

How can I obtain this order:

id | dad | mum | name
----+------+----------+-------
8 | leo | zara | juan
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

In the understanding (please forgive I repeat) that "o" and "ó" has no
difference in weight in the Spanish collation. As you can see below in the
first table of your answer the collation doesn´t work. It assumes that leon
is different from león (with accent). I was assuming the collation
eliminates such difference. It isn´t that way it works?

Thanks in advance for your help.

Tulio

Albe Laurenz *EXTERN* wrote
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
View this message in context: http://postgresql.1045698.n5.nabble.com/spanish-locale-question-tp5650043p5687242.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.