Postgres 15 or newer
Postgres 15 adds the clause NULLS NOT DISTINCT. The release notes:
Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut)
Previously NULL values were always indexed as distinct values, but this can now be changed by creating constraints and indexes using
UNIQUE NULLS NOT DISTINCT.
With this clause NULLnull is treated like just another value, and a UNIQUE constraint does not allow more than one row with the same NULLnull value. The task is simple now:
ALTER TABLE favorites ADD CONSTRAINT favo_uni UNIQUE NULLS NOT DISTINCT (user_id, menu_id, recipe_id); There are examples in the manual chapter "Unique Constraints".
The clause switches behavior for all keys of the same index. You can't treat NULLnull as equal for one key, but not for another.
NULLS DISTINCT remains the default (in line with standard SQL) and does not have to be spelled out.
The same clause works for a UNIQUE index, too:
CREATE UNIQUE INDEX favo_uni_idx ON favorites (user_id, menu_id, recipe_id) NULLS NOT DISTINCT; Note the position of the new clause after the key fields.
Postgres 14 or older
Create two partial indexes:
CREATE UNIQUE INDEX favo_3col_uni_idx ON favorites (user_id, menu_id, recipe_id) WHERE menu_id IS NOT NULL; CREATE UNIQUE INDEX favo_2col_uni_idx ON favorites (user_id, recipe_id) WHERE menu_id IS NULL; This way, there can only be one combination of (user_id, recipe_id) where menu_id IS NULL, effectively implementing the desired constraint.
Possible drawbacks:
- You cannot have a foreign key referencing
(user_id, menu_id, recipe_id). (It seems unlikely you'd want a FK reference three columns wide - use the PK column instead!) - You cannot base
CLUSTERon a partial index. - Queries without a matching
WHEREcondition cannot use the partial index.
If you need a complete index, you can alternatively drop the WHERE condition from favo_3col_uni_idx and your requirements are still enforced.
The index, now comprising the whole table, overlaps with the other one and gets bigger. Depending on typical queries and the percentage of NULLnull values, this may or may not be useful. In extreme situations it may even help to maintain all three indexes (the two partial ones and a total on top).
This is a good solution for a single nullable column, maybe for two. But it gets out of hands quickly for more as you need a separate partial index for every combination of nullable columns, so the number grows binomially. For multiple nullable columns, see instead:
Aside: I advise not to use mixed case identifiers in PostgreSQL.