title: UNIQUE constraint where NULL is one valid value
body: <p>Inn PostgreSQL I want to have a multi-column UNIQUE constraint where one of the columns can be NULL exactly once.</p>
  
  <p>What I've tried so far:</p>
  
  <pre><code>ALTER TABLE customexternalemail 
  ADD CONSTRAINT customexternalemail_sp_emaildomain_unique 
      UNIQUE(serviceproviderid, emailprefix, emaildomainid);
  </code></pre>
  
  <p>Where <code>serviceproviderid</code> and <code>emaildomainid</code> are BIGINT, and <code>emailprefix</code> is TEXT. <code>emaildomainid</code> is the only column with NULL allowed, and is the column I'm having trouble with.</p>
  
  <p>Basically, I want to allow only one entry matching a combination of <code>serviceproviderid</code>, <code>emailprefix</code>, and <code>emaildomainid</code>, where <code>emaildomainid</code> could be a BIGINT value or NULL. Currently (with the above constraint), it will accept repeats if <code>emaildomainid</code> is NULL, but if <code>emaildomainid</code> is not NULL, it must be unique.</p>