How to add a conditional unique index in PostgreSQL

Question

I have a line_items table with the following columns:

 product_id variant_id 

variant_id is null.

Here is the condition:

• If variant_id is NULL, then product_id must be unique.
• If variant_id matters, then the combination of product_id and variant_id must be unique.

Is this possible in PostgreSQL?

Answer 1

Create a UNIQUE multi- (product_id, variant_id) INDEX in (product_id, variant_id) :

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

However, this will allow the use of multiple elements (1, NULL) for (product_id, variant_id) , because NULL values ​​are not considered identical.
To compensate for this, additionally create a partial UNIQUE INDEX on product_id :

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

So you can enter (1,2) , (1,3) and (1, NULL) , but not one of them can be a second time. Also speeds up queries with conditions on one or both columns.

This answer to dba.SE I recently wrote is very similar and almost directly applicable to your problem.

Answer 2

Another option is to use expressions in your key fields. Perhaps this was not the case when you asked a question, but could be useful to others who are facing this now.

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

It is assumed that your variant_id is an automatically incrementing integer starting with 1. Also note the parentheses around the expression. In the documents they are required.

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