Create collation fails

[Čikić Nenad]

Hello!
I am novice linux,postgre,psql and so on :)
I am using ubuntu 11.10, postgre 9.1
My database has:
server_encoding UTF8
lc_collate en_US.UTF8

I am developing an application for android and I want to serve
multilingual data. I thought of using on android unicode collation
algorithm, and I will do,
but I have non consistent behaviour and googling it seems that on
android it does not behave always well. So I thought of serving clients
on their request such for example
select * from xxx order by collation fr_FR

To check this behaviour I have found that I have to do
( as explained by mr Lelarge in
http://postgresql.1045698.n5.nabble.com/Specifying-column-level-collations-td4378231.html)
CREATE COLLATION fr (locale='fr_FR');
but then it does not worked for me so I have found that I have to do
(as explained in
http://archives.postgresql.org/pgsql-bugs/2011-03/msg00013.php)
apt-get install language-pack-fr

the second language-support-fr does not exist though.

Well still my psql CREATE COLLATION statement fails.

So I have 2 questions:
1. How can I talk to french girls in their collation?
2. Is the idea of using "order by collation" on utf8 data stupid?

Thanks
Cikic Nenad

[Josh Kupershmidt]

Post by Čikić Nenad
Hello!
I am novice linux,postgre,psql and so on :)
I am using ubuntu 11.10, postgre 9.1
server_encoding UTF8
lc_collate en_US.UTF8
I am developing an application for android and I want to serve multilingual
data. I thought of using on android unicode collation algorithm, and I will
do,
but I have non consistent behaviour and googling it seems that on android it
does not behave always well. So I thought of serving clients on their
request such for example
select * from xxx order by collation fr_FR

The syntax you want given the CREATE COLLATION you are trying below is probably:
SELECT * FROM xxx ORDER BY colname COLLATE "fr";

see also the examples at:
http://www.postgresql.org/docs/current/static/collation.html

Post by Čikić Nenad
To check this behaviour I have found that I have to do
( as explained by mr Lelarge in
http://postgresql.1045698.n5.nabble.com/Specifying-column-level-collations-td4378231.html)
CREATE COLLATION fr (locale='fr_FR');
but then it does not worked for me so I have found that I have to do
(as explained in
http://archives.postgresql.org/pgsql-bugs/2011-03/msg00013.php)
apt-get install language-pack-fr
the second language-support-fr does not exist though.

On Debian, I was able to use
$ sudo dpkg-reconfigure locales

and enable "fr_FR.UTF-8 UTF-8" from the list. I had to restart my
Postgres server, but afterwards this worked fine:
CREATE COLLATION fr (LOCALE = 'fr_FR.UTF-8');

The locale generation procedure might be a tad different on Ubuntu though, see:
http://www.ubuntugeek.com/how-to-select-and-generate-locales-on-ubuntu.html

Post by Čikić Nenad
Well still my psql CREATE COLLATION statement fails.

Can you verify that:
$ locale -a

says that fr_FR.utf8 is available after following the locale
generation steps for Ubuntu. If so, what error message do you get when
trying CREATE COLLATION?

Josh

[Čikić Nenad]

Post by Josh Kupershmidt

Post by Čikić Nenad
Hello!
I am novice linux,postgre,psql and so on :)
I am using ubuntu 11.10, postgre 9.1
server_encoding UTF8
lc_collate en_US.UTF8
I am developing an application for android and I want to serve multilingual
data. I thought of using on android unicode collation algorithm, and I will
do,
but I have non consistent behaviour and googling it seems that on android it
does not behave always well. So I thought of serving clients on their
request such for example
select * from xxx order by collation fr_FR

SELECT * FROM xxx ORDER BY colname COLLATE "fr";
http://www.postgresql.org/docs/current/static/collation.html

Post by Čikić Nenad
To check this behaviour I have found that I have to do
( as explained by mr Lelarge in
http://postgresql.1045698.n5.nabble.com/Specifying-column-level-collations-td4378231.html)
CREATE COLLATION fr (locale='fr_FR');
but then it does not worked for me so I have found that I have to do
(as explained in
http://archives.postgresql.org/pgsql-bugs/2011-03/msg00013.php)
apt-get install language-pack-fr
the second language-support-fr does not exist though.

On Debian, I was able to use
$ sudo dpkg-reconfigure locales
and enable "fr_FR.UTF-8 UTF-8" from the list. I had to restart my
CREATE COLLATION fr (LOCALE = 'fr_FR.UTF-8');
http://www.ubuntugeek.com/how-to-select-and-generate-locales-on-ubuntu.html

Post by Čikić Nenad
Well still my psql CREATE COLLATION statement fails.

$ locale -a
says that fr_FR.utf8 is available after following the locale
generation steps for Ubuntu. If so, what error message do you get when
trying CREATE COLLATION?
Josh

Thanks.
Now it works. I think i missed the UTF-8 from the CREATE COLLATION
statement.

Nenad