Postgres Unique Constraint on two columns: Integer and Boolean
Partial index is what you want:
create unique index some_index on test (id) where active;As @ThiefMaster states primary index should be removed.
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.