Re: Create collation fails

On Sat, Nov 24, 2012 at 6:24 AM, Čikić Nenad <nenad.cikic@gmail.com> wrote:
> 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

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

> 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

> 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