Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Is it possible to have a custom unique column constraint as follows? Suppose I have two cols, subset and type, both strings (though the data types probably doesn't matter).

If type is "true", then I want the combination of type and subset to be unique. Otherwise, there is no constraint. I'm using PostgreSQL 8.4 on Debian.

share|improve this question
add comment

2 Answers 2

up vote 12 down vote accepted

In other words, you want subset to be unique if type = 'true'.
A partial unique index will do that:

CREATE UNIQUE INDEX tbl_some_name_idx ON tbl (subset) WHERE type = 'true';

This way you can even make combinations with NULL unique, which is not possible otherwise - as detailed in this related answer:
PostgreSQL multi-column unique constraint and NULL values

share|improve this answer
    
Thanks Erwin. I didn't see this option when I looked at the documentation. A more direct link is postgresql.org/docs/current/interactive/indexes-partial.html. See Example 11-3. –  Faheem Mitha Mar 23 '13 at 18:54
    
@FaheemMitha: I linked one level higher, since you need to combine a partial index with a unique index. –  Erwin Brandstetter Mar 23 '13 at 19:13
1  
@Erwin That page (about partial indexes), has an example with partial unique index. –  ypercube Mar 23 '13 at 19:37
    
@ypercube: Ah, right. That's the better link. I changed my answer to point to that last chapter. –  Erwin Brandstetter Mar 23 '13 at 21:08
add comment

This is supplemental to Erwin's answer above, but PostgreSQL supports a bunch of types of indexes. These are not generally mutually exclusive. You can think of these as being:

  • Index method (btree, GiST, GIN, etc). Choose one, if necessary (btree being the default)
  • Partial or full. If partial use a where clause
  • Direct or functional. You can index the output of functions.
  • Unique or non-unique

These can all be combined in various ways. All you are doing here is using the unique and partial features, so that gives you partial unique indexes (which are extremely useful as you are finding out.

But suppose you want to have a case insensitive index on the subset field where type is true. Then you would add a functional definition:

CREATE INDEX my_index_name_idx_u ON tbl (lower(subset)) WHERE type;

Note this creates a unique index on the output of the lower() function called on the subset attribute where type is true.

share|improve this answer
    
So the index in Erwin's answer is direct, whereas the one in your example in functional, correct? –  Faheem Mitha Mar 24 '13 at 18:12
    
@FaheemMitha: Correct. –  Erwin Brandstetter Mar 24 '13 at 21:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.