How to add a conditional unique index on PostgreSQL
indexing, null, postgresql
Solution
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
Problem
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?