Postgresql - unique constraint allowing only ONE null

Question

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?

Gordon Linoff · Accepted Answer · 2021-03-02 19:16:01Z

2

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.

edited Mar 2, 2021 at 19:16

answered Mar 2, 2021 at 16:21

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

EuberDeveloper Over a year ago

Does it change the update performance? I do not know how the constraints are checked, but adding an index would change the update performance and occupies size on disk. However it works, than you!

Gordon Linoff Over a year ago

@EuberDeveloper . . . The performance is pretty much the same. Unique constraints are implemented using unique indexes. The filtering on the index has little impact on performance.

EuberDeveloper Over a year ago

Using a unique constraint is as adding an index but without having the improved searches? Thank you for your answer!

eshirvana · Accepted Answer · 2021-03-02 16:22:13Z

1

you can make a conditional unique index :

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

answered Mar 2, 2021 at 16:22

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

Postgresql - unique constraint allowing only ONE null

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related