Migrating from Oracle - Implicit Casting Issue

From: Karthik K L V <venkata(dot)karthik4u(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Migrating from Oracle - Implicit Casting Issue
Date: 2022-07-19 06:28:02
Message-ID: CAGpQzhy43WJarMv1L=f-yHzShQ1OYoTe+R1Hdzp==Eyg7A8sQw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Team,

We are migrating from Oracle 12c to Aurora Postgres 13 and running into
implicit casting issues.

Oracle is able to implicitly cast the bind value of prepared statements
executed from the application to appropriate type - String -> Number,
String -> Date, Number -> String etc. when there is a mismatch b/w java
data type and the column Datatype.

For example: If the Datatype of a Column is defined as Number and the
application sends the bind value as a String (with single quotes in the
query) - Oracle DB is able to implicitly cast to Number and execute the
query and return the results.

The same is not true with Postgres and we are getting below exception

*org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint
= character varying*
*Hint: No operator matches the given name and argument types. You might
need to add explicit type casts..*

We found a Postgres Driver property - stringtype=unspecified which appears
to solve this problem and have the following questions.
https://jdbc.postgresql.org/documentation/83/connect.html

Could you please let us know the following?

Q1) Will configuring this stringtype property introduce overhead on
Postgres leading to Performance issues
Q2)Does setting this attribute have any other implications on the data in
the DB.
Q3)Is there any plan to deprecate / stop supporting this attribute in
future Aurora Postgres releases.

--
Karthik klv

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-07-19 06:42:18 Migrating from Oracle - Implicit Casting Issue
Previous Message Meera Nair 2022-07-19 04:37:09 pg_dump is filling C: drive up to 100 percent