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