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

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(at)umajor ~]$ docker start psql14-5
psql14-5
[mgogala(at)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