Unique constraint blues - Mailing list pgsql-performance
From | Mladen Gogala |
---|---|
Subject | Unique constraint blues |
Date | |
Msg-id | b42e17e9-3958-891c-ebda-20d02b5bc28b@gmail.com Whole thread Raw |
Responses |
Re: Unique constraint blues
("David G. Johnston" <david.g.johnston@gmail.com>)
|
List | pgsql-performance |
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
pgsql-performance by date: