configure `insertOrUpdate` to DO NOTHING on conflict

[sebarys]

Hello,

Is it possible to configure insertOrUpdate statement to DO NOTHING instead of performing upsert when row with given PK already exist?

Use case: I want insert list (dozens) of rows and instead of performing (get row + insert if not exist) for each I would like instruct insert statement to DO NOTHING on conflict detection.

Thanks for help in advance! :)

[GEverding]

This is an important feature. How hard is it to implement?

[misaugstad]

Isn't this an issue with Slick more broadly? Or is there something specific to slick-pg that I'm missing here?

[hughsimpson]

It's not too bad to build this out, actually. I have the following that I have been using myself for a couple of years now:

trait FurtherPostgresProfileExtensions { self: RedzonePostgresDriver =>

  def createDoNothingOnConflictBuilder(conflictsToConsider: Option[String], node: Insert): PostgresUpsertBuilder =
    new NativeDoNothingOnConflictBuilder(conflictsToConsider, node)

  def doNothingOnConflictConditionCompiler(maybeConflictCondition: Option[String]): QueryCompiler =
    QueryCompiler(
      Phase.assignUniqueSymbols,
      Phase.inferTypes,
      new InsertCompiler(InsertCompiler.AllColumns),
      new JdbcInsertCodeGen(createDoNothingOnConflictBuilder(maybeConflictCondition, _)))

  class NativeDoNothingOnConflictBuilder(maybeConflictCondition: Option[String], ins: Insert) extends PostgresUpsertBuilder(ins) {
    private lazy val funcDefinedPKs = table.profileTable.asInstanceOf[Table[?]].primaryKeys

    private lazy val (nonPkAutoIncSyms, insertingSyms) = syms.toSeq.partition { s =>
      s.options.contains(ColumnOption.AutoInc) && !(s.options contains ColumnOption.PrimaryKey)
    }

    override lazy val (pkSyms, softSyms) = insertingSyms.partition { sym =>
      sym.options.contains(ColumnOption.PrimaryKey) || funcDefinedPKs.exists(pk =>
        pk.columns.collect {
          case Select(_, f: FieldSymbol) => f
        }.exists(_.name == sym.name))
    }

    private lazy val insertNames = insertingSyms.map { fs => quoteIdentifier(fs.name) }
    override lazy val pkNames = pkSyms.map { fs => quoteIdentifier(fs.name) }
    override lazy val softNames = softSyms.map { fs => quoteIdentifier(fs.name) }

    override def buildInsert: InsertBuilderResult = {
      val insert = s"insert into $tableName (${insertNames.mkString(",")}) values (${insertNames.map(_ => "?").mkString(",")})"
      val conflict = maybeConflictCondition match {
        case None => "conflict"
        case Some(cond) => s"conflict $cond"
      }
      new InsertBuilderResult(table, s"$insert on $conflict do nothing", syms)
    }

    override def transformMapping(n: Node) = reorderColumns(n, insertingSyms ++ nonPkAutoIncSyms ++ nonPkAutoIncSyms ++ nonPkAutoIncSyms)
  }

}

trait CustomDriverExtensions { self: RedzonePostgresDriver#MyAPIImpl =>
  type MultiInsertResult = Option[Int]

  class EnrichedQuery[U, C[_]](q: Query[?, U, C]) {

    class SubCompiled(source: Node) extends JdbcCompiledInsert(source) {

      def generateArtifacts(maybeConflictCondition: Option[String]): Artifacts =
        compile(doNothingOnConflictQueryCompiler(maybeConflictCondition))

    }

    val compiled = new SubCompiled(q.toNode)

    def doNothingOnConflict(
      maybeConflictCondition: Option[String] = None
    ): Iterable[U] => ProfileAction[MultiInsertResult, NoStream, Effect.Write] = {
      val artifacts: compiled.Artifacts = compiled.generateArtifacts(maybeConflictCondition)
      (values: Iterable[U]) => new DoNothingOnConflict(values, artifacts)
    }

    class DoNothingOnConflict(values: Iterable[U], artifactsForConflictType: compiled.Artifacts) extends SimpleJdbcProfileAction[MultiInsertResult](
          "DoNothingOnConflictAction", Vector(artifactsForConflictType.sql)) {

      override def run(ctx: JdbcBackend#JdbcActionContext, sql: Vector[String]) =
        nativeUpsert(values, sql.head)(ctx.session)

      protected def preparedInsert[T](sql: String, session: JdbcBackend#Session)(f: PreparedStatement => T) =
        session.withPreparedStatement(sql)(f)

      protected def nativeUpsert(values: Iterable[U], sql: String)(
        implicit session: JdbcBackend#Session
      ): MultiInsertResult =
        preparedInsert(sql, session) { st =>
          st.clearParameters()
          for (value <- values) {
            artifactsForConflictType
              .converter
              .set(value, st, 0)
            st.addBatch()
          }
          val counts = st.executeBatch()
          retManyBatch(st, values, counts)
        }

      protected def retManyBatch(@unused st: Statement, @unused values: Iterable[U], updateCounts: Array[Int]) = {
        var unknown = false
        var count = 0
        for ((res, idx) <- updateCounts.zipWithIndex) res match {
          case Statement.SUCCESS_NO_INFO => unknown = true
          case Statement.EXECUTE_FAILED => throw new SlickException("Failed to insert row #" + (idx + 1))
          case i => count += i
        }
        if (unknown) None else Some(count)
      }

    }

  }

}

and then it's relatively flexible to use, e.g. MyTable.TableQuery[RowModel].doNothingOnConflict(Some("""("field") where "someCondition" is null """)) to specify which conflict you want to do nothing on. I never got around to cleaning this up enough to pr though...