database/sql: is there a way to get non integer LastInsertId?

[jemeshsu]

I'm using github.com/lib/pq with Postgres 9.x. I'm using Postgres uuid type as the primary key. Postgres uuid is a 128bit type internally storing the standard UUID eg e1297b38-1452-49c4-b3af-fb26e57df275.

create extension "uuid-ossp";

create table product (product_id uuid primary key default uuid_generate_v4(), title text);

To insert new Product record in golang, first call NewProduct() to get a new *Product struct and then call Save():

type Product struct {

  Id string `sql:"product_id"`

  Title string `sql:"title"`

}

func NewProduct(title string) *Product {

  p := Product{}

  p.Id = ""

  p.Title = title

  

  return &p

}

func (p *Product) Save() error {

  if len(p.Id) > 0 {

         // do an update

  } else {

         // create new 

         result, err := dbConn.Exec(

      "INSERT INTO product (title) VALUES($1)", p.Title)

         if err != nil { return err }

         id, _ := result.LastInsertId()  // ****************************

         p.Id = id                              // ****************************

  }

  return nil

}

Inside *Product Save(), the sql Exec returns a type Result interface with LastInsertId() returning int64. There is no other interface method:

       // http://golang.org/pkg/database/sql/

       type Result interface {

             LastInsertId() (int64, error)

             RowsAffected() (int64, error)

       }

Inside *Product Save(), I need to retrieve the product_id uuid generated by Postgres after the INSERT, so that I can update the *Product struct.

How do I go about?

[Rodrigo Kochenburger]

Try dbConn.QueryRow("INSERT INTO product (title) VALUES ($1) RETURNING product_id", p.Title).Scan(&p).

Cheers

- RK

--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[jemeshsu]

It works! Not aware there is RETURNING in SQL stmt. Thanks. 

[Rodrigo Kochenburger]

It's actually a postgres specific statement.

http://www.postgresql.org/docs/9.2/static/sql-insert.html

- RK

[Kamil Kisiel]

In fact the pq driver doesn't support LastInsertId at all, the only way to get it is via the RETURNING statement. That's because the results you can get back are more complicated than can be expressed by a simple integer key.