Multicolumn index where one is nullable

Asked Jan 05 '22 at 11:06

Active Jan 05 '22 at 11:06

Viewed 42 times

How can I make a multicolumn index for two columns where one of them can be null?

I only want the index to validate that there are no duplicates when both columns have value (when the nullable column is not null). When the nullable column is null I don't want the validation.

Is this possible?

asked Jan 05 '22 at 11:06

Brainmaniac

2,203
4
29
53

Yes, CREATE UNIQUE INDEX u_name_of_index ON table_name (col1, col2); – Frank Heikens Jan 05 '22 at 11:08
Did you try [this solution](https://stackoverflow.com/questions/14221775/in-postgresql-force-unique-on-combination-of-two-columns) ? – Benoit Jan 05 '22 at 11:15
@FrankHeikens :D this worked! I didn't think it would be that easy. Thank you! If you want to add it as an answer I will mark it as correct. – Brainmaniac Jan 05 '22 at 11:37
@Benoit thanks, did not since Frank's answer worked – Brainmaniac Jan 05 '22 at 11:38

Multicolumn index where one is nullable

0 Answers0