null - How to add a conditional unique index on PostgreSQL

Question

I have a line_items table with following columns:

product_id
variant_id

variant_id is nullable.

Here is the condition:

• If variant_id is NULL then product_id should be unique.
• If variant_id has a value then combination of product_id and variant_id should be unique.

Is that possible in PostgreSQL?

Answer 1

Create a UNIQUE multicolumn index on (product_id, variant_id):

CREATE UNIQUE INDEX line_items_prod_var_idx ON line_items (product_id, variant_id);

However, this would allow multiple entries of (1, NULL) for (product_id, variant_id) because NULL values are not considered identical.
To make up for that, additionally create a partial UNIQUE index on product_id:

CREATE UNIQUE INDEX line_items_prod_var_null_idx ON line_items (product_id)
WHERE variant_id IS NULL;

This way you can enter (1,2), (1,3) and (1, NULL), but neither of them a second time. Also speeds up queries with conditions on one or both column.

Recent, related answer on dba.SE, almost directly applicable to your case:

• PostgreSQL multi-column unique constraint and NULL values