0

How can I do insert if an entry not yet exists using Squeryl?

I tried to catch a PSQLException if an insert statement is executed on an already-existing tuple, but it does not work (the PSQLException is not catched). In fact it would be better to tell Squeryl to not insert already existing tuples. That is what I am searching for.

ideaboxer
  • 3,863
  • 8
  • 43
  • 62

2 Answers2

2

Unfortunately, if you are using Postgres, if an exception occurs you are not going to be able to continue processing. There is a pretty good discussion of this here: https://groups.google.com/forum/?fromgroups=#!topic/squeryl/zaflXRra0qg

To do what you are looking for, you'd probably want to issue a select first to find the ids that already exist and remove them from the items you are looking to insert.

jcern
  • 7,798
  • 4
  • 39
  • 47
  • Could I do select and insert in an atomic operation? – ideaboxer Apr 25 '13 at 19:35
  • 1
    Not that I can think of natively, however you could probably start a transaction, then grab the raw jdbc session to issue a table lock, and then execute your commands. Not super efficient, but would be atomic. – jcern Apr 25 '13 at 19:48
0

You don't want the same tuples to exist in the DB at all, or only that squeryl will not insert them?

In the first case, just add a Unique Constraint on that tuple. Example:

on(myTable)(e => declare(
    columns(e.name, e.year) are (indexed("myIndex"), unique)
))

This code is to be placed near table definitions like val myTable = table[MyTable]. I also added an index on this tuple, you can drop this code if you want.

VasiliNovikov
  • 9,681
  • 4
  • 44
  • 62
  • The first is handled by the database. It does not allow one tuple to exist more than one time. So that is not what I want. In fact I just need Squeryl to avoid unwanted inserts that would be rejected by the database anyway by throwing an exception (which is not catchabale in the case of the thrown PSQLException) – ideaboxer Apr 25 '13 at 19:40