title: SQL - relying on server errors during INSERT
body: <p>I'm working with PostgreSQL 9.1. Let's say I have a table where some columns have <code>UNIQUE</code> constraint. The easiest example:</p>
  
  <pre><code>CREATE TABLE test (
      value INTEGER NOT NULL UNIQUE
  );
  </code></pre>
  
  <p>Now, when inserting some values, I have to separately handle the case, where the values to be inserted are already in the table. I have two options:</p>
  
  <ul>
  <li>Make a <code>SELECT</code> beforehand to ensure the values are not in the table, or:</li>
  <li>Execute the <code>INSERT</code> and watch for any errors the server might return.</li>
  </ul>
  
  <p>The application utilizing the PostgreSQL database is written in Ruby. Here's how I would code the second option:</p>
  
  <pre><code>require 'pg'
  
  db = PG.connect(...)
  
  begin
      db.exec('INSERT INTO test VALUES (66)')
  rescue PG::UniqueViolation
      # ... the values are already in the table
  else
      # ... the values were brand new
  end
  
  db.close
  </code></pre>
  
  <p>Here's my thinking: let's suppose we make a <code>SELECT</code> first, before inserting. The SQL engine would have to scan the rows and return any matching tuples. If there are none, we make an <code>INSERT</code>, which presumably makes yet another scan, to see if the <code>UNIQUE</code> constraint is not about to be violated by any chance. So, in theory, second option would speed the execution up by 50%. Is this how PostgreSQL would actually behave?</p>
  
  <p>We're assuming there's no ambiguity when it comes to the exception itself (e.g. we only have one <code>UNIQUE</code> constraint).</p>
  
  <p>Is it a common practice? Or are there any caveats to it? Are there any more alternatives?</p>