Hacker News new | past | comments | ask | show | jobs | submit login

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 ?

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...

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact