Re: Unique constraint blues

On Tue, Jan 18, 2022 at 10:13 AM Mladen Gogala <gogala(dot)mladen(at)gmail(dot)com>
wrote:

>
> mgogala=# create unique index test1_uq on test1(col1,coalesce(col2,'***
> EMPTY ***'));
>
> -> Bitmap Index Scan on test1_uq (cost=0.00..1.70 rows=6 width=0)

.......
> Index Cond: (test1.col1 = 1)
>
> How come that the index is used for search without the "coalesce"
> function?

Only the second column is an expression. The first (leading) column is
perfectly usable all by itself. It is less efficient, hence the parent
node's:

Recheck Cond: (test1.col1 = 1)
Filter: ((test1.col2)::text = 'test1'::text)

but usable.

If you are willing to create partial unique indexes you probably should
just create two of them. One where col2 is null and one where it isn't.

If the coalesce version is acceptable you should consider declaring the
column not null and put the sentinel value directly into the record.

David J.