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