Postgres SQL unable to handle Null values for Text datatype
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.
--
Karthik klv
org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea
This has been discussed on Stack Overflow[0]https://stackoverflow.com/a/54223586.
The answer with the highest approval suggests to use coalesce[1]https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL:
```
Select * from A where middle_name = coalesce(?1)
```
Lutz
[0]: https://stackoverflow.com/a/54223586
[1]: https://www.postgresql.org/docs/current/functions-conditional.html#FUNCTIONS-COALESCE-NVL-IFNULL
On 9/6/22 02:10, Karthik K L V wrote:
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 nul
Oracle is actually in the wrong here. Nothing should be equal to null,
ever. There is also different behavior with unique indexes:
[mgogala@umajor ~]$ docker start psql14-5
psql14-5
[mgogala@umajor ~]$ psql -U scott
Password for user scott:
psql (14.5)
Type "help" for help.
scott=> create table test1 (key1 integer,key2 integer, data
varchar(10));
CREATE TABLE
scott=> alter table test1 add constraint test1_uq unique(key1,key2);
ALTER TABLE
scott=> insert into test1 values(1,null,'aaaaa');
INSERT 0 1
scott=> insert into test1 values(1,null,'bbbbb');
INSERT 0 1
scott=> select * from test1;
key1 | key2 | data
------+------+-------
1 | | aaaaa
1 | | bbbbb
(2 rows)
The same thing would not work with Oracle. However, please note that,
according to SQL standard, NULL is not equal to anything, to those 2
rows are actually not a unique constraint violation. To enforce the
uniqueness the same way as with Oracle, you actually need 2 indexes.
You need to use the "coalesce" function.
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
On Sep 5, 2022, at 23:10, Karthik K L V <venkata.karthik4u@gmail.com> wrote:
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
This is a Hibernate issue. If you use setParameter with a NULL without specifying the type, it assumes bytea (with the PostgreSQL JDBC driver, at least). You'll need to use the three-parameter form of setParameter() that specifies a type, if the value is going to be NULL. You can also use the setXXXX methods on SQLQuery, since the type is specified by the particular method there.
That being said, PostgreSQL's handling of NULL string values is different from Oracle's, and this is an area that code changes are often required.