Sqlx: LastInsertId and postgres

[davisford]

Should this work? I know with PostgreSQL you need to add the RETURNING clause...

CREATE SEQUENCE foo_id_seq;
CREATE TABLE foo ( 
  id bigint PRIMARY KEY default nextval('foo_id_seq'::regclass),
  created timestamp with time zone NOT NULL,
  name varchar(255) NOT NULL
);

sql := `INSERT into foo (created, name) VALUES ($1, $2) RETURNING id`
result, err := db.Exec(sql, time.Now(), "somename")
if err {
  panic(err)
}
lastid, lastiderr := result.LastInsertId()
fmt.Println("LastId?", lastid, lastiderr) // prints: LastId? 0 no LastInsertId available

Seems like it works with lib/pq by using QueryRow

[mbaragiola]

Hello!

Quoting from https://godoc.org/github.com/lib/pq:

"pq does not support the LastInsertId() method of the Result type in database/sql. To return the identifier of an INSERT (or UPDATE or DELETE), use the Postgres RETURNING clause with a standard Query or QueryRow call".

Perhaps you misread the statement.

[davisford]

Hi, thanks -- I did miss that line about using Query or QueryRow -- thanks. I have it working now.

[tj]

I might be missing something but there's no Named version that returns a single row? That would be handy, little weird to have all Named queries and then for a few inserts switch to positional

[jmoiron]

There's no one-liner for it. NamedExec and NamedQuery came before PrepareNamed, and I decided to keep them mostly for convenience. Rather than cluttering up the DB, Tx, etc structs with Named variants of everything (since sqlx adds a couple new verbs as well like Select, Get, etc.), I decided to leave most of that on the NamedStmt. So you can do:

stmt, err := db.PrepareNamed("INSERT INTO person (name, email) VALUES (:name, :email) RETURNING id")
// check error
var id int
err = stmt.Get(&id, arg)

It isn't much more difficult to use an interface and define an all-in-one type function:

type namedPreparer interface {
    PrepareNamed(string) (*sqlx.NamedStmt, error)
}

func NamedGet(db namedPreparer, dest interface{}, query string, arg interface{}) error {
    stmt, err := db.PrepareNamed(query)
    if err != nil {
        return err
    }
    return stmt.Get(dest, arg)
}

FWIW in most drivers this is basically what happens when you use eg Query or QueryRow with bindvars + args, anyway.

[mwf]

I'd like to leave a note about the suggestion above.

There's no one-liner for it. NamedExec and NamedQuery came before PrepareNamed, and I decided to keep them mostly for convenience. Rather than cluttering up the DB, Tx, etc structs with Named variants of everything (since sqlx adds a couple new verbs as well like Select, Get, etc.), I decided to leave most of that on the NamedStmt. So you can do:

stmt, err := db.PrepareNamed("INSERT INTO person (name, email) VALUES (:name, :email) RETURNING id")
// check error
var id int
err = stmt.Get(&id, arg)

Actually there are situations when you don't want to create prepared statement as suggested.
For example, when your work with PgBouncer in Transaction pooling mode, when prepared statements are disabled.

So the most accurate suggestion is to use sql.Named like this:

query, args, err := sqlx.Named("INSERT INTO person (name, email) VALUES (:name, :email) RETURNING id", arg)
if err != nil {
    <handle error>
}
query = db.Rebind(query)
var id int
err = db.Get(&id, query, args...)
...