Search code examples
rubypg

Upsert in Postgres Ruby

I have seen the other questions that deal with this issue and I know that PostgreSQL doesn't have an inbuilt upsert and must be done using 2 methods. This is the code I'm using in Ruby using the pg gem.

@db.exec_params("UPDATE crawled SET url = $1, timestamp = $2 WHERE url = $1",[url,DateTime.now])

@db.exec_params("INSERT INTO crawled (url, timestamp) VALUES ($1, $2) WHERE NOT EXISTS 
              (SELECT 1 FROM crawled WHERE url = $1)",[url,DateTime.now])

However when I run this I get a syntax error 

exec_params': ERROR:  syntax error at or near "WHERE" (PG::Error)
LINE 1: ...ERT INTO crawled (url, timestamp) VALUES ($1, $2) WHERE NOT ...

Where is my mistake?

Solution

  • just from looking at your example i have two questions.

    1. which of the lines is causing the error?

    2. table should have been replaced with a table-name, shouldnt it? http://www.postgresql.org/docs/8.2/static/sql-insert.html