pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to setup a good collation?
5+ messages / 2 participants
[nested] [flat]
* How to setup a good collation?
@ 2016-03-06 09:13 Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-06 09:40 ` Re: How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
0 siblings, 1 reply; 5+ messages in thread
From: Nagy László Zsolt @ 2016-03-06 09:13 UTC (permalink / raw)
To: pgsql-admin
Example database on a Unix system:
CREATE DATABASE test
TEMPLATE = template0
ENCODING = 'UTF8'
LC_COLLATE = 'hu_HU.UTF-8'
LC_CTYPE = 'hu_HU.UTF-8'
CONNECTION LIMIT = -1;
\c test
create table a (name text collate "hu_HU.UTF-8");
insert into a values ('a');
insert into a values ('á');
insert into a values ('Á');
insert into a values ('b');
insert into a values ('e');
insert into a values ('é');
insert into a values ('É');
insert into a values ('o');
insert into a values ('ó');
insert into a values ('ö');
insert into a values ('U');
insert into a values ('Ü');
insert into a values ('Z');
select name from a order by upper(name) asc;
Results in:
"a"
"b"
"e"
"o"
"U"
"Z"
"á"
"Á"
"é"
"É"
"ó"
"ö"
"Ü"
Which is bad. The good order should be the following:
"a"
"á"
"Á"
"b"
"e"
"é"
"É"
"o"
"ó"
"ö"
"U"
"Ü"
"Z"
What is wrong with my collaction setup?
Thanks,
Laszlo
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to setup a good collation?
2016-03-06 09:13 How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
@ 2016-03-06 09:40 ` Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-06 09:53 ` Re: How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
0 siblings, 1 reply; 5+ messages in thread
From: Nagy László Zsolt @ 2016-03-06 09:40 UTC (permalink / raw)
To: pgsql-admin
To answer my own question: it seems that the problem is not with
postgresql but with the FreeBSD OS. FreeBSD does not support sorting by
unicode characters, so I need to recompile the postgresql server with
"use ICU for unicode collation" flag.
2016.03.06. 10:13 keltezéssel, Nagy László Zsolt írta:
> Example database on a Unix system:
>
> CREATE DATABASE test
> TEMPLATE = template0
> ENCODING = 'UTF8'
> LC_COLLATE = 'hu_HU.UTF-8'
> LC_CTYPE = 'hu_HU.UTF-8'
> CONNECTION LIMIT = -1;
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to setup a good collation?
2016-03-06 09:13 How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-06 09:40 ` Re: How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
@ 2016-03-06 09:53 ` Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-10 11:30 ` Re: How to setup a good collation? Reko Turja <reko.turja@liukuma.net>
0 siblings, 1 reply; 5+ messages in thread
From: Nagy László Zsolt @ 2016-03-06 09:53 UTC (permalink / raw)
To: pgsql-admin
All right, I have reinstalled postgresql server with "Use ICU for
unicode collation" but it did not help!
What else do I need to make this work?
Thanks
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to setup a good collation?
2016-03-06 09:13 How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-06 09:40 ` Re: How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-06 09:53 ` Re: How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
@ 2016-03-10 11:30 ` Reko Turja <reko.turja@liukuma.net>
2016-03-17 07:32 ` Re: How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
0 siblings, 1 reply; 5+ messages in thread
From: Reko Turja @ 2016-03-10 11:30 UTC (permalink / raw)
To: pgsql-admin; Nagy László Zsolt <gandalf@shopzeus.com>
> All right, I have reinstalled postgresql server with "Use ICU for
> unicode collation" but it did not help!
Postgres 9.3 on FreeBSD 10.2 with kernel, userland and software from
ports compiled with clang. Server compile flags include ICU in
addition of the defaults and client flags using non-GPL libedit
instead of readline.
test=# select name from a order by upper(name) asc;
name
------
a
á
Á
b
e
é
É
o
ó
ö
U
Ü
Z
(13 rows)
test=#
-Reko
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to setup a good collation?
2016-03-06 09:13 How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-06 09:40 ` Re: How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-06 09:53 ` Re: How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-10 11:30 ` Re: How to setup a good collation? Reko Turja <reko.turja@liukuma.net>
@ 2016-03-17 07:32 ` Nagy László Zsolt <gandalf@shopzeus.com>
0 siblings, 0 replies; 5+ messages in thread
From: Nagy László Zsolt @ 2016-03-17 07:32 UTC (permalink / raw)
To: pgsql-admin
>> All right, I have reinstalled postgresql server with "Use ICU for
>> unicode collation" but it did not help!
>
> Postgres 9.3 on FreeBSD 10.2 with kernel, userland and software from
> ports compiled with clang. Server compile flags include ICU in
> addition of the defaults and client flags using non-GPL libedit
> instead of readline.
Thank you for testing this! I have 9.4 but probably that doesn't matter.
I'm not sure what is causing the problem on my box. I'll be installing a
new virtual machine and try other variations/settings. Will be back
with the results soon.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2016-03-17 07:32 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-03-06 09:13 How to setup a good collation? Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-06 09:40 ` Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-06 09:53 ` Nagy László Zsolt <gandalf@shopzeus.com>
2016-03-10 11:30 ` Reko Turja <reko.turja@liukuma.net>
2016-03-17 07:32 ` Nagy László Zsolt <gandalf@shopzeus.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox