Postgres last insert id

[noonien]

There does not seem to be a way of getting the record id of the last inserted record(s).

The correct way would be to append "RETURNING id" to the query, and then scan the ids, however, I don't see how that's possible currently.

Also, when using InsertBuilder.Record, an id field is only selected when its type is int64, why not allow any kind of integers?

[taylorchu]

Appending "RETURNING id" works if the table has the column id. If the driver has last insert id available, dbr will use it.

Because https://golang.org/pkg/database/sql/driver/#RowsAffected.LastInsertId. I try to keep it simple, but all kinds of integer is possible. It will be the library users' responsibilities to ensure the id int types can hold the returned insert id. Do you have a specific use case for this?

[noonien]

pq does not support LastInsertId().

You are correct, the column might not be named "id", but the name can be extracted from InsertBuilder.RecordID.

[taylorchu]

You are welcomed to try this one: https://github.com/gocraft/dbr/pull/30/files.

[noonien]

It looks kinda hacky and it does not support multiple records. This logic should be somewhere inside the dialect handler.
I have no better option right now though, I currently use this function as a workaround:

func execInsertGetIDs(r dbr.SessionRunner, b *dbr.InsertBuilder, ids []*int) error {
    var rids []int
    q, vals := b.ToSql()
    _, err := r.SelectBySql(q+" RETURNING \"id\"", vals...).Load(&rids)
    if err != nil {
        return err
    }

    for i, id := range rids {
        *ids[i] = id
    }

    return nil
}

[taylorchu]

dbr tries to provide the minimal abstraction to hide database differences. Although batch auto-inject for postgres is easy with “RETURNING”, it is still hard for mysql (mysql, as far as I know, only has last insert id for this feature).

There are a few obvious choices for dbr:

• support batch insert with auto-inject: for mysql, we need to create a transaction in the library and insert record one by one so that we can use last insert id.
• support single insert with auto-inject: create transaction outside dbr.

I prefer the second one.

[tyler-smith]

I think we'd love to have this work, but I don't think any of us have time right now to address it. If anybody has thoughts on how to best handle this I'd love to open up a discussion here.

[orian]

I've been poking around returning LastInsertID. Not sure how, but in some circumstances the LastInsertID works just fine (let's say >30%).

At this point I don't see an easy way of getting an ID (it's not guaranteed that the ID even exist), thus one must use RETURNING column0 [, columnX].

I've hacked a bit around and added Return method to InsertBuilder/InsertStmt and copied a Load* from SelectBuilder as RETURNING may in fact return all columns.

The branch is here: https://github.com/orian/dbr/tree/add-returning

About using auto-inject: in many cases the dbr doesn't know primary key field and in some cases the primary key may not be a single column. It would cause the error to get returned.

[austintaylor]

There is a way to do this in MySQL, but it has a couple of caveats.

When inserting multiple rows, the value of LastInsertId will be the auto increment value of the first row inserted. The auto increment values for the other rows will be consecutive. So you can loop through the number of rows inserted and increment by the value of @@auto_increment_increment to get the id of each row.

We are using this technique in a couple of places to implement bulk actions.

Caveats:

1. This is based on the docs for InnoDB. I don't know how it works with other engines.

2. If innodb_autoinc_lock_mode is set to "interleaved" and "bulk inserts" are being executed on the table concurrently, then consecutive ids are not guaranteed. (Bulk inserts are defined as statements where the number of rows being inserted is not known up front.)

   If the only statements executing are “simple inserts” where the number of rows to be inserted is known ahead of time, there will be no gaps in the numbers generated for a single statement, except for “mixed-mode inserts”. However, when “bulk inserts” are executed, there may be gaps in the auto-increment values assigned by any given statement.

3. There are more complicated rules for "mixed-mode inserts" (inserts where you specify the id of some but not all the rows).

[dougEfresh]

I ran into this problem and although it is easily solved by just building your own SQL with the RETURNING keyword, I still wanted (and like) the InsertInto(...).Columns(...) syntax. So I have a diff that adds a Returning function on InsertBuilder struc :
master...dougEfresh:returning

[taylorchu]

This issue is resolved with latest master. Thanks!

[maxgardner]

Don't have any issues with the current implementation, but I wanted to add a quick code example because it took me a little while to piece this together. It looks like the ToSql() method referenced above doesn't exist anymore, and I didn't realize exactly what the Load method was for in the context of the Insert functionality. So in case this is helpful for anyone else who lands on this issue:

var id int64
err := session.Conn().
	InsertInto(<table-name>).
	Columns(<column-names>).
	Record(<struct-variable>).
	Returning("id").
	LoadContext(ctx, &id)
log.Printf("Last insert id is %d", id)

[taylorchu]

https://github.com/gocraft/dbr/blob/master/example_test.go#L8
it seems like a good example to be added in that file.
@maxgardner

[fajrulaulia]

There does not seem to be a way of getting the record id of the last inserted record(s).

The correct way would be to append "RETURNING id" to the query, and then scan the ids, however, I don't see how that's possible currently.

Also, when using InsertBuilder.Record, an id field is only selected when its type is int64, why not allow any kind of integers?

it's work, it's minimaize my code, thank you