How to allow NULL value for one column in unique index on multiple columns

Question

Seems like a pretty straightforward question, but I can't seem to locate the specific answer anywhere online.

I have a price model that references both quote and a line item, thus it has quote_id and line_item_id columns. I am using a multicolumn index with unique: true to prevent multiple prices for the same line_item from being attached to a quote. quote_id is the first column in the index.

However, I want to allow users to add prices to line_items that haven't been quoted. It works fine for one price, quote_id is null and line_item_id is present. However, the unique index is preventing me from attaching a second price to the same line_item. The null value in quote_id is being treated as unique.

Is there any way to make the unique constraint only apply when the quote is not null?
I know that allow_nil can be used in model validation, but can it be used in the index?

I am thinking something like:

add_index :prices, [:quote_id, :line_item_id], :unique => true, :allow_nil => true

PostgreSQL and Rails 4.

Answer 1

Is there any way to make the unique constraint only apply when the quote is not null?

Actually, this is the only way. You can have multiple "identical" entries with one or more of the columns in a multicolumn index being NULL, because Postgres does not consider two NULL values identical for this purpose (like in most contexts).

The sequence of columns in the index doesn't matter for this. (It matters for other purposes, though.)

Make sure the underlying columns in the table itself can be NULL. (Sometimes confused with the empty string ''.)

After re-reading your question, the more appropriate scenario seems to be this related answer:
Create a multicolumn index to enforce uniqueness
This one may be of help, too, but the question asks for the opposite of yours:
How to add a conditional unique index on PostgreSQL

If you actually want a partial index with only non-null value, you can do that too:

CREATE INDEX price_uni_idx ON price (quote_id, line_item_id)
WHERE  quote_id     IS NOT NULL
AND    line_item_id IS NOT NULL;

You do not need that for your purpose, though. It may still be useful to exclude rows with NULL values from the index to make it faster. Details here:
Indexed ORDER BY with LIMIT 1