Having difficulty catching postgresql exception in sequel/ruby

I have postgresql table that has somekind of unique constraint.

I have ruby script that will update thi开发者_Python百科s table. The ruby script should be able to switch to UPDATE instead of INSERT when this kind of error occured:

PGError: ERROR: duplicate key value violates unique constraint

CMIIW, sequel seems cannot catch this exception??

Anyway,sample code below is something that I would like to see to work but apparently not:

@myarray.each do |x|
        fresh_ds = DB["INSERT INTO mytable (id, col_foo) values ('#{x}' ,'#{myhash[x]}')"] 
        result = fresh_ds.insert

        catch Sequel::Error do

            fresh_ds = DB["UPDATE mytable set col_foo = '#{myhash[x]} where id = #{x}"]
            result = fresh_ds.update

        end

end

Maybe my ruby code is wrong or I missed something I don't know.

Any solution?

Thanks.

UPDATE: code below works, the error is caught when unique constraint is violated.

@myarray.each do |x|
    begin
      # INSERT CODE
    rescue Sequel::Error
      # UPDATE CODE
    end

end

---

First, try-catch in Ruby goes like this:

begin
...
rescue ExceptionClass => ex
...
end

Another thing is that you should use Sequel's parameter interpolation instead of making whole query by hand (mainly because of SQL injection):

fresh_ds = DB["INSERT INTO mytable (id, col_foo) values (? ,?)", x, myhash[x]]

PS:

As others already pointed out, there are other, arguably better, ways to perform "insert or update". Generally, exceptions are meant for, er, exceptions, i.e. conditions that shouldn't normally happen during course of execution. Your case is more of if sort, so I would either first use SELECT to check whether row already exists, or, rather, try UPDATE first and see the number of changed rows and perform INSERT if it is zero.

---

An alternate path to solve this problem uses Sequel::Model's find_or_create method:

find_or_create (cond, &block)

Like find but invokes create with given conditions when record does not exist. Unlike find in that the block used in this method is not passed to find, but instead is passed to create only if find does not return an object.