Re:Underrated Features of PostgreSQL
by famousactress on Wednesday November 02, 2011 @11:41AM
(#3187287)
I used Postgres in a former life.. really liked it, but switched to MySQL because at the time pg didn't have a very palatable solution for running a read-slave. Boy have I been interested in jumping back since v8&9! A question about partial indexes... Can this be used to solve the problem of multi-column unique constraints with null values? That is.. Say I've got columns a, b, and deleted_time. If a null deleted_time means the item hasn't been deleted.. traditionally I can't create a useful unique constraint across them (because NULL != NULL). Does this postgres feature allow me to create a unique constraint on (A,B) where deleted_time is null ?
[ Parent
| on HN ]
According to the manual [1], this is one of the standard use cases for partial indexes. See the last section.
CREATE UNIQUE INDEX index_name ON table_name (a, b) WHERE deleted_time IS NULL;
[1] http://www.postgresql.org/docs/9.1/static/indexes-partial.ht...