Unique constrain for values combination in Postgresql

Question

is it possible to create unique constrain in Postgresql, that would take into account column values and work with some value, but not with another?

I have table, where items have "code" that must be unique, but records aren't deleted from table, just marked as deleted (special boolean field "deleted"). I could create unique(code, deleted) to enforce uniqueness, it works if I don't mark 2 rows as deleted with same "code". If I would try to "delete" second row, it will alert that 2 rows will have same unique values and doesn't allow. What I would like to have, is check unique field "code" for every row, that "deleted" is false.

GMB · Accepted Answer · 2020-05-04 09:20:44Z

2

You can't do this with a consraint. However, in Postgres, a partial unique index would do exactly what you want:

create unique index myindex on mytable(code) where (deleted = false);

You can also phrase this as:

create unique index myindex on mytable(code) where (not deleted);

answered May 4, 2020 at 9:20

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

Unique constrain for values combination in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related