Scala's Slick with multiple PK insertOrUpdate() throws exception ERROR: syntax error at end of input
composite-primary-key, postgresql, scala, slick, upsert
Solution
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.
Problem
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. ```