Thread: to --enable-locale or not to --enable-locale?
Hi! I am now in the process of upgrading a couple of pg installation to 7.2. I am a little in doubt if I should or not use the --enable-locale when ./configuring. Even though I am a Norwegian, I prefer to have the messages from the data base in english as that makes it much simpler to ask questions here or to searc for help on the web. on the other hand, I need to store character string containing letters outside the 7bit ASCII char.set (e.g. æøåäëö etc.) and I've had some problems there with my existing 7.1.3 and 7.1.2 installations. E.g what is put in as 'Tromsø' at one PC later shows up as 'Troms>' at another one running the same application... Would --enable-locale or some other ./configure flags (--enable-nls?, --enable-recode, --enable-multibyte?) help here, or do I have to tweak my application (written in Delphi, using Pg-ODBC)somehow? Morten (well, I assume I have to do some data cleaning if I transfer my data to a new database with a different coding, but I prefer that to the present situation) -- Morten Sickel Norwegian Radiation Protection Authority
Morten Sickel writes: > Even though I am a Norwegian, I prefer to have the messages from the data > base in english as that makes it much simpler to ask questions here or to > searc for help on the web. on the other hand, I need to store character > string containing letters outside the 7bit ASCII char.set (e.g. æøåäëö etc.) > and I've had some problems there with my existing 7.1.3 and 7.1.2 > installations. E.g what is put in as 'Tromsø' at one PC later shows up as > 'Troms>' at another one running the same application... Would > --enable-locale or some other ./configure flags (--enable-nls?, > --enable-recode, --enable-multibyte?) help here, or do I have to tweak my > application (written in Delphi, using Pg-ODBC)somehow? OK, let's see: --enable-locale gives you two things: 1) The ability to sort text according to your local preference (i.e., a..zæøå) in Norwegian. 2) The ability to do case-insensitive text comparisons (so ø and Ø match) I would guess that you want that. --enable-multibyte gives you two things: 1) The ability to store multibyte characters in the database. Since Norwegian works with ISO 8859-1 or -15, you probably don't need that. 2) The ability to do character set conversions between client and server. If you client is all Unicode (which at least all Java and Tcl clients are), then you need this. (Or you can store everything in the database in Unicode, but then you need (1).) --enable-recode is a simplified version of part (2) of multibyte, which only works for single-byte encodings. It's mostly useful for environments where Unix and Windows use different character sets for the same language. (I think Czech was an example.) --enable-nls gives you the ability to see messages in a different language, but since no one has contributed a Norwegian translation yet, this is going to give you zero benefit. -- Peter Eisentraut peter_e@gmx.net
Le Vendredi 15 Mars 2002 17:19, Peter Eisentraut a écrit : > --enable-recode is a simplified version of part (2) of multibyte, which > only works for single-byte encodings. It's mostly useful for environments > where Unix and Windows use different character sets for the same language. > (I think Czech was an example.) As of PostgreSQL 7.2+, --enable--recode provides: - Unicode <-> Latin1/Latin15 recoding, - Unicode <-> SJIS (=Japanese Multibyte), - and much more... Client and server encodings can be set separately. Examples: - CREATE DABASE foo WITH ENCODING 'Unicode'; - SET CLIENT_ENCODING = 'Latin9' (=ISO-8859-15) = Latin1 + euro symbol. In pgAdmin2, we plan to take advantage of these new features to : - change client encoding on the fly, - display multi-byte text. Cheers, Jean-Michel POURE
Hi, I'm having trouble sorting my data.
I'm using PostgresQL 7.2 compiled with:
--enable-multibyte=LATIN1 --enable-locale
I've also created a test db, with one table:
CREATE DATABASE "testdb" WITH ENCODING = 'LATIN1';
And
CREATE TABLE "sorttest" (
"id" int8 NOT NULL,
"data" varchar(100),
CONSTRAINT "sorttest_pkey" PRIMARY KEY ("id")
) WITH OIDS;
Then I've inserted some test values which seem to be sorted wrongfully
when I issue an
select * from sorttest order by data
This is the output I get:
aa
aå
aä
åa
äa
ab
åb
äb
aö
ba
bå
bä
bb
bö
za
zö
I want it sorted in abcd..zåäö
What am I missing here? Any Ideas?
Regards,
Niclas Gustafsson
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Jean-Michel POURE
Sent: den 17 mars 2002 11:15
To: Peter Eisentraut; Morten Sickel
Cc: Pgsql-Admin (E-post)
Subject: Re: [ADMIN] to --enable-locale or not to --enable-locale?
Le Vendredi 15 Mars 2002 17:19, Peter Eisentraut a écrit :
> --enable-recode is a simplified version of part (2) of multibyte,
which
> only works for single-byte encodings. It's mostly useful for
environments
> where Unix and Windows use different character sets for the same
language.
> (I think Czech was an example.)
As of PostgreSQL 7.2+, --enable--recode provides:
- Unicode <-> Latin1/Latin15 recoding,
- Unicode <-> SJIS (=Japanese Multibyte),
- and much more...
Client and server encodings can be set separately. Examples:
- CREATE DABASE foo WITH ENCODING 'Unicode';
- SET CLIENT_ENCODING = 'Latin9' (=ISO-8859-15) = Latin1 + euro symbol.
In pgAdmin2, we plan to take advantage of these new features to :
- change client encoding on the fly,
- display multi-byte text.
Cheers,
Jean-Michel POURE
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
On Mon, 2002-03-25 at 11:14, Niclas Gustafsson wrote: > Hi, I'm having trouble sorting my data. > > I'm using PostgresQL 7.2 compiled with: > --enable-multibyte=LATIN1 --enable-locale ... > Then I've inserted some test values which seem to be sorted wrongfully > when I issue an > select * from sorttest order by data ... > I want it sorted in abcd..zåäö > What am I missing here? Any Ideas? 1. Does the locale you are using sort as you want? 2. Did you initdb with that locale set? (Use pg_controldata from contrib to see what locale the backend is using.) locale must be set correctly for initdb, to ensure that indexes don't get broken by changes of locale. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11
Attachment
Hi, I am sure that 1) is correct, allthough I think I've overlooked 2)! pg_controldata reports: (last 2 rows) LC_COLLATE: en_US LC_CTYPE: en_US Is it possible to change this after you've run initdb? Regards, Niclas Gustafsson -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Oliver Elphick Sent: den 25 mars 2002 12:26 To: Niclas Gustafsson Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] to --enable-locale or not to --enable-locale? On Mon, 2002-03-25 at 11:14, Niclas Gustafsson wrote: > Hi, I'm having trouble sorting my data. > > I'm using PostgresQL 7.2 compiled with: > --enable-multibyte=LATIN1 --enable-locale ... > Then I've inserted some test values which seem to be sorted wrongfully > when I issue an > select * from sorttest order by data ... > I want it sorted in abcd..zåäö > What am I missing here? Any Ideas? 1. Does the locale you are using sort as you want? 2. Did you initdb with that locale set? (Use pg_controldata from contrib to see what locale the backend is using.) locale must be set correctly for initdb, to ensure that indexes don't get broken by changes of locale. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11
Hrm, a big RTFM to myself. :) >"The sort order used within a particular database cluster is set >by initdb and cannot be changed later, short of dumping all data, >rerunning initdb, and reloading the data." Well I changed LC_ALL to LATIN1 and run initdb, dumped back all the data, but the sorting order seem to wrong still, allthough different, pg_controldata shows: LC_COLLATE: C LC_CTYPE: C Now I get the data sorted in the order below, quite close though: aa ab aä aå aö ba bb bä bå bö za zö äa äb åa åb The å and ä are sorted in the wrong order. Where can I see the order of the charset? Regards, Niclas Gustafsson -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Niclas Gustafsson Sent: den 25 mars 2002 12:47 To: 'Oliver Elphick' Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] to --enable-locale or not to --enable-locale? Hi, I am sure that 1) is correct, allthough I think I've overlooked 2)! pg_controldata reports: (last 2 rows) LC_COLLATE: en_US LC_CTYPE: en_US Is it possible to change this after you've run initdb? Regards, Niclas Gustafsson -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Oliver Elphick Sent: den 25 mars 2002 12:26 To: Niclas Gustafsson Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] to --enable-locale or not to --enable-locale? On Mon, 2002-03-25 at 11:14, Niclas Gustafsson wrote: > Hi, I'm having trouble sorting my data. > > I'm using PostgresQL 7.2 compiled with: > --enable-multibyte=LATIN1 --enable-locale ... > Then I've inserted some test values which seem to be sorted wrongfully > when I issue an > select * from sorttest order by data ... > I want it sorted in abcd..zåäö > What am I missing here? Any Ideas? 1. Does the locale you are using sort as you want? 2. Did you initdb with that locale set? (Use pg_controldata from contrib to see what locale the backend is using.) locale must be set correctly for initdb, to ensure that indexes don't get broken by changes of locale. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
On Mon, 2002-03-25 at 11:47, Niclas Gustafsson wrote: > Hi, > > I am sure that 1) is correct, allthough I think I've overlooked 2)! > pg_controldata reports: (last 2 rows) > LC_COLLATE: en_US > LC_CTYPE: en_US > > Is it possible to change this after you've run initdb? No. You have to dump the database, destroy everything, do initdb again (with locale set correctly) and reload from your dump. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11
Attachment
On Mon, 2002-03-25 at 12:41, Niclas Gustafsson wrote: > Hrm, a big RTFM to myself. :) > >"The sort order used within a particular database cluster is set > >by initdb and cannot be changed later, short of dumping all data, > >rerunning initdb, and reloading the data." > > Well I changed LC_ALL to LATIN1 and run initdb, dumped back all the > data, but the sorting order seem to wrong still, allthough different, LATIN1 is an encoding, but I don't think it is a locale. A locale looks like de_DE@euro or en_GB: it consists of a language code followed by a country code and an optional supplement. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11
Attachment
Sorry, I'm a bit overclocked today, the locale is ofcourse sv_SE... -----Original Message----- From: Oliver Elphick [mailto:olly@lfix.co.uk] Sent: den 25 mars 2002 13:57 To: Niclas Gustafsson Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] to --enable-locale or not to --enable-locale? On Mon, 2002-03-25 at 12:41, Niclas Gustafsson wrote: > Hrm, a big RTFM to myself. :) > >"The sort order used within a particular database cluster is set > >by initdb and cannot be changed later, short of dumping all data, > >rerunning initdb, and reloading the data." > > Well I changed LC_ALL to LATIN1 and run initdb, dumped back all the > data, but the sorting order seem to wrong still, allthough different, LATIN1 is an encoding, but I don't think it is a locale. A locale looks like de_DE@euro or en_GB: it consists of a language code followed by a country code and an optional supplement. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11
On Mon, 2002-03-25 at 12:59, Niclas Gustafsson wrote: > Sorry, I'm a bit overclocked today, the locale is ofcourse > sv_SE... Well if that doesn't sort right, it is a problem with the lcoale definition, not PostgreSQL. As I understand it, PostgreSQL merely uses the locale without amendment. -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Why are you downcast, O my soul? Why so disturbed within me? Put your hope in God, for I will yet praise Him, my Saviour and my God." Psalm 42:11
Attachment
"Niclas Gustafsson" <niclas.gustafsson@codesense.com> writes:
> Well I changed LC_ALL to LATIN1 and run initdb, dumped back all the
> data, but the sorting order seem to wrong still, allthough different,
> pg_controldata shows:
> LC_COLLATE: C
> LC_CTYPE: C
"C" is certainly not the locale you want...
regards, tom lane