Postgres Pro
Downloads
Home   >   mailing lists

Thread: Value changed from Integer to Double

Value changed from Integer to Double

From
Karl Aschbacher
Date:
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

RE: Value changed from Integer to Double

From
"Wal, Jan Tjalling van der"
Date:

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

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

Jan_Tjalling.vanderWal@wur.nl

From: Karl Aschbacher <krlschbchr@gmail.com>
Sent: Monday, March 11, 2024 10:21 AM
To: pgsql-odbc@postgresql.org
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