0

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?

Althaf Hameez
  • 1,511
  • 1
  • 10
  • 18

2 Answers2

0

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

phoet
  • 18,688
  • 4
  • 46
  • 74
  • @db.exec_params("INSERT INTO table (url, timestamp) VALUES ($1, $2) WHERE NOT EXISTS (SELECT 1 FROM table WHERE url = $1)",[url,DateTime.now]) That's the line that is causing the error. 2) Yes you are right, and in my actual code table is replaced with a table name. I shall update my question to reflect that. – Althaf Hameez Jul 24 '13 at 10:43
  • `INSERT INTO` + `WHERE` does not make sense. do you want to make something like `INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';` ? – phoet Jul 24 '13 at 10:47
  • I'm looking at the 2nd solution in [this question on SO](http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql) – Althaf Hameez Jul 24 '13 at 11:02
  • yeah, but it uses a `SELECT` statement that you are missing before the where! – phoet Jul 24 '13 at 11:28
  • Good catch. That seemed to have slipped me and I've been persisting with having VALUES instead. Thanks! – Althaf Hameez Jul 24 '13 at 11:33
0

pseudo-code:

# transaction
query('BEGIN');

# find out if row already exists, and lock it if it does
result = query('SELECT * FROM crawled WHERE url = $1 FOR UPDATE', [url])

# row exists, so update it
if (result.rows > 0) {
    query('UPDATE crawled SET timestamp = $2 WHERE url = $1', [url, DateTime.now])
}

# row doesn't exist, insert
else {
    query('INSERT INTO crawled (url, timestamp) VALUES ($1, $2)', [url, DateTime.now])
}

# commit transaction
query('COMMIT');