[BUGS] BUG #2496: Troubles with fixed-point types (NUMERIC, DECIMAL,...)

#1

The following bug has been logged online:

Bug reference: 2496
Logged by: Heinz-Peter Heidinger
Email address: hph (AT) comserve-it-services (DOT) de
PostgreSQL version: 8.x
Operating system: all (Linux & Win was tested)
Description: Troubles with fixed-point types (NUMERIC, DECIMAL,...)
Details:

OpenOffice 1.x/2.x & StarOffice7/..8 cannot write fixed-point values
(NUMERIC|DECIMAL) to a PGSQL database when using a JDBC interface and a
desktop locale of 'german' (maybe others as well) is used.

The resulting message is:

ERROR: column <XXX> has type numeric but
expression has type character varying.

Testbed:
--------
Platforms: Linux & WinXP
DBdriver: JDBC3
DBMS: PostgreSQL V8.x (8.0.3)
OO: OOo V2.0 DE, StarOffice7 & 8

Table definition by DDL:
CREATE TABLE test
(
id serial NOT NULL,
price numeric(5,2),
payed bool,
CONSTRAINT test_pkey PRIMARY KEY (id)
)
WITH OIDS;

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

#2

Can you set "log_statement" in postgresql.conf and post the logged
statements?

--
With regards,
William ZHANG

----- Original Message -----
From: ""Heinz-Peter Heidinger"" <hph (AT) comserve-it-services (DOT) de>
Newsgroups: pgsql.bugs
Sent: Saturday, June 24, 2006 3:44 PM
Subject: BUG #2496: Troubles with fixed-point types (NUMERIC, DECIMAL,...)

Quote:

The following bug has been logged online:

Bug reference: 2496
Logged by: Heinz-Peter Heidinger
Email address: hph (AT) comserve-it-services (DOT) de
PostgreSQL version: 8.x
Operating system: all (Linux & Win was tested)
Description: Troubles with fixed-point types (NUMERIC, DECIMAL,...)
Details:

OpenOffice 1.x/2.x & StarOffice7/..8 cannot write fixed-point values
(NUMERIC|DECIMAL) to a PGSQL database when using a JDBC interface and a
desktop locale of 'german' (maybe others as well) is used.

The resulting message is:

ERROR: column <XXX> has type numeric but
expression has type character varying.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

#3

The logged statement is:

INSERT INTO "public"."junk" ( "preis" ) VALUES ( $1 )

If you send it via psql, pgsql will raise error:

ERROR: there is no parameter $1

I don't know why you got the error message saying "type mismatch".
But sending the following statement via psql will cause the similar error
you got:

PREPARE foo (VARCHAR) AS INSERT INTO "junk" ( "preis" ) VALUES ( $1 )

Can you change the parameter "log_error_verbosity" to "verbose", and
send me the log file?

Regards,
William ZHANG

----- Original Message -----
From: "ComServe IT-Services" <hph (AT) ComServe-IT-Services (DOT) de>
To: "William ZHANG" <uniware (AT) zedware (DOT) org>
Sent: Monday, July 10, 2006 10:25 PM
Subject: Re: BUG #2496: Troubles with fixed-point types
(NUMERIC,DECIMAL,...)

Quote:

Hello William,

Am Di, den 27.06.2006 schrieb William ZHANG um 17:01:
Can you set "log_statement" in postgresql.conf and post the logged
statements?

Sure. See the appended screen-shot "Whole-screen.png"

Also appended is the postgresql.conf

The quirk happens with fixed-pointcoloums (DECIMAL, NUMERIC, MONEY) and
BOOLEAN coloums as well when using the JDBC abstraction layer in locale
'German' (and maybe all others than 'C' or 'en_US' as well)

Table definition:

comserve=> \c junk
Sie sind jetzt mit der Datenbank »junk« verbunden.
junk=> \d
Liste der Relationen
Schema | Name | Typ | Eigentümer
--------+-------------+---------+------------
public | Tabelle1 | Tabelle | comserve
public | junk | Tabelle | comserve
public | junk_id_seq | Sequenz | comserve
(3 Zeilen)

junk=> \d junk
Tabelle »public.junk«
Spalte | Typ | Attribute
--------+--------------+------------------------------------------------------
id | integer | not null default
nextval('public.junk_id_seq'::text)
preis | numeric(5,2) |
Indexe:
»junk_pkey« PRIMARY KEY, btree (id)

junk=

If you need further assistance: just let me know!
I hope we can cut down this bad finger, since an Office-System that
cannot store business figures to a DB doesn't make much sense - at least
with that specific DBMS.

Thanks for your concern anyway.

With regards,
William ZHANG

Best regards,
Peter Heidinger
--
-----------------------------------------------------------------
ComServe - IT-Services
Think IT, Plan IT, Do IT!

Steeler Str. 121
45138 Essen
-------------------------------------------------------------
Professionelle Unix- & Linux-Lösungen
-------------------------------------------------------------
* Unix-Systemtechnik * Linux-Unternehmenslösungen
(AIX, HPUX, Solaris, Linux) * Linux-Server & -Cluster
* Hochverfügbarkeitssysteme * Linux-Virtual-Server
* Proaktive Security-Konzepte
* Enterprise-Backup-Konzepte
mit Tivoli-Storage-Manager
-----------------------------------------------------------------
Telefon : +49 (201) 319-9875
Fax : +49 (201) 319-9876
Mobil : 0160/6111-819
http://ComServe-IT-Services.de
-----------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

[BUGS] BUG #2496: Troubles with fixed-point types (NUMERIC, DECIMAL,...)

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

[BUGS] BUG #2496: Troubles with fixed-point types (NUMERIC, DECIMAL,...) - 06-25-2006 , 07:42 AM

Re: [BUGS] BUG #2496: Troubles with fixed-point types (NUMERIC, DECIMAL,...) - 06-28-2006 , 07:02 AM

Re: [BUGS] BUG #2496: Troubles with fixed-point types (NUMERIC, DECIMAL,...) - 07-11-2006 , 09:30 AM