Unique constraint with null columns (Hibernate, PostgreSQL)

Question

I have class like Clazz

    @Table(
    name="tablename", 
    uniqueConstraints=
        @UniqueConstraint(
                          name= "uniqueColumn_deleted_uk",
                          columnNames={"myuniquecolumn", "deleted"}
                         )

    )
 public class Clazz {

     @Column(name = "deleted")
     private LocalDateTime deleted;
}

deleted is nullable, PosgreSQL creates unique index like

CREATE UNIQUE INDEX uniqueColumn_date_uk ON public.tablename (short_code_3, deleted);

and it allows insert duplicate myuniquecolumn when deleted is NULL.

How prevent this?

I want have non duplicates when deleted is null.

score 2 · Answer 1 · answered Dec 02 '16 at 13:34

2

You should create two partial unique indexes

create unique index on public.tablename (short_code_3, deleted) where deleted is not null;
create unique index on public.tablename (short_code_3) where deleted is null;

(I don't know how to do it in your ORM).

answered Dec 02 '16 at 13:34

klin

112,967
15
204
232

I came-up with a trigger function, but this works better. – coladict Dec 02 '16 at 14:24

score 0 · Answer 2 · answered Dec 02 '16 at 13:22

0

This is not possible because null is never = null.

Read more about null values in SQL https://en.wikipedia.org/wiki/Null_(SQL)

If you want to have the deleted column in the unique index you must provide a default value for that column.

answered Dec 02 '16 at 13:22

Simon Martinelli

34,053
5
48
82

score 0 · Answer 3 · answered Jun 05 '22 at 23:48

Tow partial indexes like klin provided are best practice up to Postgres 14.

Postgres 15 adds NULLS NOT DISTINCT for this purpose:

CREATE UNIQUE INDEX foo_idx ON public.tbl (short_code_3, deleted) NULLS NOT DISTINCT;

See:

Create unique constraint with null columns

Unique constraint with null columns (Hibernate, PostgreSQL)

3 Answers3