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?
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.
A query.
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, notNULL
, and specifyingUNIQUE
on a column is shorthand for creating an index.Edit: Actually,
UNIQUE
does apply toNULL
, butNULL
compares unequal to all values, including itself (likeNaN
in floats), so allNULL
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.