New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

not support LastInsertId() #24

Closed
astaxie opened this Issue Jun 12, 2012 · 17 comments

Comments

Projects
None yet
@astaxie

astaxie commented Jun 12, 2012

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()

@bmizerany

This comment has been minimized.

Contributor

bmizerany commented Jun 12, 2012

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

@bmizerany bmizerany closed this Jun 12, 2012

@isaiah

This comment has been minimized.

isaiah commented Apr 1, 2013

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.

@bmizerany

This comment has been minimized.

Contributor

bmizerany commented Apr 2, 2013

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
.

@kisielk

This comment has been minimized.

Contributor

kisielk commented Apr 2, 2013

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

@jackmanlabs

This comment has been minimized.

jackmanlabs commented Apr 2, 2013

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
.

@kisielk

This comment has been minimized.

Contributor

kisielk commented Apr 2, 2013

hm yes, didn't realize it already returned an error. I agree that's enough, people should be checking those.

@shutej shutej referenced this issue Dec 20, 2014

Closed

RETURNING syntax #5

thermokarst pushed a commit to thermokarst/bactdb that referenced this issue Oct 29, 2015

yinshuwei added a commit to yinshuwei/osm that referenced this issue Apr 29, 2016

lib/pq#24
pg lastInsertId

rafaeljusto added a commit to rafaeljusto/atiradorfrequente that referenced this issue Sep 20, 2016

Corrige a estratégia de obtenção do ID
A biblioteca de PostgreSQL utilizada não tem suporte ao Result.LastInsertId()
[1], portanto vamos utilizar a sintaxe RETURNING conforme recomendado pela
própria comunidade [2].

[1] https://golang.org/pkg/database/sql/#Result
[2] lib/pq#24

rafaeljusto added a commit to rafaeljusto/atiradorfrequente that referenced this issue Sep 20, 2016

Corrige a estratégia de obtenção do ID
A biblioteca de PostgreSQL utilizada não tem suporte ao Result.LastInsertId()
[1], portanto vamos utilizar a sintaxe RETURNING conforme recomendado pela
própria comunidade [2].

[1] https://golang.org/pkg/database/sql/#Result
[2] lib/pq#24
@paulm17

This comment has been minimized.

paulm17 commented Oct 23, 2016

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?

@tamird

This comment has been minimized.

Collaborator

tamird commented Oct 23, 2016

QueryRow returns a result, and Exec does not.

@cbandy

This comment has been minimized.

Contributor

cbandy commented Oct 23, 2016

Maybe we should some more basics (or a link) to the docs?

marioidival added a commit to prest/prest that referenced this issue Jan 5, 2017

Add transactions in SQL operations
A PostgreSQL library does not support the result Result.LastInsertId()[1],
here we will use a RETURNING syntax as recommended by own community [2].

[1] https://golang.org/pkg/database/sql/#Result
[2] lib/pq#24

marioidival added a commit to prest/prest that referenced this issue Jan 5, 2017

Add transactions in SQL operations
A PostgreSQL library does not support the result
Result.LastInsertId()[1],
here we will use a RETURNING syntax as recommended by own community
[2].

[1] https://golang.org/pkg/database/sql/#Result
[2] lib/pq#24

marioidival added a commit to prest/prest that referenced this issue Jan 5, 2017

Add transactions in SQL operations
A PostgreSQL library does not support the result
Result.LastInsertId()[1],
here we will use a RETURNING syntax as recommended by own community
[2].

[1] https://golang.org/pkg/database/sql/#Result
[2] lib/pq#24
@anacrolix

This comment has been minimized.

anacrolix commented May 29, 2017

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.

@uhoh-itsmaciek

This comment has been minimized.

Contributor

uhoh-itsmaciek commented May 30, 2017

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.

@anacrolix

This comment has been minimized.

anacrolix commented May 31, 2017

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.

@uhoh-itsmaciek

This comment has been minimized.

Contributor

uhoh-itsmaciek commented May 31, 2017

@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.

avelino pushed a commit to prest/adapters that referenced this issue Jul 25, 2017

Add transactions in SQL operations
A PostgreSQL library does not support the result
Result.LastInsertId()[1],
here we will use a RETURNING syntax as recommended by own community
[2].

[1] https://golang.org/pkg/database/sql/#Result
[2] lib/pq#24

@dadleyy dadleyy referenced this issue Nov 14, 2017

Closed

postgres support #66

@jwatte

This comment has been minimized.

jwatte commented May 8, 2018

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.

@Timmmm

This comment has been minimized.

Timmmm commented Sep 29, 2018

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.

@uhoh-itsmaciek

This comment has been minimized.

Contributor

uhoh-itsmaciek commented Sep 29, 2018

@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?

@Timmmm

This comment has been minimized.

Timmmm commented Oct 3, 2018

@uhoh-itsmaciek Ah right you are - I opened a pull request.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment