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
