title: Postgresql Exclusion constraint with soft-deleted rows
body: <p>I am trying to accomplish a PostgreSQL constraint on te following table:</p>
  
  <pre><code>CREATE TABLE contracts
  (
      id bigint NOT NULL,
      startdate date NOT NULL,
      enddate date NOT NULL,
      price numeric(19,2) NOT NULL,
      deleted boolean NOT NULL,
      supplier_id bigint NOT NULL,
  )
  </code></pre>
  
  <p>It contains contracts for suppliers with different prices. For a given time only one contract can exists for a given supplier. I have made the following constraint to enforce that:</p>
  
  <pre><code>ALTER TABLE contracts ADD CONSTRAINT overlaping_contracts EXCLUDE USING GIST (
          supplier_id WITH =,
          daterange(startdate, enddate) WITH &amp;&amp;
  );
  </code></pre>
  
  <p>This ensures that one cannot insert a new contract that overlaps with an already existing contract. Now we also support "soft-deletion" of contracts. This invalidates the old contract and sets the "deleted" flag to true. Now I want to insert a new contract for the same period, but this overlaps triggering the constraint.</p>
  
  <p>I tried to combine a conditional unqiue index with the above constaint but cannot make it work. The documentation on exclusion constraints is quite small.</p>
  
  <p>My instinct tells me I have to add something like</p>
  
  <pre><code>deleted = false
  </code></pre>
  
  <p>to the exclusion constraint, but I can't find the right syntax to do so.</p>
  
  <p>How can I combine an exclusion constraint with a conditional unique index so I can enforce the overlapping constraint only on rows which have deleted = false?</p>