From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Adam PAPAI <adam(dot)papai(at)bsdsupportservice(dot)hu> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PostgreSQL 9.0.3 - collate/ctype order by hu_HU.UTF8 is not correct sorting |
Date: | 2011-02-05 17:51:10 |
Message-ID: | AANLkTimj3qJ8G-GSkHj7d9C4L=REB5xaZo=cAPxHGgyc@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Vincent Veyron | 2011-02-05 18:21:07 | Re: set theory question |
Previous Message | ray | 2011-02-05 17:30:13 | Looking for Suggestion on Learning |