Just got bitten by this. Am I the only one who didn’t know this? Is this common in database land? I don’t remember Oracle working like this.
You are apparently not the first to run into this.
Did you hit it in the indexing form, or was it a query that did it?8
For a longer dissertation on it, check here.
It’s comon in database land. NULL’s semantics in SQL are pretty messed up. I recommend avoiding SQL as much as possible, though I recognize that’s a minority view.
In PostgreSQL you can define a column as unique using the UNIQUE keyword when you create the table or through a unique index after you create table. Do both methods suffer from this?
Speaking of which, what exactly is the difference there? Do both of those methods actually produce the same result:
a unique index?
Yes, UNIQUE only ever applies to actual values, not NULL, and specifying UNIQUE on a column is shorthand for creating an index.
Edit: Actually, UNIQUE does apply to NULL, but NULL compares unequal to all values, including itself (like NaN in floats), so all NULL are unique.
There’s a good summary of the different behaviors here.
TL;DR is SQLite, PostgreSQL, MySQL, Oracle, and Firebird do this. Informix and Microsoft SQL Server do not.