title: Batch update in Postgresql
body: <p>I needed <code>UPSERT</code> functionality in Postgres. Since Postgres does not support this natively, I wrote a function which does that (tries to update, if no row was updated then inserts)</p>
  
  <p>This is the template for the function: <a href="https://stackoverflow.com/a/1109198/681671">https://stackoverflow.com/a/1109198/681671</a></p>
  
  <pre><code>CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS
  $$
  BEGIN
      LOOP
          -- first try to update the key
          UPDATE db SET b = data WHERE a = key;
          IF found THEN
              RETURN;
          END IF;
          -- not there, so try to insert the key
          -- if someone else inserts the same key concurrently,
          -- we could get a unique-key failure
          BEGIN
              INSERT INTO db(a,b) VALUES (key, data);
              RETURN;
          EXCEPTION WHEN unique_violation THEN
              -- do nothing, and loop to try the UPDATE again
          END;
      END LOOP;
  END;
  $$
  LANGUAGE plpgsql;
  
  
  SELECT merge_db(1, 'david');
  </code></pre>
  
  <p>I am using Spring JDBC template. This select statement (in its parameterized form) and an array of objects is what I pass to the the batchUpdate method of JDBCTemplate.</p>
  
  <p>I get this exception:</p>
  
  <pre><code> org.postgresql.util.PSQLException: A result was returned when none was expected.
  </code></pre>
  
  <p>I suspect it is because of use of <code>SELECT</code>. </p>
  
  <p>I know I can use Callable in a loop but that would make the app very chatty and I/O latency will make it very slow.</p>
  
  <p>How is batch upsert done in Postgres using Spring JDBC / raw JDBC?</p>
  
  <p>I am using Postgresql 9.1.</p>