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
