How to add a conditional unique index on PostgreSQ

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

Answer 2

Another option is to use expressions in your key fields. This may not have been around when you asked the question, but could be helpful for others that come across this now.

CREATE UNIQUE INDEX line_items_prod_id_var_id_idx
ON line_items ( product_id, (coalesce(variant_id, 0)) );

Granted, this assumes that your variant_id is an auto-incrementing integer that started at 1. Also note the parentheses around the expression. Per the docs, they are required.

http://www.postgresql.org/docs/9.3/static/sql-createindex.html