Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres SQL unable to handle Null values for Text datatype

Postgres SQL unable to handle Null values for Text datatype

From
Karthik K L V
Date:
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

Re: Postgres SQL unable to handle Null values for Text datatype

From
Lutz Horn
Date:
> org.postgresql.util.PSQLException: ERROR: operator does not exist: character varying = bytea

This has been discussed on Stack Overflow[0].

The answer with the highest approval suggests to use coalesce[1]:

```
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

Re: Postgres SQL unable to handle Null values for Text datatype

From
Mladen Gogala
Date:
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

Re: Postgres SQL unable to handle Null values for Text datatype

From
Christophe Pettus
Date:

> 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
PostgreSQLJDBC driver, at least).  You'll need to use the three-parameter form of setParameter() that specifies a type,
ifthe value is going to be NULL.  You can also use the setXXXX methods on SQLQuery, since the type is specified by the
particularmethod there. 

That being said, PostgreSQL's handling of NULL string values is different from Oracle's, and this is an area that code
changesare often required.