Re: PostgreSQL 9.0.3 - collate/ctype order by hu_HU.UTF8 is not correct sorting

Hello

2011/2/5 Adam PAPAI <adam(dot)papai(at)bsdsupportservice(dot)hu>:
> I have a PgSQL node on a FreeBSD 8.1-RELEASE.
>
> (PostgreSQL) 9.0.3 client/server.
>
> pkg_info | grep 'postgresql'
> postgresql-client-9.0.3
> postgresql-server-9.0.3
>
>
> I have a lots of UTF8 database with hu_HU.UTF8 COLLATE and LC_CTYPE
>
> For example:
>
> test       | pgsql | UTF8      | hu_HU.UTF-8 | hu_HU.UTF-8
>
> The problem is with the "ORDER BY", which does not sort the results
> corretly.
>
>        Table "public.erdekessegek"
>  Column |          Type          | Modifiers
> --------+------------------------+-----------
>  lead   | character varying(255) |
>
>
> test=# select * from erdekessegek  order by lead asc;
>  lead
> -------
>  Adi
>  Bedi
>  Evi
>  Zsolt
>  Ádi
>  Évi
> (6 rows)
>
> which is not correct.
>
> It should be:
>
> Adi
> Ádi
> Bedi
> Evi
> Évi
> Zsolt
>
> The "chars" wich accents always at the end of the order by but I don't
> know why.
>
> What can I do to make it work? I've read all docs on this topic and all
> sais the LC_COLLATE and LC_CTYPE solves it, but it seems not.
>

You should to initialize database with correct locale. You can't to
change locale after database is created.

/usr/local/pgsql91/bin/createdb test --locale=cs_CZ.utf-8 -e UTF-8
CREATE DATABASE test LC_COLLATE 'cs_CZ.utf-8' LC_CTYPE 'cs_CZ.utf-8';
COMMENT ON DATABASE test IS 'UTF-8';

test=# show lc_collate ;
lc_collate
-------------
cs_CZ.utf-8
(1 row)

test=# select * from (values('Sís'),('Šiška'),('Syrový'),('Vondra')) x
ORDER BY 1;
column1
---------
Sís
Syrový
Šiška
Vondra
(4 rows)

Regards

Pavel Stehule

> Any advice would be helpful.
>
> Thanks in advance,
>
> --
> Adam PAPAI
> BSD Support Service
> http://www.bsdsupportservice.hu
> E-mail: adam(dot)papai(at)bsdsupportservice(dot)hu
> Phone: +36 30 33-55-735 (Hungary)
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>