Unique constraint blues

Postgres version is 13.5, platform is Oracle Linux 8.5, x86_64. Here is 
the problem:

mgogala=# create table test1(col1 integer,col2 varchar(10));
CREATE TABLE
mgogala=# alter table test1 add constraint test1_uq unique(col1,col2);
ALTER TABLE
mgogala=# insert into test1 values(1,null);
INSERT 0 1
mgogala=# insert into test1 values(1,null);
INSERT 0 1
mgogala=# select * from test1;
  col1 | col2
------+------
     1 |
     1 |
(2 rows)

So, my unique constraint doesn't work if one of the columns is null. 
Bruce Momjian to the rescue: 
https://blog.toadworld.com/2017/07/12/allowing-only-one-null

Let's see what happens:

mgogala=# truncate table test1;
TRUNCATE TABLE
mgogala=# alter table test1 drop constraint test1_uq;
ALTER TABLE
mgogala=# create unique index test1_uq on test1(col1,(col2 is null)) 
where col2 is null;
CREATE INDEX
mgogala=# insert into test1 values(1,null);
INSERT 0 1
mgogala=# insert into test1 values(1,null);
ERROR:  duplicate key value violates unique constraint "test1_uq"
DETAIL:  Key (col1, (col2 IS NULL))=(1, t) already exists.


So, this allows only a single NULL value, just what I wanted. However, 
there is a minor issue: this doesn't work for the general case:

mgogala=# insert into test1 values(1,'test1');
INSERT 0 1
mgogala=# insert into test1 values(1,'test1');
INSERT 0 1
mgogala=# select * from test1;
  col1 | col2
------+-------
     1 |
     1 | test1
     1 | test1
(3 rows)

I can insert the same row twice, which defeats the purpose. So, let's 
make the 3d modification:

mgogala=# truncate table test1;
TRUNCATE TABLE
mgogala=# drop index test1_uq;
DROP INDEX
mgogala=# create unique index test1_uq on test1(col1,coalesce(col2,'*** 
EMPTY ***'));

Using "coalesce" enforces the constraint just the way I need:

mgogala=# insert into test1 values(1,null);
INSERT 0 1
mgogala=# insert into test1 values(1,null);
ERROR:  duplicate key value violates unique constraint "test1_uq"
DETAIL:  Key (col1, COALESCE(col2, '*** EMPTY ***'::character 
varying))=(1, *** EMPTY ***) already exists.
mgogala=# insert into test1 values(1,'test1');
INSERT 0 1
mgogala=# insert into test1 values(1,'test1');
ERROR:  duplicate key value violates unique constraint "test1_uq"
DETAIL:  Key (col1, COALESCE(col2, '*** EMPTY ***'::character 
varying))=(1, test1) already exists.
mgogala=#

Now comes the greatest mystery of them all:

explain (analyze,verbose) select * from test1 where col1=1 and col2='test1';
                                                    QUERY PLAN

--------------------------------------------------------------------------------
---------------------------------
  Bitmap Heap Scan on mgogala.test1  (cost=1.70..7.52 rows=1 width=42) 
(actual ti
me=0.023..0.024 rows=1 loops=1)
    Output: col1, col2
    Recheck Cond: (test1.col1 = 1)
    Filter: ((test1.col2)::text = 'test1'::text)
    Rows Removed by Filter: 1
    Heap Blocks: exact=1
    ->  Bitmap Index Scan on test1_uq  (cost=0.00..1.70 rows=6 width=0) 
(actual t
ime=0.015..0.016 rows=2 loops=1)
          Index Cond: (test1.col1 = 1)
  Planning Time: 1.184 ms
  Execution Time: 0.407 ms
(10 rows)

How come that the index is used for search without the "coalesce" 
function? The unique index is a function based index and, in theory, it 
shouldn't be usable for searches without the function. I don't 
understand why is this working. I am porting application from Oracle to 
Postgres and Oracle behaves like this:

SQLcl: Release 21.3 Production on Tue Jan 18 11:39:43 2022

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.13.0.0.0

Elapsed: 00:00:00.001
SQL> create table test1(col1 integer,col2 varchar2(10));

Table TEST1 created.

Elapsed: 00:00:00.050

SQL> alter table test1 add constraint test1_uq unique(col1,col2);

Table TEST1 altered.

Elapsed: 00:00:00.139
SQL> insert into test1 values(1,null);

1 row inserted.

Elapsed: 00:00:00.026
SQL> insert into test1 values(1,null);

Error starting at line : 1 in command -
insert into test1 values(1,null)
Error report -
ORA-00001: unique constraint (SCOTT.TEST1_UQ) violated

Elapsed: 00:00:00.033

Oracle is rejecting the same row twice, regardless of whether it 
contains NULL values or not. As in  Postgres, the resulting index can be 
used for searches. However, Oracle index is not a function-based index 
because it doesn't contain the coalesce function.


-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com