I am creating a table in PostgreSQL v14.9 with a unique constraint on two columns: A and B where B can be null. As PostgreSQL does not consider the absence of values as equal (NULL) it will allow rows with the same A values and B set to null.

Row: (“mydata”, null)
Row: (“mydata”, null)

this should be caught by the constraint, and can be achieved by using partial index instead by creating two indices. One like a regular index for whenever B has a value, and another using a where-clause that checks if B is null.

If I use those two indices it is working as expected. The forementioned rows would not be allowed into the database, however I am unable to update rows without breaking the constraint as PostgreSQL will evaluate after each update before the commit even if using Transactional. The workaround for this is to set the deferrable initially deferred mode when defining unique constraints. This does not work for indices though, and unique constraints does not allow for using a where-clause.

I am using Spring Data JPA with Hibernate against a PostgreSQL database, so I am unable to write PostgreSQL specific SQL queries, which means I have to make my initial migration script setup everything correct.

This is my database table definition:
But this will allow null values for ‘deleted’.

create table myTable
(
    some_key uuid         not null,
    name     VARCHAR(255) not null,
    deleted  TIMESTAMP,
    primary key (some_key),
    constraint myConstraint unique (name, deleted)
);

So to fix null values I would instead remove my unique constraint, and create two indices like so.
However, if I have two rows like so.
Row1: (“mydata”, null)
Row2: (“mydata”, someDate)
and I wish to switch them around, so that Row1 becomes deleted and Row2 becomes null, I will get a constraint violation as I am doing my update. As mentioned, PostgreSQL will check after each update and not when the commit is performed.

create table myTable
(
    some_key uuid         not null,
    name     VARCHAR(255) not null,
    deleted  TIMESTAMP,
    primary key (some_key)
);

create unique index idx_one ON myTable (name, deleted);
create unique index idx_two ON myTable (name) where deleted is null;

To alter when the constraint is checked I can use the deferrable initially deferred mode, but the below constraint will not consider null values as equal.

create table myTable
(
    some_key uuid         not null,
    name     VARCHAR(255) not null,
    deleted  TIMESTAMP,
    primary key (some_key),
    constraint myConstraint unique (name, deleted) deferrable initially deferred
);

and using a where-clause in my constraint like below is bad syntax and not possible.

create table myTable
(
    some_key uuid         not null,
    name     VARCHAR(255) not null,
    deleted  TIMESTAMP,
    primary key (some_key),
    constraint myConstraint unique (name, deleted) deferrable initially deferred,
    constraint myConstraint unique (name) where deleted is null
);

So what I am asking for is a UNIQUE CONSTRAINT across two columns, where one column might be null which should be considered equal and give me the possibility to update multiple rows and only check the constraint once the entire update has completed. Transactional does not ensure this in PostgreSQL, and I am using v14.9 so as side note I can not use DISTINCT NOT NULL to get around partial indices as this was only introduced in v15+.

Any help is appreciated.