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.

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) 

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