CODEWITHSUNDEEP
sqlpostgresql
Prajwal
Prajwal

Reputation: 163

INSERT into PostgreSQL table with unique columns and non-null

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)

Upvotes: 1

Views: 61

Answers (1)

D-Shih
D-Shih

Reputation: 46239

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

sqfiddle

Upvotes: 3

Related Questions