title: UPSERT in PostgreSQL using jOOQ
body: <p>I am trying to perform an UPSERT in PostgreSQL using the jOOQ library. </p>
  
  <p>For doing this I am currently trying to implement the following SQL statement in jOOQ:
  <a href="https://stackoverflow.com/a/6527838">https://stackoverflow.com/a/6527838</a></p>
  
  <p>My code looks like this so far:</p>
  
  <pre><code>public class UpsertExecutor {
  
      private static final Logger logger = LoggerFactory.getLogger(UpsertExecutor.class);
  
      private final JOOQContextProvider jooqProvider;
  
      @Inject
      public UpsertExecutor(JOOQContextProvider jooqProvider) {
          Preconditions.checkNotNull(jooqProvider);
  
          this.jooqProvider = jooqProvider;
      }
  
      @Transactional
      public &lt;T extends Record&gt; void executeUpsert(Table&lt;T&gt; table, Condition condition, Map&lt;? extends Field&lt;?&gt;, ?&gt; recordValues) {
          /*
           * All of this is for trying to do an UPSERT on PostgreSQL. See:
           * https://stackoverflow.com/a/6527838
           */
  
          SelectConditionStep&lt;Record1&lt;Integer&gt;&gt; notExistsSelect = jooqProvider.getDSLContext().selectOne().from(table).where(condition);
          SelectConditionStep&lt;Record&gt; insertIntoSelect = jooqProvider.getDSLContext().select(recordValues).whereNotExists(notExistsSelect);
  
          try {
              int[] result = jooqProvider.getDSLContext().batch(
                  jooqProvider.getDSLContext().update(table).set(recordValues).where(condition),
                  jooqProvider.getDSLContext().insertInto(table).select(insertIntoSelect)
              ).execute();
  
              long rowsAffectedTotal = 0;
              for (int rowsAffected : result) {
                  rowsAffectedTotal += rowsAffected;
              }
  
              if (rowsAffectedTotal != 1) {
                  throw new RuntimeException("Upsert must only affect 1 row. Affected: " + rowsAffectedTotal + ". Table: " + table + ". Condition: " + condition);
              }
          } catch (DataAccessException e) {
              if (e.getCause() instanceof BatchUpdateException) {
                  BatchUpdateException cause = (BatchUpdateException)e.getCause();
  
                  logger.error("Batch update error in upsert.", cause.getNextException());
              }
  
              throw e;
          }
      }
  }
  </code></pre>
  
  <p>This code does however not compile, since select() doesn't support a map of values:</p>
  
  <pre><code>SelectConditionStep&lt;Record&gt; insertIntoSelect = jooqProvider.getDSLContext().select(recordValues).whereNotExists(notExistsSelect);
  </code></pre>
  
  <h2>The Question</h2>
  
  <p>How do I provide select() with a set of predefined values like this: <code>SELECT 3, 'C', 'Z'</code>?</p>
  
  <h2>Update 1</h2>
  
  <p>I managed to get the code working. Here is the complete class:</p>
  
  <pre><code>public class UpsertExecutor {
  
      private static final Logger logger = LoggerFactory.getLogger(UpsertExecutor.class);
  
      private final JOOQContextProvider jooqProvider;
  
      @Inject
      public UpsertExecutor(JOOQContextProvider jooqProvider) {
          Preconditions.checkNotNull(jooqProvider);
  
          this.jooqProvider = jooqProvider;
      }
  
      @Transactional
      public &lt;T extends Record&gt; void executeUpsert(Table&lt;T&gt; table, Condition condition, List&lt;FieldValue&lt;Field&lt;?&gt;, ?&gt;&gt; recordValues) {
          /*
           * All of this is for trying to do an UPSERT on PostgreSQL. See:
           * https://stackoverflow.com/a/6527838
           */
  
          Map&lt;Field&lt;?&gt;, Object&gt; recordValuesMap = new HashMap&lt;Field&lt;?&gt;, Object&gt;();
          for (FieldValue&lt;Field&lt;?&gt;, ?&gt; entry : recordValues) {
              recordValuesMap.put(entry.getFieldName(), entry.getFieldValue());
          }
  
          List&lt;Param&lt;?&gt;&gt; params = new LinkedList&lt;Param&lt;?&gt;&gt;();
          for (FieldValue&lt;Field&lt;?&gt;, ?&gt; entry : recordValues) {
              params.add(val(entry.getFieldValue()));
          }
  
          List&lt;Field&lt;?&gt;&gt; fields = new LinkedList&lt;Field&lt;?&gt;&gt;();
          for (FieldValue&lt;Field&lt;?&gt;, ?&gt; entry : recordValues) {
              fields.add(entry.getFieldName());
          }
  
          SelectConditionStep&lt;Record1&lt;Integer&gt;&gt; notExistsSelect = jooqProvider.getDSLContext().selectOne().from(table).where(condition);
          SelectConditionStep&lt;Record&gt; insertIntoSelect = jooqProvider.getDSLContext().select(params).whereNotExists(notExistsSelect);
  
          try {
              int[] result = jooqProvider.getDSLContext().batch(
                  jooqProvider.getDSLContext().update(table).set(recordValuesMap).where(condition),
                  jooqProvider.getDSLContext().insertInto(table, fields).select(insertIntoSelect)
              ).execute();
  
              long rowsAffectedTotal = 0;
              for (int rowsAffected : result) {
                  rowsAffectedTotal += rowsAffected;
              }
  
              if (rowsAffectedTotal != 1) {
                  throw new RuntimeException("Upsert must only affect 1 row. Affected: " + rowsAffectedTotal + ". Table: " + table + ". Condition: " + condition);
              }
          } catch (DataAccessException e) {
              if (e.getCause() instanceof BatchUpdateException) {
                  BatchUpdateException cause = (BatchUpdateException)e.getCause();
  
                  logger.error("Batch update error in upsert.", cause.getNextException());
              }
  
              throw e;
          }
      }
  }
  </code></pre>
  
  <p>It does however not feel very clean with the <code>List&lt;FieldValue&lt;Field&lt;?&gt;, ?&gt;&gt; recordValues</code> parameter. Any better ideas on how to do this?</p>