Hi Team,
We are migrating from Oracle 12C to Aurora Postgres 13 and running into query failures when the bind value of a Text datatype resolves to null.
The same query works fine in Oracle without any issues. We use SpringDataJPA and Hibernate framework to connect and execute queries and the application uses native queries.
Here is an example query:
Select * from A where middle_name=?1
The above query fails with the below exception when the value of ?1 resolves to null.
org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea
Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
Position: 64
We debugged through the Hibernate code comparing Oracle vs Postgres for the same query to understand if the framework was doing anything different when switched to Postgres and didn't notice any difference in the behaviour.
We have also set transform_null_equals to ON in Postgres..but this doesn't help.
Could you please let us know if there are any other configurations that need to be set in Postgres to make it work similar to Oracle?
This issue is impacting multiple modules in our application and any help will be appreciated.