Thread: Create collation fails
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
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
On 25.11.2012. 23:16, Josh Kupershmidt wrote: > 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 Thanks. Now it works. I think i missed the UTF-8 from the CREATE COLLATION statement. Nenad