Collation problem?

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 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?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

-----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-----

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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"

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.utf8 did the trick...

Thx... :)

BTJ

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general