Postgres Pro
Downloads
Home   >   mailing lists

Thread: Collation problem?

Collation problem?

From
Bjørn T Johansen
Date:
My databases looks like this...:


                                     List of databases
     Name     |    Owner     | Encoding |   Collate   |    Ctype    |   Access privileges
--------------+--------------+----------+-------------+-------------+-----------------------
       dbname | owner        | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
.......


And my problem is that I am using Norwegian in some tables and when using order by the sort order is not correct for
theNorwegian letters.. 
So my guestion is if it is possible to get the correct sort order without recreating all my databases or initialize
PGSQL?


Regards,

BTJ

--
-----------------------------------------------------------------------------------------------
Bjørn T Johansen

btj@havleik.no
-----------------------------------------------------------------------------------------------
Someone wrote:
"I understand that if you play a Windows CD backwards you hear strange Satanic messages"
To which someone replied:
"It's even worse than that; play it forwards and it installs Windows"
-----------------------------------------------------------------------------------------------

Re: Collation problem?

From
Michael Paquier
Date:
On Sun, Apr 26, 2015 at 5:02 PM, Bjørn T Johansen wrote:
> And my problem is that I am using Norwegian in some tables and when using order by the sort order is not correct for
theNorwegian letters.. 
> So my guestion is if it is possible to get the correct sort order without recreating all my databases or initialize
PGSQL?

You can enforce the collate used in an ORDER BY clause:
SELECT a, b, c FROM tbl WHERE ... ORDER BY a COLLATE "C";
http://www.postgresql.org/docs/devel/static/sql-expressions.html#SQL-SYNTAX-COLLATE-EXPRS
Regards,
--
Michael

Re: Collation problem?

From
"Gunnar \"Nick\" Bluth"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Am 26.04.2015 um 10:02 schrieb Bjørn T Johansen:
> My databases looks like this...:
>
>
> List of databases Name     |    Owner     | Encoding |   Collate
> |    Ctype    |   Access privileges
> --------------+--------------+----------+-------------+-------------+-
- ----------------------
>
>
dbname | owner        | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> .......
>
>
> And my problem is that I am using Norwegian in some tables and when
> using order by the sort order is not correct for the Norwegian
> letters.. So my guestion is if it is possible to get the correct
> sort order without recreating all my databases or initialize
> PGSQL?
>
Sure (i.e., if you're on a half recent version):

ALTER TABLE <yourtable> ALTER COLUMN <yourcolumn> COLLATE <collation>


http://www.postgresql.org/docs/9.4/static/sql-altertable.html

- --
Gunnar "Nick" Bluth
RHCE/SCLA

Mobil +49 172 8853339
Email: gunnar.bluth@pro-open.de
_____________________________________________________________
In 1984 mainstream users were choosing VMS over UNIX.
Ten years later they are choosing Windows over UNIX.
What part of that message aren't you getting? - Tom Payne

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.22 (MingW32)

iQEcBAEBAgAGBQJVPKJoAAoJEBAQrmsyiTOMhRsH/3RENBEhVBHCKpAURq9EdPdj
gEB8vD9PY2U/m5L3vG/RrqNhtPbIhVsfLn0CUSqhTTh4VEmoiGIbRS8MTDxH79Nl
Ic/ovsjioPy7feIPBKRKALTY4R+8KG/XAIuY2WEWUcy3NTr5NX3id+BcHnOc5nXU
PB7QB3VD5a3YykWVE6/6OxskeoSiEN97ey4vbdav9qNNSQ60zt0gJa9SR7nGHsaV
M8yF9fG57TeUFrTaEkZNkmZwC4Ui4w+eKFZk0m9L13JXaoZ4xIqLvH1nufKIO0uB
SHBUl22MGKZoksg1KaAEvq6lYLGfdOvH53tsulw4nDR0w+VYztY4eIR0j1xm17c=
=Sh2G
-----END PGP SIGNATURE-----
Attachment

Re: Collation problem?

From
Bjørn T Johansen
Date:
On Sun, 26 Apr 2015 10:31:37 +0200
"Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Am 26.04.2015 um 10:02 schrieb Bjørn T Johansen:
> > My databases looks like this...:
> >
> >
> > List of databases Name     |    Owner     | Encoding |   Collate
> > |    Ctype    |   Access privileges
> > --------------+--------------+----------+-------------+-------------+-
> - ----------------------
> >
> >
> dbname | owner        | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> > .......
> >
> >
> > And my problem is that I am using Norwegian in some tables and when
> > using order by the sort order is not correct for the Norwegian
> > letters.. So my guestion is if it is possible to get the correct
> > sort order without recreating all my databases or initialize
> > PGSQL?
> >
> Sure (i.e., if you're on a half recent version):
>
> ALTER TABLE <yourtable> ALTER COLUMN <yourcolumn> COLLATE <collation>
>
>
> http://www.postgresql.org/docs/9.4/static/sql-altertable.html
>

Yes, I am using 9.4.x so I will check this out... Thx... :)

BTJ

Re: Collation problem?

From
Bjørn T Johansen
Date:
On Sun, 26 Apr 2015 10:59:10 +0200
Bjørn T Johansen <btj@havleik.no> wrote:

> On Sun, 26 Apr 2015 10:31:37 +0200
> "Gunnar \"Nick\" Bluth" <gunnar.bluth@pro-open.de> wrote:
>
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > Am 26.04.2015 um 10:02 schrieb Bjørn T Johansen:
> > > My databases looks like this...:
> > >
> > >
> > > List of databases Name     |    Owner     | Encoding |   Collate
> > > |    Ctype    |   Access privileges
> > > --------------+--------------+----------+-------------+-------------+-
> > - ----------------------
> > >
> > >
> > dbname | owner        | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
> > > .......
> > >
> > >
> > > And my problem is that I am using Norwegian in some tables and when
> > > using order by the sort order is not correct for the Norwegian
> > > letters.. So my guestion is if it is possible to get the correct
> > > sort order without recreating all my databases or initialize
> > > PGSQL?
> > >
> > Sure (i.e., if you're on a half recent version):
> >
> > ALTER TABLE <yourtable> ALTER COLUMN <yourcolumn> COLLATE <collation>
> >
> >
> > http://www.postgresql.org/docs/9.4/static/sql-altertable.html
> >
>
> Yes, I am using 9.4.x so I will check this out... Thx... :)
>
> BTJ
>
>


Ok, tried to run:

alter table medlem alter column fornavn varchar(50) collate nb_NO.utf8

But I just get:

 [Error Code: 0, SQL State: 42601]  ERROR: syntax error at or near "varchar"
(tried without the varchar also..)

Also tried running:

select fornavn from medlem order by fornavn collate nb_NO

Then I get:

ERROR: collation "nb_no" for encoding "UTF8" does not exist

Do I need to add some collation to the database or?


BTJ

Re: Collation problem?

From
Oliver Elphick
Date:
On Sun, 2015-04-26 at 11:09 +0200, Bjørn T Johansen wrote:
> Ok, tried to run:
>
> alter table medlem alter column fornavn varchar(50) collate nb_NO.utf8
>
> But I just get:
>
>  [Error Code: 0, SQL State: 42601]  ERROR: syntax error at or near "varchar"
> (tried without the varchar also..)

ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE
collation ] [ USING expression ]


