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

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