postgresql - How to get the rows affected by an update query and also the returned value from the query? - Stack Overflow

I'm updating a table with query

sqlStatement := `UPDATE "my_table" SET "column1" = $1 WHERE "column2" = $2 RETURNING "column3"`

row, err := db.ExecContext(ctx, sqlStatement, args...)
if err != nil {
  // handle error
}

rows, err := row.RowsAffected()

is there any way to get the rows affected as well as scan the returned value from update query?

I'm updating a table with query

sqlStatement := `UPDATE "my_table" SET "column1" = $1 WHERE "column2" = $2 RETURNING "column3"`

row, err := db.ExecContext(ctx, sqlStatement, args...)
if err != nil {
  // handle error
}

rows, err := row.RowsAffected()

is there any way to get the rows affected as well as scan the returned value from update query?

• postgresql
• go

Share Improve this question Follow edited Jan 15 at 6:33 DarkBee 15.5k88 gold badges7272 silver badges118118 bronze badges asked Jan 15 at 6:32 Kaushal Kaushal 8355 bronze badges 2

• 1 Can you explain what you mean with "the returned value" ? UPDATE doesn't return rows. – Volker Commented Jan 15 at 6:43
• Update returns sql.Result which will have the rows affected. but if you check my query i'm also returning a value by using RETURNING , i was asking if it is possible to get both rows affected and the value returned from the update query. – Kaushal Commented Jan 15 at 7:00

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

for PostgreSQL you can get results as query result like this:

sqlStatement := `UPDATE "my_table" SET "column1" = $1 WHERE "column2" = $2 RETURNING "column3"`

rows, err := db.QueryContext(ctx, sqlStatement, args...)
if err != nil {
  // handle error
}
rowsAffected:=0
defer rows.Close()
for rows.Next() {
    // Handle result set.
    rowsAffected++
}

log.Println(rowsAffected)

in other databases, to get output from dml statements which affect single row, normally(since go 1.9) we use this syntax as passed arg to statement:

sql.Named("Arg1", sql.Out{Dest: &outArg})

for your case:

sqlStatement := `UPDATE "my_table" SET "column1" = $1 WHERE "column2" = $2 RETURNING "column3" INTO $column3`

var column3 string
result, err := db.ExecContext(ctx, sqlStatement, args..., sql.Named("column3", sql.Out{Dest: &column3}))
if err != nil {
  // handle error
}

rowsAffected, err := result.RowsAffected()