I have an existing table which I would like to alter in order for it to be such that col1 and col2 has to be unique if col3 has a non-null value.
So in the following example, the last row should fail to be inserted because the 4th row has the same col1 and col2 values with a non-null col3 value.
| col1 | col2 | col3 |
|---|---|---|
| matt | 234 | |
| matt | 234 | |
| matt | 237 | banana |
| matt | 234 | apple |
| matt | 234 | |
| matt | 234 | pear |
So how do I alter my table for it to account for col3's non-null values? This is all that I have for now.
ALTER TABLE someTable
ADD UNIQUE (col1, col2)
CodePudding user response：
PostgreSQL didn't UNIQUE conditional constraint, however you can try to use Partial Indexes and add UNIQUE
CREATE UNIQUE INDEX someTable_unique_col1_col2 ON someTable(col1, col2)
WHERE col3 IS NOT NULL