title: JOOQ handling nested list UPSERT
body: <p>Using the documentation <a href="https://www.jooq.org/doc/3.13/manual/sql-building/sql-statements/insert-statement/insert-on-duplicate-key/" rel="nofollow noreferrer">here</a> I know I can use <code>onDuplicateKeyUpdate</code> to handle an UPSERT. But, how can I match values when inserting a list of list values? I'm using Postgres as the actual implementation here.</p>
  
  <p>Suppose the following Java taking in a nested List, then starting a JOOQ query.</p>
  
  <pre class="lang-java prettyprint-override"><code>List&lt;List&lt;String&gt;&gt; values = List.of(List.of("1", "2", "3"), List.of("4", "5", "6"));
  
  dsl.insertInto(TABLE)
          .values(values)
          .onDuplicateKeyUpdate()
  </code></pre>
  
  <p>Next I would have to call <code>set()</code> in the fluent call. However, with this being a nested list I cannot access each item here.</p>
  
  <p>For example, suppose the database contains this row already:</p>
  
  <pre><code>| col1 | col2 | col3 |
  +------+------+------+
  |   1  |   0  |   0  |
  +------+------+------+
  </code></pre>
  
  <p>With Col1 being the primary key, I'd expect the above code to do an UPSERT and try to insert, but then fail on the first row. Updating <code>col2</code> &amp; <code>col3</code> respectively. Then doing a normal insert for the second. Becoming:</p>
  
  <pre><code>| col1 | col2 | col3 |
  +------+------+------+
  |   1  |   2  |   3  |
  +------+------+------+
  |   4  |   5  |   6  |
  +------+------+------+
  </code></pre>
  
  <p>As SQL this would be:</p>
  
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO table(col1, col2 col3)
  ((1, 2, 3), (4, 5, 6))
  ON CONFLICT (col1)
  DO UPDATE SET
  col1 = excluded.col1,
  col2 = excluded.col2,
  col3 = excluded.col3
  </code></pre>
  
  <p>Is there a good way to handle this in JOOQ? Or should I just go with raw SQL and bind in values instead?</p>