Re: How does PostgreSQL treat null values in unique composite

On Tue, Apr 08, 2003 at 11:46:41AM -0700, Dennis Gearon wrote:
> the sql paradigm says NULL is 'unknown'
>
> So, you can have unique values in a column, but the unknown ones are ignored.
> this can be very handy, but is best used only in date/date related fields
> because NULLS complicate many SQL statements.

Take note that there is no standard for how NULLs are handled in unique
constraints, though. Some databases act like pgsql, some treat null as a
single unique value (what you were expecting), and some disallow unique
constraints on any columns that are nullable.

BTW, I think pgsql's ability to index on a function would enable you to
get the behavior you were expecting by indexing on something like
coalesce(aa,'null'), coalesce(bb,'null')
--
Jim C. Nasby (aka Decibel!) jim(at)nasby(dot)net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"