How to get row value(s) back after db insert?-CodePudding

I am using Golang to insert data into a DB. basically my query looks like below

var cols = "(col1, col2, col3)"
var values = "($1, $2, $3)"
var query = fmt.Sprintf("INSERT INTO %s %s VALUES %s", myTable, cols, values)

res, err := db.Exec(query, thing.val1, thing.val2, thing.val3)

The only things available from res are lastInsertId and # of rows affected. But what I need is the rows affected. The reason being is that I insert data into a psql database which has an AUTOINCREMENT id column - so I want the data back with that.

For example - with Java hibernate I can do what this answer explains. I don't have to re-query the DB for the ID.

EDIT: I tried to use the lastInsertId method and got this error

LastInsertId is not supported by this driver

CodePudding user response：

Assuming you just want the auto-incremented value(s) in a column called id and this is an insert with the pq driver

var cols = "(col1, col2, col3)"
var values = "($1, $2, $3)"
var query = fmt.Sprintf(
    "INSERT INTO %s %s VALUES %s RETURNING id",
    myTable, cols, values,
)
var id int
if err := db.QueryRow(
    query,
    thing.val1, thing.val2, thing.val3,
).Scan(&id); err != nil {
    panic(err)
}
fmt.Println("ID: ", id)

For multiple inserts:

var cols = "(col1, col2, col3)"
var values = "($1, $2, $3),($4, $5, $6)"
var query = fmt.Sprintf(
    "INSERT INTO %s %s VALUES %s RETURNING id",
    myTable, cols, values,
)
var ids []int
rows, err := db.Query(
    query,
    thing.val1, thing.val2, thing.val3,
    thing.val4, thing.val5, thing.val6,
)
if err != nil {
    panic(err)
}
for rows.Next() {
    var id int
    if err := rows.Scan(&id); err != nil {
        panic(err)
    }
    ids = append(ids, id)
}
fmt.Println("IDs: ", ids)

CodePudding user response：

res.LastInsertId() is not supported in Postgres Driver. However, It is supported in MySQL Driver.

db.Exec() doesn't return last inserted id but db.QueryRow() does.

For better understanding you can refer this link.

Here, I added one example which might help you.

var id int
err := db.QueryRow("INSERT INTO user (name) VALUES ('John') RETURNING id").Scan(&id)
if err != nil {
...
}