Re: [GENERAL] Strange query results with invalid multibyte
Hi Tom, Thanks for putting up with the questions. On Wed, 2006-09-27 at 14:58 -0400, Tom Lane wrote: LANG=en_US locale charmap I have done this on both machines: The working machine says: ISO-8859-1 The not working machine says: ISO-8859-1 I still do not understand what is happening and maybe more important, how I can solve this (without re-installing the OS / database). A better understanding of how the server OS, database, codepages, client OS, charmaps etc work would be nice -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Strange query results with invalid multibyte
Joost Kraaijeveld [EMAIL PROTECTED] writes: On Wed, 2006-09-27 at 14:58 -0400, Tom Lane wrote: LANG=en_US locale charmap The working machine says: ISO-8859-1 The not working machine says: ISO-8859-1 OK, so the problem is that you have a locale that expects ISO-8859-1 encoding, but the database is set to UTF8 encoding, and that means it may contain characters that the locale can't deal with. Locale-dependent operations ... like upper() ... will fail when that happens. If we had a more reliable way of telling what encoding the locale expects, we'd probably forbid creation of databases with incompatible encodings. I still do not understand what is happening and maybe more important, how I can solve this (without re-installing the OS / database). If you want to use UTF8 then you're stuck with a re-initdb I'm afraid. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Strange query results with invalid multibyte character
Hi, I have a strange problem. I have 2 database, both created with: CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default; Running the queries below on the first database is OK and do what I expect. If I create a backup of the first datase and restore that backup in the second database the following happens: The first query (see below) returns 17 records, all containing 'Boonk%'. The second query (see below)returns 'ERROR: invalid multibyte character for locale' Query 1: select lastname from salesordercustomer where lower(lastname) like 'boonk%' Query 2: select lastname from salesordercustomer where upper(lastname) like 'BOONK%' Both database are running *exactly* the same version of Debian Etch AMD64 and *exactly* the same version of PostgreSQL (8.1.4 for AMD64) Can anyone explain this to me? TIA Joost ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Strange query results with invalid multibyte
Hi Joost. (hoe gaat het ermee?) I would like to test this too. Could you please provide the data you are inserting into the database. Regards, Gevik On Wed, 2006-09-27 at 13:13 +0200, Joost Kraaijeveld wrote: Hi, I have a strange problem. I have 2 database, both created with: CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default; Running the queries below on the first database is OK and do what I expect. If I create a backup of the first datase and restore that backup in the second database the following happens: The first query (see below) returns 17 records, all containing 'Boonk%'. The second query (see below)returns 'ERROR: invalid multibyte character for locale' Query 1: select lastname from salesordercustomer where lower(lastname) like 'boonk%' Query 2: select lastname from salesordercustomer where upper(lastname) like 'BOONK%' Both database are running *exactly* the same version of Debian Etch AMD64 and *exactly* the same version of PostgreSQL (8.1.4 for AMD64) Can anyone explain this to me? TIA Joost ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Regards, Gevik Babakhani ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Strange query results with invalid multibyte character
Joost Kraaijeveld [EMAIL PROTECTED] writes: I have 2 database, both created with: CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default; But have they got the same locale settings (lc_collate, lc_ctype)? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Strange query results with invalid multibyte
Hi Tom, On Wed, 2006-09-27 at 11:00 -0400, Tom Lane wrote: Joost Kraaijeveld [EMAIL PROTECTED] writes: I have 2 database, both created with: CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default; But have they got the same locale settings (lc_collate, lc_ctype)? According to the postgresql.conf of the *working* database the locales are: lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' According to the other obe: lc_messages = 'en_US' lc_monetary = 'en_US' lc_numeric = 'en_US' lc_time = 'en_US' Could this be the difference? Is there any documentation available somewhere, on how these locale settings work and interact (in combination with the server and/or client platform???), besides the explanation in the PostgreSQL manual (http://www.postgresql.org/docs/8.1/interactive/charset.html#LOCALE) (which is to terse for me to understand)? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Strange query results with invalid multibyte character
Joost Kraaijeveld [EMAIL PROTECTED] writes: On Wed, 2006-09-27 at 11:00 -0400, Tom Lane wrote: But have they got the same locale settings (lc_collate, lc_ctype)? According to the postgresql.conf of the *working* database the locales are: lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' According to the other obe: lc_messages = 'en_US' lc_monetary = 'en_US' lc_numeric = 'en_US' lc_time = 'en_US' You did not show us the settings that actually count, but based on this I'm guessing they are en_US also. What you need to find out next is what character set encoding that locale implies on your machine. I'm betting it's not utf8 though :-( regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Strange query results with invalid multibyte
On Wed, 2006-09-27 at 12:10 -0400, Tom Lane wrote: Joost Kraaijeveld [EMAIL PROTECTED] writes: On Wed, 2006-09-27 at 11:00 -0400, Tom Lane wrote: But have they got the same locale settings (lc_collate, lc_ctype)? According to the postgresql.conf of the *working* database the locales are: lc_messages = 'C' lc_monetary = 'C' lc_numeric = 'C' lc_time = 'C' According to the other obe: lc_messages = 'en_US' lc_monetary = 'en_US' lc_numeric = 'en_US' lc_time = 'en_US' You did not show us the settings that actually count, but based on this I'm guessing they are en_US also Ah, sorry: show lc_collate and show lc_ctype confirm your guess. What you need to find out next is what character set encoding that locale implies on your machine. I'm betting it's not utf8 though :-( I am not sure what you mean by that but maybe this helps: the output of locale on the working machine is: LANG= LANGUAGE=en_US:en_GB LC_CTYPE=POSIX LC_NUMERIC=POSIX LC_TIME=POSIX LC_COLLATE=POSIX LC_MONETARY=POSIX LC_MESSAGES=POSIX LC_PAPER=POSIX LC_NAME=POSIX LC_ADDRESS=POSIX LC_TELEPHONE=POSIX LC_MEASUREMENT=POSIX LC_IDENTIFICATION=POSIX LC_ALL= The output of the same command on the non-working machine: LANG=en_US LANGUAGE=en_NL:en_US:en_GB:en LC_CTYPE=en_US LC_NUMERIC=en_US LC_TIME=en_US LC_COLLATE=en_US LC_MONETARY=en_US LC_MESSAGES=en_US LC_PAPER=en_US LC_NAME=en_US LC_ADDRESS=en_US LC_TELEPHONE=en_US LC_MEASUREMENT=en_US LC_IDENTIFICATION=en_US LC_ALL= If this is not what you mean, could you help me in the right direction? -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Strange query results with invalid multibyte
Joost Kraaijeveld [EMAIL PROTECTED] writes: On Wed, 2006-09-27 at 12:10 -0400, Tom Lane wrote: What you need to find out next is what character set encoding that locale implies on your machine. I'm betting it's not utf8 though :-( If this is not what you mean, could you help me in the right direction? Try this command: LANG=en_US locale charmap regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] strange query results
HI tom A few days back i had bugged this list about the seemingly impossible select queries results.. ##RECAP## select distinct site_section as "distinct site sections" from exhibit_distributions ; distinct site sections ARCHIVED ARTETC CALENDAR GALLERY POSTCARD (5 rows) select site_section, count(*) from exhibit_distributions group by site_section; site_section | count --+--- | 352 |45 | 1 | 166 | 2 ##RECAP## On going thru the flat files which we used to uplaod the database we noticed a few fields had '' characters to signify '.. This was creating problems in some jdbc queries. One changing them to single ' and reloading all the data, the problem simply disappered.. Thanks for the help Anand Raman On Tue, Feb 13, 2001 at 10:20:53AM -0500, Tom Lane wrote: Anand Raman [EMAIL PROTECTED] writes: The table description is as follows Hmm ... nothing obviously funny here. Is there anything unusual about the history of this table? (For example, were site_section or any other columns added via ALTER TABLE, rather than being there all along?) regards, tom lane
Re: [GENERAL] strange query results
Anand Raman [EMAIL PROTECTED] writes: The second query is not priniting out site_section column. Odd. What is the exact definition of table exhibit_distributions? Does it have any indices? What plan is printed by EXPLAIN for the problem query? regards, tom lane
Re: [GENERAL] strange query results
Hi tom
The table description is as follows
arttoday= \d exhibit_distributions
Table "exhibit_distributions"
Attribute | Type | Modifier
--+---+--
exhibit_distribution_id | integer | not null default
nextval('sq_exhibit_dist_id'::text)
exhibit_id | integer | not null
created_by | integer | not null
creation_date| timestamp | not null default
"timestamp"('now'::text)
last_update_date | timestamp |
last_updated_by | integer |
exhibit_type_id | integer | not null
medium | varchar(100) |
image_path_small | varchar(50) |
image_path_big | varchar(50) |
length | numeric(7,2) |
breadth | numeric(7,2) |
width| numeric(7,2) |
diameter | numeric(7,2) | default 8
dimensional_aspect | varchar(50) | default 'Dimensions'
unframed_volume_weight | numeric(10,2) |
framed_volume_weight | numeric(10,2) |
override_vw_computation | boolean | default 'f'::bool
exhibit_code | varchar(25) |
exhibit_options | varchar(10) |
unframed_exhibit_restriction | varchar(25) | default 'WORLD'
framed_exhibit_restriction | varchar(25) | default 'WORLD'
up_for_sale | char(1) | not null default 'T'
gallery_id | integer |
site_section | varchar(20) | not null default 'GALLERY'
Index: exhibit_distributions_pkey
Constraints: ((up_for_sale = 'T'::bpchar) OR (up_for_sale = 'F'::bpchar))
(length '0'::"numeric")
(breadth '0'::"numeric")
(width '0'::"numeric")
(((unframed_exhibit_restriction = 'WORLD'::"varchar") OR
(unframed_exhibit_restriction = 'INDIA'::"varchar")) OR (unframed_exhibit_restriction
= 'ONLY_WORLD'::"varchar"))
(((framed_exhibit_restriction = 'WORLD'::"varchar") OR
(framed_exhibit_restriction = 'INDIA'::"varchar")) OR (framed_exhibit_restriction =
'ONLY_WORLD'::"varchar"))
(((exhibit_options = 'FRAMED'::"varchar") OR (exhibit_options =
'UNFRAMED'::"varchar")) OR (exhibit_options = NULL::"varchar"))
Explain plan results in the following
arttoday= explain select site_section, count(*) from exhibit_distributions group by
site_section;
NOTICE: QUERY PLAN:
Aggregate (cost=69.83..74.83 rows=100 width=12)
- Group (cost=69.83..72.33 rows=1000 width=12)
- Sort (cost=69.83..69.83 rows=1000 width=12)
- Seq Scan on exhibit_distributions
(cost=0.00..20.00 rows=1000 width=12)
The problem still persists.. I will a drop and recreation of the db just
in case..
Thanks for the help
Anand
On Mon, Feb 12, 2001 at 11:04:55AM -0500, Tom Lane wrote:
Anand Raman [EMAIL PROTECTED] writes:
The second query is not priniting out site_section column.
Odd. What is the exact definition of table exhibit_distributions?
Does it have any indices? What plan is printed by EXPLAIN for the
problem query?
regards, tom lane
[GENERAL] strange query results
hi guys I am trying out some relativly simple queries against my database.. select distinct site_section as "distinct site sections" from exhibit_distributions ; distinct site sections ARCHIVED ARTETC CALENDAR GALLERY POSTCARD (5 rows) select site_section, count(*) from exhibit_distributions group by site_section; site_section | count --+--- | 352 |45 | 1 | 166 | 2 The second query is not priniting out site_section column.. This is happening in 3 seperate dbs (702 and 703).. Can any one point out the mistake. This is proving to be a show stopper .. We arent able to select rows for a particular site_section.. Thanks for your response Anand
[GENERAL] Strange query results with dates
Hi!
Help me please to resolve my problem.
I cann't understand why my query returns very strange results.
select datop from mytable where date_trunc('month',datop)
=date_trunc('month',abstime '22.05.00')
it returns a strings with operations which were made in May,
but similar query
select datop from mytable where date_trunc('month',datop)
=date_trunc('month',abstime '02.05.00')
it returns operations which were made in february !
I'm running PGSQL 7.0 ,
SET DATESTYLE ='GERMAN'
SET TIME ZONE 'EUROPE/MOSCOW'
Tell me please , what I made wrong?
Thanks for any suggestions.
Igor.
Re: [GENERAL] Strange query results with dates
Igor Khanjine [EMAIL PROTECTED] writes:
select datop from mytable where date_trunc('month',datop)
=date_trunc('month',abstime '02.05.00')
it returns operations which were made in february !
I'm running PGSQL 7.0 ,
SET DATESTYLE ='GERMAN'
SET TIME ZONE 'EUROPE/MOSCOW'
FWIW, I do not see this in 7.0.3-to-be:
play= SET DATESTYLE ='GERMAN';
SET VARIABLE
play= select abstime '02.05.00';
?column?
02.05.2000 00:00:00.00 EDT
(1 row)
play= select date_trunc('month',abstime '02.05.00');
date_trunc
01.05.2000 00:00:00.00 EDT
(1 row)
Either it's been fixed since 7.0 release, or there is something peculiar
about the datetime support on your platform (which you didn't specify).
regards, tom lane
