CODEWITHSUNDEEP
sqlpostgresql
EuberDeveloper
EuberDeveloper

Reputation: 1048

Postgresql - unique constraint allowing only ONE null

On PostgreSQL, I have a table with the columns name, surname and _deleted.

name and surname cannot be duplicated, so I had a unique constraint on them and everything was good.

after that, I added the _deleted for soft deletes. If I add (name, surname) and then I soft delete it, I cannot add anymore (name, surname).

I thought that the solution was adding unique to (name, surname, _deleted) but it does not work, because _deleted is a DateTime that is null when it is not deleted, hence NULL can be multiple.

How can I add a proper constraint?

Upvotes: 0

Views: 566

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269447

You want a filtered unique index:

create unique index unq_t_name_surname
    on t(name, surname)
    where _deleted is not null;

You cannot (easily) do this with a unique constraint, but this is functionally equivalent to a constraint.

Upvotes: 2

eshirvana
eshirvana

Reputation: 24568

you can make a conditional unique index :

CREATE UNIQUE INDEX names ON (name,lastname) WHERE (_deleted is NOT null);

Upvotes: 1

Related Questions