title: Unique constraint with soft deleted rows excluded
body: <p>We have a table with a unique constraint on it, for feedback left from one user, for another, in relation to a sale.</p>
  
  <pre><code>ALTER TABLE feedback
  ADD CONSTRAINT unique_user_subject_and_sale
  UNIQUE (user_id, subject_id, sale_id)
  </code></pre>
  
  <p>This ensures we don't accidentally get duplicated rows of feedback.</p>
  
  <p>Currently we sometimes hard-delete feedback left in error and left the user leave it again. We want to change to soft-delete:</p>
  
  <pre><code>ALTER TABLE feedback
  ADD COLUMN deleted_at timestamptz
  </code></pre>
  
  <p>If <code>deleted_at IS NOT NULL</code>, consider the feedback deleted, though we still have the audit trail in our DB (and will probably show it ghosted out to site admins).</p>
  
  <p>How can we keep our unique constraint when we're using soft-delete like this? Is it possible without using a more general <code>CHECK()</code> constraint the does an aggregate check (I've never tried using check constraint like this).</p>
  
  <p>It's like I need to append a WHERE clause to the constraint.</p>