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.