Postgres Unique Constraint on two columns: Integer and Boolean

Question

I would like to find a way to store multiple addresses for one subject in my database, with only one default address per subject.

To cut a long story short, lets say we have a table:

CREATE TABLE test
(
  id integer NOT NULL,
  active boolean NOT NULL,
  CONSTRAINT pk_id PRIMARY KEY (id)
)

For each id in the table, there must be at most 1 true active value.

How could I achieve this?

Thanks.

Answer 1

Partial index is what you want:

create unique index some_index on test (id) where active;

As @ThiefMaster states primary index should be removed.

Answer 2

In this Question’s case it's not needed as explained previously.

But FYI, you can set constraints to 2 or more (non-PrimaryKey) columns with different types. For example:

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (integer_column, boolean_column);

For more info, see the Postgres doc for Constraints, the "Unique Constraints" section. To quote:

If a unique constraint refers to a group of columns, the columns are listed separated by commas…

This specifies that the combination of values in the indicated columns is unique across the whole table, though any one of the columns need not be (and ordinarily isn't) unique.

Note: A partial index sets an index built over a subset of a table, which has a completely different purpose.