Re: Migrating from Oracle - Implicit Casting Issue
Hi David, Thanks for the quick response. Making sure I got it right - U mean Postgres DB Server when you say server right? IIUC, by configuring this property, the driver will not set the type and leave it to the Postgres DB Server to map it to the appropriate type. Will this have any performance implication on the Postgres server that we need to worry about? Thanks, Karthik K L V On Tue, Jul 19, 2022 at 12:12 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Monday, July 18, 2022, Karthik K L V > wrote: > >> 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. >> >> > That setting is not recognized by the server in any way, it is a driver > concern only. IIUC it makes the Java Driver behave in a way consistent > with the expectations of the server since by leaving the supplied type info > undeclared the server can use its own logic. If it works for you I say use > it, it will be less problematic than methodically fixing your queries up > front. Though if there are some that show to be bottlenecks getting the > type info correct may prove to make a difference in some situations. > > David J. > > -- Karthik klv
Migrating from Oracle - Implicit Casting Issue
On Monday, July 18, 2022, Karthik K L V wrote: > 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. > > That setting is not recognized by the server in any way, it is a driver concern only. IIUC it makes the Java Driver behave in a way consistent with the expectations of the server since by leaving the supplied type info undeclared the server can use its own logic. If it works for you I say use it, it will be less problematic than methodically fixing your queries up front. Though if there are some that show to be bottlenecks getting the type info correct may prove to make a difference in some situations. David J.
Migrating from Oracle - Implicit Casting Issue
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
