How to add a conditional unique index on PostgreSQL

by

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 would allow multiple entries of (1, NULL) for (product_id, variant_id) because NULL values are not considered identical.
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.

Recent, related answer on dba.SE, almost directly applicable to your case:

More Related Contents:

  1. Do nullable columns occupy additional space in PostgreSQL?
  2. PostgreSQL LIKE query performance variations
  3. What to do with null values when modeling and normalizing?
  4. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  5. Why does PostgreSQL perform sequential scan on indexed column?
  6. Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
  7. Does a Postgres UNIQUE constraint imply an index?
  8. Difference between GiST and GIN index
  9. Test for null in function with varying parameters
  10. Postgres query optimization (forcing an index scan)
  11. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  12. Optimize Postgres query on timestamp range
  13. Multicolumn index on 3 fields with heterogenous data types
  14. Composite PRIMARY KEY enforces NOT NULL constraints on involved columns
  15. PostgreSQL composite primary key
  16. How does the search_path influence identifier resolution and the “current schema”
  17. How do you find the row count for all your tables in Postgres
  18. How do you use variables in a simple PostgreSQL script?
  19. How to switch databases in psql?
  20. docker postgres pgadmin local connection
  21. Dynamically generate columns for crosstab in PostgreSQL
  22. Retrieving Comments from a PostgreSQL DB
  23. Orbeon Forms Postgres DB connection
  24. Alternate output format for psql showing one column per line with column name
  25. Library not loaded: /usr/local/opt/readline/lib/libreadline.6.2.dylib
  26. Destroying a Postgres DB on Heroku
  27. Is it possible to change the natural order of columns in Postgres?
  28. ERROR: could not stat file “XX.csv”: Unknown error
  29. SELECT or INSERT a row in one command
  30. Selecting rows ordered by some column and distinct on another

Leave a Comment