3 ms·
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.
by famousactress 15y ago
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 ?
- kijin 15y agoAccording 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.html http://www.postgresql.org/docs/9.1/static/indexes-partial.ht...