Slick 3: insertOrUpdate not working

Question

I'm using slick with Postgresql 9.6.1, Plya! 2.5 and play-slick 2.0.2.

(I also use slick-pg 0.14.3 but I don't think that it changes anything here.)

I'm using insertOrUpdate in a very straight forward way but I still get a unique exception.

I have a very simple test using insertOrUpdate: if I run it several times I always get an sql exception:

ERROR: duplicate key value violates unique constraint "ga_client_id_pkey"
  Detail: Key (client_id)=(1885746393.1464005051) already exists

However, my table is defined with client_id as primary key:

def clientId = column[String]("client_id", O.PrimaryKey)

and defined in sql as followed:

client_id    TEXT NOT NULL UNIQUE PRIMARY KEY

The function tested simply does:

db.run(gaClientIds.insertOrUpdate(gaClientId))

and the controller simply calls this methods and does nothing else.

A strange thing is that launching the methods itself several times don't lead to the error but the controller does although it only calls the method.

Is insertOrUpdate slick function not sure yet or am I missing something?

Answer 1

insertOrUpdate is only supported in MySQL driver

http://slick.lightbend.com/doc/3.2.1/supported-databases.html

You can try this library which gives you the implementation of insertOrUpdate/upsert

https://github.com/tminglei/slick-pg

This is how we use it on current project.

import com.github.tminglei.slickpg._
import com.typesafe.config.ConfigFactory
import slick.basic.Capability

object DBComponent {

  private val config = ConfigFactory.load()

  val driver = config.getString("rdbms.properties.driver") match {
    case "org.h2.Driver" => slick.jdbc.H2Profile
    case _               => MyPostgresProfile
  }

  import driver.api._

  val db: Database = Database.forConfig("rdbms.properties")

}

trait MyPostgresProfile extends ExPostgresProfile {

  // Add back `capabilities.insertOrUpdate` to enable native `upsert` support; for postgres 9.5+
  override protected def computeCapabilities: Set[Capability] =
    super.computeCapabilities + slick.jdbc.JdbcCapabilities.insertOrUpdate

  override val api: MyAPI.type = MyAPI

  object MyAPI extends API
}

object MyPostgresProfile extends MyPostgresProfile