Slick 3.0 - insert if not exists with Postgres

[Debasish Ghosh]

Hi - I am using the technique demonstrated in http://stackoverflow.com/a/31352126 to insert a row if not exists based on some criteria. The core function of the linked post looks like this ..

def insertIfNotExists(name: String) = users.forceInsertQuery {

  val exists = (for (u <- users if u.name === name.bind) yield u).exists
  val insert = (name.bind, None) <> (User.apply _ tupled, User.unapply)
  for (u <- Query(insert) if !exists) yield u
}

It generates the insert statement as ..

insert into "USERS" ("NAME","ID") select ?, null where not exists(select x2."NAME", x2."ID" from "USERS" x2 where x2."NAME" = ?)

which is as the post suggests. The problem is this does not work in Postgres, which does not allow null to be specified for a NOT NULL serial primary key. What's the best alternative here ?

Thanks.

[Yosef Fertel]

Without knowing more about your table structure, I'm assuming that ID is auto-incrementing and the only other column is 'name' which is not null.

Would this work for you?

def insertIfNotExists(name: String) = users.map(u => u.name).forceInsertQuery {
val exists = users.filter(_.name === name.bind).exists
Query(name.bind).filter(_ => !exists)
}

it would generate something like

insert into "USER" ("NAME") select ? where not exists(select x2."ID", x2."NAME" from "USER" x2 where x2."NAME" = ?)

Notice the `map(u => u.name)` before the `forceInsertQuery`.

[Debasish Ghosh]

Thanks for the response .. my use case was a bit more complicated. Something like the following - insert the passed in User if no record matching the name exists ..

def insertIfNotExists(name: String, user: User) = users.map(u => (u.name, u.email)).forceInsertQuery {

  val exists = users.filter(_.name === name.bind).

exists
  Query((name.bind, user.email)).filter(_ => !exists)
}

Thanks.

[Naftoli Gugenheim]

Why are you using forceInsertXXX?

--

---
You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/a33c70ea-1960-4359-aab8-73f8c5843ba1%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ryan Bair]

Assuming you have or can add a unique constraint to that column, how about leveraging that? Attempt the insert and catch the exception in the case a matching record already exists.

--

---
You received this message because you are subscribed to the Google Groups "Slick / ScalaQuery" group.
To unsubscribe from this group and stop receiving emails from it, send an email to scalaquery+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/scalaquery/eff04ceb-df0e-4627-91cb-6346551be65c%40googlegroups.com.