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)