Hello 2011/2/5 Adam PAPAI <adam.pa...@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.pa...@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 > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general