The following bug has been logged online:

Bug reference: 2496
Logged by: Heinz-Peter Heidinger
Email address: hph@comserve-it-services.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

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

--
With regards,
William ZHANG

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


>
> The following bug has been logged online:
>
> Bug reference: 2496
> Logged by: Heinz-Peter Heidinger
> Email address: hph@comserve-it-services.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.

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@ComServe-IT-Services.de>
To: "William ZHANG" <uniware@zedware.org>
Sent: Monday, July 10, 2006 10:25 PM
Subject: Re: BUG #2496: Troubles with fixed-point types
(NUMERIC,DECIMAL,...)


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