Re: How to enforce uniqueness when NULL values are present?

On Sun, Mar 11, 2007 at 11:09:56AM +0100, Christian Schröder wrote:
> Of course, if a NULL always means "unknown", then this approach doesn't
> make sense. Where can I find an authorative definition of what NULL
> means? As I have quoted before, according to the Wikipedia (far from
> being authorative!) a NULL can also mean "not applicable".

Nowhere. SQL's definition of NULL isn't even consistant, which is why
some people prefer to avoid it altogether.

Your particular case may be solvable with:

CREATE UNIQUE INDEX foo ON bar(key1, (coalesce(key2,'1900-01-01')));

Though I agree that -infinity would be a better fit.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.