Thread: BUG #2496: Troubles with fixed-point types (NUMERIC, DECIMAL,...)
BUG #2496: Troubles with fixed-point types (NUMERIC, DECIMAL,...)
From
"Heinz-Peter Heidinger"
Date:
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;
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 > ----------------------------------------------------------------- >