Go: How to get last insert id on Postgresql with NamedExec()

Question

I use jmoiron/sqlx library for communicating with my PostgreSql server in my Go apps. Somewhere on my apps i have this following code:

sqlQuery := `
    INSERT INTO table_to_insert  (
        code,
        status,
        create_time,
        create_by
    ) VALUES (
        '',
        0,
        CURRENT_TIMESTAMP,
        0
    ) RETURNING id
`

datas, err := tx.NamedExec(sqlQuery, structToInsert)

Question: how can i get the last insert id using the return from tx.NamedExec()? I've tried datas.LastInsertId() but its always return 0.

Note: im sure the insert to postgres is success.

Busra

Answer 1

The reason for this is because PostgreSQL does not return you the last inserted id. This is because last inserted id is available only if you create a new row in a table that uses a sequence.

If you actually insert a row in the table where a sequence is assigned, you have to use RETURNING clause. Something like this: INSERT INTO table (name) VALUES("val") RETURNING id".

I am not sure about your driver, but in pq you will do this in the following way:

lastInsertId := 0
err = db.QueryRow("INSERT INTO brands (name) VALUES($1) RETURNING id", name).Scan(&lastInsertId)

Oller

What if the ID is a UUID, generated withing the database? – Aida

Answer 2

resp.LastInsertID() only (typically) works with mySQL, and only works for integer IDs: https://golang.org/pkg/database/sql/#Result

Note that since you're using sqlx (by the use of NamedExec) you'll want to instead use tx.Get to exec the query and capture the return value:

// id should match the type of your ID 
// e.g. int64 for a bigserial column, or string for a uuid
var id string
resp, err := tx.Get(&id, query, v1, v2, v3)

See this relevant discussion on the sqlx GitHub repo: https://github.com/jmoiron/sqlx/issues/154#issuecomment-148216948

Lowdown