not support LastInsertId()
astaxie opened this issue · 18 comments
in the database/sql
the result has define
type Result
type Result interface {
LastInsertId() (int64, error)
RowsAffected() (int64, error)
}
but the pg drive don't support LastInsertId()
Correct. Postgres does not automatically return the last insert id, because it would be wrong to assume you're always using a sequence. You need to use the RETURNING keyword in your insert to get this information from postgres.
Example:
var id int
err := db.QueryRow("INSERT INTO user (name) VALUES ('John') RETURNING id").Scan(&id)
if err != nil {
...
}
More information here:
http://www.postgresql.org/docs/8.2/static/sql-insert.html
Could you please kindly put this on the home page? Took me some time to find this piece of valuable information, someone from mysql may also get similar issue.
Maybe we should panic in that method?
On Monday, April 1, 2013, Isaiah Peng wrote:
Could you please kindly put this on the home page? Took me some time to
find this piece of valuable information, someone from mysql may also get
similar issue.—
Reply to this email directly or view it on GitHubhttps://github.com//issues/24#issuecomment-15739654
.
I think a panic with a message about using RETURNING would be appropriate, but I'm not sure how that would affect other libraries which may try to use database/sql in a driver-agnostic fashion. Might be worth asking bradfitz on golang-dev
Quite personally, I thought the returned error message was clever. It
didn't take me long to figure out that it wasn't supported. I am compelled
to ask how OP is doing error handling?
On Apr 1, 2013 10:27 PM, "Kamil Kisiel" notifications@github.com wrote:
I think a panic with a message about using RETURNING would be appropriate,
but I'm not sure how that would affect other libraries which may try to use
database/sql in a driver-agnostic fashion. Might be worth asking bradfitz
on golang-dev—
Reply to this email directly or view it on GitHubhttps://github.com//issues/24#issuecomment-15752137
.
hm yes, didn't realize it already returned an error. I agree that's enough, people should be checking those.
How do I get the last ID from a prepared statement in a transaction?
sql := `
insert into [table] (col1, col2, col3, col4, col5, col6)
values ($1, $2, $3, $4, $5, $6) RETURNING id;
`
stmt, err := db.Prepare(sql)
if err != nil {
panic(err)
}
res, err := stmt.Exec(data1, data2, data3, data4, data5, data6)
if err != nil {
panic(err)
} else {
ID, err = res.LastInsertId()
if err != nil {
panic(err)
}
}
Of course I'm getting:
panic: no LastInsertId available
Everywhere I read, like above is to use QueryRow.
Which I tried and works fine.
What's the difference between Exec and QueryRow for this driver then?
QueryRow returns a result, and Exec does not.
Maybe we should some more basics (or a link) to the docs?
According to the INSERT documentation, the default behaviour is to return the oid and count if no returning clause is present? https://www.postgresql.org/docs/9.6/static/sql-insert.html. If this is so, it should be made available to sql.Result.
Using OIDs is discouraged in user tables, and users will not insert directly into system tables. Do you have a use case for returning OIDs? It seems like the behavior you propose, while technically correct, would be confusing at best.
Yes, I want to update a row, sometime after I insert it. The use case is logging HTTP requests. I insert the initial values, and after responding, I update the row with stuff like response code, bytes written etc.
@anacrolix but are you using OIDs for those IDs? That's the only ids that Postgres returns to the client on insert, but they're not something you should be using--from the official docs:
The use of OIDs in user tables is considered deprecated...
The right way to solve this is to use the RETURNING clause and specify what data you would like to receive about the inserted row.
You could update LastInsertId() to return the ID returned if the query has a RETURNING clause, else return -1 and an error. This would let people re-use code that uses LastInsertId() from other drivers, which is important from a compatibility point of view.
The error message for this is also not clear:
no LastInsertId available
That just sounds like I've done something wrong that means the driver couldn't get the last insert ID. It doesn't make it clear that it isn't supported at all.
@jwatte to do that pq would have to parse the query which introduces a ton of additional complexity and still doesn't handle cases like non-integer (e.g., uuid) identifiers.
@Timmmm good point, but I think that message comes from https://github.com/golang/go/blob/50bd1c4d4eb4fac8ddeb5f063c099daccfb71b26/src/database/sql/driver/driver.go#L472 not pq. Maybe file an issue upstream?
@uhoh-itsmaciek Ah right you are - I opened a pull request.