Upsert in Postgres Ruby

Question

我已经看到了处理这个问题的其他问题，我知道PostgreSQL没有内置的upstart，必须使用两种方法。这是我在Ruby中使用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])

然而，当我运行这个时，我得到了一个语法错误

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

我的错误在哪里？

Answer 1

仅从您的示例来看，我就有两个问题。

1. 哪一行导致了错误？

2. table应该被替换为表名，不是吗？http://www.postgresql.org/docs/8.2/static/sql-insert.html

Answer 2

伪代码：

# 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');