title: Why does Postgres handle NULLs inconsistently where unique constraints are involved?
body: <p>I recently noticed an inconsistency in how Postgres handles NULLs in columns with a unique constraint. </p>
  
  <p>Consider a table of people:</p>
  
  <pre><code>create table People (
     pid  int  not null,
     name text not null,
     SSN  text unique,
   primary key (pid)
  );
  </code></pre>
  
  <p>The SSN column should be kept unique. We can check that:</p>
  
  <pre><code>-- Add a row.
  insert into People(pid, name, SSN)
  values(0, 'Bob', '123');
  
  -- Test the unique constraint.
  insert into People(pid, name, SSN)
  values(1, 'Carol', '123');
  </code></pre>
  
  <p>The second insert fails because it violates the unique constraint on SSN. So far, so good. But let's try a NULL:</p>
  
  <pre><code>insert into People(pid, name, SSN)
  values(1, 'Carol', null);
  </code></pre>
  
  <p>That works.</p>
  
  <pre><code>select *    
  from People;
  
  0;"Bob";"123"
  1;"Carol";"&lt;NULL&gt;"
  </code></pre>
  
  <p>A unique column will take a null. Interesting. How can Postgres assert that null is in any way unique, or not unique for that matter? </p>
  
  <p>I wonder if I can add two rows with null in a unique column.</p>
  
  <pre><code>insert into People(pid, name, SSN)
  values(2, 'Ted', null);
  
  select *    
  from People;
  
  0;"Bob";"123"
  1;"Carol";"&lt;NULL&gt;"
  2;"Ted";"&lt;NULL&gt;"
  </code></pre>
  
  <p>Yes I can. Now there are two rows with NULL in the SSN column even though SSN is supposed to be unique.</p>
  
  <p>The <a href="http://www.postgresql.org/docs/9.0/interactive/sql-createtable.html" rel="noreferrer">Postgres documentation</a> says, <em>For the purpose of a unique constraint, null values are not considered equal.</em> </p>
  
  <p>Okay. I can see the point of this. It's a nice subtlety in null-handling: By considering all NULLs in a unique-constrained column to be disjoint, we delay the unique constraint enforcement until there is an actual non-null value on which to base that enforcement.  </p>
  
  <p>That's pretty cool. But here's where Postgres loses me. If all NULLs in a unique-constrained column are not equal, as the documentation says, then we should see all of the nulls in a <em>select distinct</em> query.</p>
  
  <pre><code>select distinct SSN
  from People;
  
  "&lt;NULL&gt;"
  "123"
  </code></pre>
  
  <p>Nope. There's only a single null there. It seems like Postgres has this wrong. But I wonder: Is there another explanation? </p>
  
  <hr>
  
  <p>Edit: </p>
  
  <p>The Postgres docs do specify that "Null values are considered equal in this comparison." in the <a href="http://www.postgresql.org/docs/9.0/interactive/queries-select-lists.html" rel="noreferrer">section on SELECT DISTINCT</a>. While I do not understand that notion, I'm glad it's spelled out in the docs.</p>