Re: spanish locale question - Mailing list pgsql-general

From Albe Laurenz
Subject Re: spanish locale question
Date
Msg-id D960CB61B694CF459DCFB4B0128514C207D50167@exadv11.host.magwien.gv.at
Whole thread Raw
In response to Re: spanish locale question  (Tulio <tulio.carrasco@gmail.com>)
Responses Re: spanish locale question
Re: spanish locale question
List pgsql-general
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

pgsql-general by date:

Previous
From: Magnus Hagander
Date:
Subject: Re: errors on restoring postgresql binary dump to glusterfs
Next
From: Stuart Bishop
Date:
Subject: Re: Is it possible to call other functions inside plpythonu?