title: Isn't PostgreSQL single query execution atomic?
body: <p>In my postgresql DB, I have a table "my_table" with primary key on columns (a, b).</p>
  
  <p>I wrote the query below for inserts into this table which ensures that the primary key constraint is never violated.</p>
  
  <pre><code>INSERT INTO my_table(a, b, c, d, e, f)
    (SELECT 'a1',
            'b1',
            'c1',
            1442849052013,
            1,
            FALSE
     FROM
       (SELECT 'a1'::VARCHAR(100) AS a,
               'b1'::VARCHAR(50) AS b) AS new_fields
     LEFT OUTER JOIN my_table AS old_fields ON (new_fields.a = old_fields.a
                                                AND new_fields.b = old_fields.b)
     WHERE old_fields.a IS NULL);  
  </code></pre>
  
  <p>As multiple such queries get fired by concurrently running threads, I sometimes get </p>
  
  <blockquote>
    <p>PSQLException: duplicate key value violates unique constraint
    "my_table_pkey". Detail: Key (a, b)=(a1, b1) already exists.</p>
  </blockquote>
  
  <p>I am unable to understand that how can this query lead to the specified error condition as this query inserts into "my_table" only if its primary key columns are missing and that this condition is being checked within the same query.</p>
  
  <p>If this is a valid scenario, doesn't this mean that query execution in postgreSQL is not atomic ?</p>
  
  <p>Can somebody please explain...</p>
  
  <p>Here, Select For Update syntax will not work as well because Select For Update takes a lock on selected row and I want to insert a row if it not already exists.</p>