Scala's Slick with multiple PK insertOrUpdate() throws exception ERROR: syntax error at end of input

Question

I am using Scala' Slick and PostgreSQL. And I am working well with tables with single PK. Now I need to use a table with multiple PKs:

case class Report(f1: DateTime,
    f2: String,
    f3: Double)

class Reports(tag: Tag) extends Table[Report](tag, "Reports") {
    def f1 = column[DateTime]("f1")
    def f2 = column[String]("f2")
    def f3 = column[Double]("f3")

    def * = (f1, f2, f3) <> (Report.tupled, Report.unapply)
    def pk = primaryKey("pk_report", (f1, f2))
}

val reports = TableQuery[Reports]

when I have empty table and use reports.insert(report) it works well. But when I use reports.insertOrUpdate(report) I receive and exception:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: syntax error at end of input
  Position: 76
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
    at ....

What am I doing wrong? How to fix it?

Thanks in advance.

PS. I tried workaround - tried to implement "if exist update else insert" logic by:

  val len = reports.withFilter(_.f1 === report.f1).withFilter(_.f2 === report.f2).length.run.toInt
                    if(len == 1) {
                        println("Update: " + report)
                        reports.update(report)
                    } else {
                        println("Insert: " + report)
                        reports.insert(report)
                    }

But I still get exception on update:

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "pk_report"
  Detail: Key ("f1", f2)=(2014-01-31 04:00:00, addon_io.aha.connect) already exists.

What is `insertOrUpdate`, I don't see such method in the `Slick` api. — Ende Neu, Jul 04 '14 at 19:27
I have it and it works well when table has one PK. How else can I implement UPSERT operation? — Konstantin Trunin, Jul 05 '14 at 05:27
Ende Neu: insertOrUpdate was added in Slick 2.1-M2 and will be part of Slick 2.1. — cvogt, Jul 06 '14 at 16:36
Regarding the second exception. In case len > 1 you also try to insert. Maybe that's the problem. Wild guess :). — cvogt, Jul 06 '14 at 16:38

score 7 · Answer 1 · answered Jan 04 '15 at 11:09

Concerning your initial question, insertOrUpdate on a table with compound keys is broken in Slick (at least with PGSql), so the error is not on your side. See bug report e.g., here: https://github.com/slick/slick/issues/966

So you have to design a workaround, however the "upsert" operation is very prone to race conditions, and is very hard to design properly as PostgreSQL does not provide native feature to perform this. See e.g., http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/

Anyway, another way to perform the operation which is a bit less prone to race condition is to first update (which will do not do anything if the row does not exist), and then perform a "insert select" query, which will only insert if the row does not exist. This is the wày Slick will perform the insertOrUpdate operation on PostgreSQL with single PK. However, "insert select" cannot be done using Slick directly, you will have to fallback to direct SQL.

score 3 · Answer 2 · answered Sep 17 '14 at 15:28

Second part where you have

val len = reports.withFilter(_.f1 === report.f1).withFilter(_.f2 === report.f2).length.run.toInt
                if(len == 1) {
                    println("Update: " + report)
                    reports.update(report)
                } else {
                    println("Insert: " + report)
                    reports.insert(report)
                }

change reports.update(report) with

reports.filter(_.id === report.id).update(report)

actually you can just make one filter call (replacing your first withFilter )

score 0 · Answer 3 · edited Jan 15 '16 at 17:18

I've successfully applied the technique described here so my upsert method looks like this:

  def upsert(model: String, module: String, timestamp: Long) = {
    // see this article http://www.the-art-of-web.com/sql/upsert/
    val insert     = s"INSERT INTO $ModulesAffectedTableName (model, affected_module, timestamp) SELECT '$model','$module','$timestamp'"
    val upsert     = s"UPDATE $ModulesAffectedTableName SET timestamp=$timestamp WHERE model='$model' AND affected_module='$module'"
    val finalStmnt = s"WITH upsert AS ($upsert RETURNING *) $insert WHERE NOT EXISTS (SELECT * FROM upsert)"
    conn.run(sqlu"#$finalStmnt")
  }

score 0 · Answer 4 · answered Feb 06 '17 at 05:53

Hopefully this issue will be fixed in 3.2.0

Currently, I work around this issue by creating a dummy table for table creation:

class ReportsDummy(tag: Tag) extends Table[Report](tag, "Reports") {
    def f1 = column[DateTime]("f1")
    def f2 = column[String]("f2")
    def f3 = column[Double]("f3")

    def * = (f1, f2, f3) <> (Report.tupled, Report.unapply)
    def pk = primaryKey("pk_report", (f1, f2))
}

and a "real" table for upsert

class Reports(tag: Tag) extends Table[Report](tag, "Reports") {
    def f1 = column[DateTime]("f1", O.PrimaryKey) 
    def f2 = column[String]("f2", O.PrimaryKey) //two primary keys here, which would throw errors on table creation. Hence a dummy one for the task
    def f3 = column[Double]("f3")

    def * = (f1, f2, f3) <> (Report.tupled, Report.unapply)
}

Scala's Slick with multiple PK insertOrUpdate() throws exception ERROR: syntax error at end of input

4 Answers4