LastInsertId method returns 0?

[vendion]

I don't know if this driver implements the LastInsertId method of the Result type of the database/sql package or not but when I tried to use it I got back a 0 but in Postgres I could see the last ID was 98.

The way that I tried it out is as follows:

result, err := db.Exec("insert into commands (command,slot) values ($1,$2)", incoming.Command, incoming.Slot)
if err != nil {
    l.Printf("Error inserting into the commands table: %v\n", err)
    return
}

id, _ := result.LastInsertId()
fmt.Println(id) //Prints 0

[lxn]

This indeed isn't supported. The main obstacle is to get at the name of the primary key sequence.

[betamos]

Subscribing, very useful

[temoto]

If you use not ancient PostgreSQL, you can do insert into <table> ... returning <id-column> and read it like normal select statement, e.g. Scan().

[lxn]

Yes, returning is nice, but to implement LastInsertId, how would we know the exact name of the id column without guessing?

[temoto]

I guess there is no way without fetching table structure. And even then you can't do anything for table like id1 serial, id2 serial, primary key (id1, id2).

Basically, LastInsertId interface is not as generic as SQL standard. That's why i'm suggesting him to use other way to get inserted ids.

[brianoh]

I'm using "SELECT LASTVAL()" which appears to work OK, and alternatively a query using eg. " RETURNING iKey" appears to work OK.