I think we have a reasonable consensus around the name "operator
exclusion constraints", Robert Haas's suggestion. I am OK with that
name, and it got support from David Fetter and Tom Lane. As David Fetter
said, it's useful for the name to hint at the API.
Peter had some reasonable objections to that name, but the word "unique"
just doesn't cut it for this feature. My feature allows constraints
which are more restrictive than a unique constraint; but the final straw
was after a discussion with Tomás in which we determined that you can
also define constraints which are the opposite of unique: all values
must be the same (by using <> as the operator*).
I agree with Peter that we should support creating these constraints at
table creation time. This can be supported with the following syntax:
CONSTRAINT foo_constr (a <op>, ...) { USING INDEX foo_idx | USING method }
and it's also a more declarative syntax for the ALTER TABLE case, and
prevents a series of other problems that Peter pointed out.
There's an important unresolved question with this patch that I need to
address, which just came to light: what about functional/expression
indexes?
Say you have a table foo(a text, b text) and an index on: ((a||b)::circle)
You could define an operator constraint like: ((a||b)::circle &&)
and that would be sane. But I suppose I should also allow any expression
with the same signature, like: ((b||a)::circle &&)
[ not a very realistic example, but it seems like it may be useful ]
Does that make sense? Does someone have a better idea? Am I missing
other issues here?
How do I test if two functions/expressions: a. are identical? b. have matching signatures?
Regards,Jeff Davis
*: Understandably, there is no strategy for <> for most data types.
However, if your constraint is that all values must be the same, it's
quite reasonable to add one and be able to use an index to quickly find
values that are different.