Hello
2011/2/5 Adam PAPAI <adam.papai@bsdsupportservice.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.papai@bsdsupportservice.hu
> Phone: +36 30 33-55-735 (Hungary)
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
