In Postgresql, add unique constraint on 2 columns, not equal to a specific value

Question

Have a table named People, column address_id (int) and is_deleted (boolean), how can I add a unique constraint to allow unique address_id + false, and allow multiple address_id + true.

e.g.

address_id | is_deleted
-----------------------
1          | false
2          | true
2          | true
2          | true

thanks!

Answer 1

how can I add a unique constraint to allow unique address_id + false, and allow multiple address_id + true

Can't use a CHECK as that would not work in case of concurrent insertion or updates.

You can use a conditional unique index:

CREATE UNIQUE INDEX people_addr_id_is_deleted_unique 
ON People (address_id) WHERE (NOT is_deleted);

This unique index will only contain rows satisfying the specified WHERE condition. Depending on the percentage of matching rows, this may save a bit of storage space, or a lot relative to a full index. Also, updates on a row that is not included in a conditional index do not have to spend time updating the index.

Further reading. Also google "postgresql conditional unique constraint".