Multiple NULL values are not UNIQUE in PostgreSQL

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.

1

friendlysock edited 5 years ago | link

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.
1. 1
  
  steveno 5 years ago | link
  
  A query.
1

lmm 5 years ago | link

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.
1

steveno 5 years ago | link

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?
1. 2
  
  causal_agent edited 5 years ago | link
  
  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.
1

danielrheath 5 years ago | link

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.