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

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)

1: https://github.com/jmoiron/sqlx "jmoiron/sqlx"

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.

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)

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