title: Postgresql - is there a way of knowing an INSERT error code to determine if I should UPDATE because of a duplicate unique restriction?
body: <p>I have a composite UNIQUE set of columns in my table. Therefore if I insert into the table where the unique key is violated, Postgresql returns and error and my PHP script can read this error. </p>
  
  <p>When inserting, instead of doing this:</p>
  
  <pre><code>SELECT id FROM table WHERE col1='x' and col2='y'
  (if no rows)
  INSERT INTO table...
  (else if rows are found)
  UPDATE table SET...
  </code></pre>
  
  <p>I prefer to use:</p>
  
  <pre><code>INSERT INTO table...
  (if error occurred then attempt to UPDATE)
  UPDATE table SET...
  </code></pre>
  
  <p>The kind of error returned from the above would be "ERROR: duplicate key value violates unique constraint "xxxxxxxx_key"</p>
  
  <p>However, there is no point doing an UPDATE if the INSERT failed for some other reason, such as invalid data. Is there a way of "knowing" (from PHP/Postgres) if the error actually failed from this duplicate key issue, rather than invalid data? I'm just curious. Performing an UPDATE also would return an error anyway if the data were invalid, but what would you say is best practice?</p>
  
  <p>Many thanks!</p>