Generated upsert with `do nothing` returns no rows and throws exception

[kaaveland]

This code will throw an exception if the row is already present in the table:

  override def upsert(unsaved: SoknadsdokumentRow)(implicit c: Connection): SoknadsdokumentRow = {
    SQL"""insert into "soknad"."soknadsdokument"("dokument_lopenummer", "soker_id", "opptakstype_kode", "opptak_kode")
          values (
            ${ParameterValue(unsaved.dokumentLopenummer, null, ToStatement.intToStatement)}::int4,
            ${ParameterValue(unsaved.sokerId, null, SokerId.toStatement)}::uuid,
            ${ParameterValue(unsaved.opptakstypeKode, null, OpptakstypeKode.toStatement)}::text,
            ${ParameterValue(unsaved.opptakKode, null, implicitly[ToStatement[OpptakKode]])}::int4
          )
          on conflict ("opptakstype_kode", "opptak_kode", "soker_id", "dokument_lopenummer")
          do nothing
          returning "dokument_lopenummer", "soker_id", "opptakstype_kode", "opptak_kode"
       """
      .executeInsert(SoknadsdokumentRow.rowParser(1).single)

The reason is that postgres returns no rows when it executes do nothing (I'm not entirely sure why this choice was made). I guess this is a special case because all the columns are part of the primary key, so there are no remaining columns to update. The following code would return a row even if it was already present (only the do nothing line is changed):

  override def upsert(unsaved: SoknadsdokumentRow)(implicit c: Connection): SoknadsdokumentRow = {
    SQL"""insert into "soknad"."soknadsdokument"("dokument_lopenummer", "soker_id", "opptakstype_kode", "opptak_kode")
          values (
            ${ParameterValue(unsaved.dokumentLopenummer, null, ToStatement.intToStatement)}::int4,
            ${ParameterValue(unsaved.sokerId, null, SokerId.toStatement)}::uuid,
            ${ParameterValue(unsaved.opptakstypeKode, null, OpptakstypeKode.toStatement)}::text,
            ${ParameterValue(unsaved.opptakKode, null, implicitly[ToStatement[OpptakKode]])}::int4
          )
          on conflict ("opptakstype_kode", "opptak_kode", "soker_id", "dokument_lopenummer")
          do set update "opptakstype_kode" = EXCLUDED."opptakstype_kode"
          returning "dokument_lopenummer", "soker_id", "opptakstype_kode", "opptak_kode"
       """
      .executeInsert(SoknadsdokumentRow.rowParser(1).single)

I'd be happy to take a stab at this if we agree that we should.

[oyvindberg]

On the big picture side, I think tables where all columns appear in the primary key should get a different interface, with Set-like API.

However, the time for that is not really now, so in the meantime I'd be ok with a fix like what you propose.