gopq

How can I check whether Psql successfully updated the record in Go

I use this driver to communicate with psql from Go. Now when I issue an update query, I have no possibility to know whether it actually updated anything (it can update 0 rows if such id is not present).

_, err := Db.Query("UPDATE tags SET name=$1 WHERE id=1", name)

I tried to investigate err variable (in the way the doc suggests for Insert statement):

if err == sql.ErrNoRows {
    ...
}

But even with non-existent id, err is still null.

I also tried to use QueryRow with returning clause:

id := 0
err := Db.QueryRow("UPDATE tags SET name=$1 WHERE id=1 RETURNING id", name).Scan(&id)

But this one fails to scan &id when id=1 is not present in the database.

So what is the canonical way to check whether my update updated anything?

Solution

  • Try using db.Exec() instead of db.Query() for queries that do not return results. Instead of returning a sql.Rows object (which doesn't have a way to check how many rows were affected), it returns a sql.Result object, which has a method RowsAffected() (int64, error). This returns the number of rows affected (inserted, deleted, updated) by any write operations in the query fed to the Exec() call.

    res, err := db.Exec(query, args...)
if err != nil {
    return err
}

n, err := res.RowsAffected()
if err != nil {
    return err
}
// do something with n

    Note that if your query doesn't affect any rows directly, but only does so via a subquery, the rows affected by the subquery will not be counted as rows affected for that method call.

    Also, as the method comment notes, this doesn't work for all database types, but I know for a fact it works with pq, as we're using that driver ourselves (and using the RowsAffected() method).

    Reference links: