Re: [ADMIN] to --enable-locale or not to --enable-locale?
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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[EMAIL PROTECTED] 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 signature.asc Description: This is a digitally signed message part
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Oliver Elphick Sent: den 25 mars 2002 12:26 To: Niclas Gustafsson Cc: [EMAIL PROTECTED] 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[EMAIL PROTECTED] 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
Re: [ADMIN] to --enable-locale or not to --enable-locale?
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[EMAIL PROTECTED] 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 signature.asc Description: This is a digitally signed message part
Re: [ADMIN] to --enable-locale or not to --enable-locale?
Sorry, I'm a bit overclocked today, the locale is ofcourse sv_SE... -Original Message- From: Oliver Elphick [mailto:[EMAIL PROTECTED]] Sent: den 25 mars 2002 13:57 To: Niclas Gustafsson Cc: [EMAIL PROTECTED] 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[EMAIL PROTECTED] 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] to --enable-locale or not to --enable-locale?
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[EMAIL PROTECTED] 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 signature.asc Description: This is a digitally signed message part
Re: [ADMIN] to --enable-locale or not to --enable-locale?
Niclas Gustafsson [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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
[ADMIN] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] to --enable-locale or not to --enable-locale?
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
