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?

Pliers

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 allows multiple entries of (1, null) for (product_id, variant_id) by default, because null values are not considered to be distinct values.
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.

Or use the NULLS NOT DISTINCT clause in Postgres 15 or later. See:

• Create unique constraint with null columns
• PostgreSQL multi-column unique constraint and NULL values

Evanston

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

Roster

Answer 3

Following should work as well -

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

More about coalesce - https://www.postgresql.org/docs/8.1/functions-conditional.html

Sememe