You need to add the TYPE key word, I think.

Oliver Elphick

Re: Collation problem?

From
Bjørn T Johansen
Date:
On Sun, 26 Apr 2015 11:48:37 +0200
Oliver Elphick <olly@lfix.co.uk> wrote:

> On Sun, 2015-04-26 at 11:09 +0200, Bjørn T Johansen wrote:
> > Ok, tried to run:
> >
> > alter table medlem alter column fornavn varchar(50) collate nb_NO.utf8
> >
> > But I just get:
> >
> >  [Error Code: 0, SQL State: 42601]  ERROR: syntax error at or near "varchar"
> > (tried without the varchar also..)
>
> ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE
> collation ] [ USING expression ]
>
>
> You need to add the TYPE key word, I think.
>
> Oliver Elphick
>
>
>

Tried that to but got the same error..

But I think I need to create the collation in the database, but I am trying to run the following statement:

CREATE COLLATION nb_NO (LOCALE = nb_NO.utf8)

But then I get this:

ERROR:  could not create locale "nb_no.utf8": No such file or directory
DETAIL:  The operating system could not find any locale data for the locale name "nb_no.utf8".

I am using Fedora 21 and running locale -a shows these among the locales:

nb_NO
nb_NO.iso88591
nb_NO.utf8

What am I missing? I see the error message from pgsql shows the country code using lowercase, is that the problem?
Any ideas?


BTJ

Re: Collation problem?

From
Oliver Elphick
Date:
On Sun, 2015-04-26 at 12:39 +0200, Bjørn T Johansen wrote:
>
> CREATE COLLATION nb_NO (LOCALE = nb_NO.utf8)
>
> But then I get this:
>
> ERROR:  could not create locale "nb_no.utf8": No such file or
> directory
> DETAIL:  The operating system could not find any locale data for the
> locale name "nb_no.utf8".
>
> I am using Fedora 21 and running locale -a shows these among the
> locales:
>
> nb_NO
> nb_NO.iso88591
> nb_NO.utf8
>
> What am I missing? I see the error message from pgsql shows the
> country code using lowercase, is that the problem?
> Any ideas?

Perhaps you need to use double quotes to preserve case:
 LOCALE = "nb_NO.utf8"

Re: Collation problem?

From
Bjørn T Johansen
Date:
On Sun, 26 Apr 2015 12:43:57 +0200
Oliver Elphick <olly@lfix.co.uk> wrote:

> On Sun, 2015-04-26 at 12:39 +0200, Bjørn T Johansen wrote:
> >
> > CREATE COLLATION nb_NO (LOCALE = nb_NO.utf8)
> >
> > But then I get this:
> >
> > ERROR:  could not create locale "nb_no.utf8": No such file or
> > directory
> > DETAIL:  The operating system could not find any locale data for the
> > locale name "nb_no.utf8".
> >
> > I am using Fedora 21 and running locale -a shows these among the
> > locales:
> >
> > nb_NO
> > nb_NO.iso88591
> > nb_NO.utf8
> >
> > What am I missing? I see the error message from pgsql shows the
> > country code using lowercase, is that the problem?
> > Any ideas?
>
> Perhaps you need to use double quotes to preserve case:
>  LOCALE = "nb_NO.utf8"
>

Yes, that's it... :) Tried using double qoutes around both nb_NO and nb_NO.utf8 but that did not work but only around
nb_NO.utf8did the trick... 

Thx... :)


BTJ