public inbox for [email protected]
help / color / mirror / Atom feedFrom: Wal, Jan Tjalling van der <[email protected]>
To: Karl Aschbacher <[email protected]>
To: [email protected] <[email protected]>
Subject: RE: Value changed from Integer to Double
Date: Tue, 12 Mar 2024 08:24:22 +0000
Message-ID: <AM0PR01MB56341BE6FB1AFBA080BDFC0FDD2B2@AM0PR01MB5634.eurprd01.prod.exchangelabs.com> (raw)
In-Reply-To: <CAADAuhBevN0+8fzUpg7j9R6H+dvis0E0nYg2MzPQooEkDyfx_g@mail.gmail.com>
References: <CAADAuhBevN0+8fzUpg7j9R6H+dvis0E0nYg2MzPQooEkDyfx_g@mail.gmail.com>
Hello Karl,
My hunch is that Oracle and Postgres (ODBC) are disagreeing about what is an integer.
At the range (length) of data that you are talking about, for postgres this is beyond ‘integer’ (4 bytes, 32 bits signed).
PostgreSQL: Documentation: 16: 8.1. Numeric Types<https://www.postgresql.org/docs/current/datatype-numeric.html;
At the postgres-side the data is then likely specified as a bigint (8 bytes, 64 bits signed).
Alternatively these could be serial and bigserial.
A similar link for Oracle on data types indicates that INTEGER/INT/SMALLINT are Oracle Data Type NUMBER(38).
Or an even higher value than 38 in this case.
So both systems are not in agreement on what data type this size of number is.
My suggestion would be to specify better as part of the query going into Oracle Analytics Server (OAS) what you are requesting.
For instance by adding a type hint (cast).
That should make clear to OAS that the question is for a -longer than usual- integer and hopefully have it not switch to a float.
Kind regards, Jan Tjalling van der Wal
Wageningen Marine Reseach (WMR) / formerly IMARES Institute for Marine Resources & Ecosystem Studies
Ankerpark 27, 1781 AG Den Helder Postbus 57, 1780 AB Den Helder
Tel. +31 (0)317-4 87147 # GSM. +31 (0)626120915 (privé) #
# Ma+Di Vr 09:00-18:00, Wo XX, Do+Vr 09:00-18:00
[email protected]<mailto:[email protected]>
From: Karl Aschbacher <[email protected]>
Sent: Monday, March 11, 2024 10:21 AM
To: [email protected]
Subject: Value changed from Integer to Double
Dear all,
we are facing a problem that is maybe related to the postgres-odbc driver.
We use Oracle Analytics Server via ODBC to talk to a postgres Database (V15)
It appears that integers with more than 10 digits get converted to doubles when the statment is being sent to the database.
So for example: "where id = 32857641330" will be converted to "where did = 3285764133.0 "
So my question is - does the driver play any role when preparing the statement?
(Like providing the PG Type ?)
In case it does, would anyone have a suggestion where to look for the potential issue?
many thanks
best regards
Karl
view thread (2+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: RE: Value changed from Integer to Double
In-Reply-To: <AM0PR01MB56341BE6FB1AFBA080BDFC0FDD2B2@AM0PR01MB5634.eurprd01.prod.exchangelabs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox