Golang/PostgreSQL - Custom LastInsertId
I’ve just stumbled upon a challenge, where I needed a query to return the ID of the last inserted row, and I was querying using ExecContext and the method LastInsertId() to retrieve the id:
1result, err := Db.ExecContext(ctx, `INSERT INTO tenants (name) VALUES ($1)`, "My Awesome, Inc.")
2
3if err != nil {
4 // do something with the error
5}
6
7id, _ := result.LastInsertId()
8
9fmt.Println(id) // 0
However, the id returned was always 0, despite the id type being set as a VARCHAR. After a quick digging I learned that unless the table’s id has been created with a SEQUENCE generator, PostgreSQL won’t return the id upon row insertion.
To overcome the challenge I’ve just described above, I’ve used the RETURNING clause, and replaced Go’s Db.ExecContext() with Db.QueryRowContext() to gain access to the modified row.
Take a look at the full script:
1func FindTenantById(ctx context.Context, id string) (*Tenant, error) {
2 tenant := &Tenant{}
3 // Use QueryRowContext instead of ExecContext() to perform an INSERT
4 row := Db.QueryRowContext(ctx, `INSERT INTO tenants (name) VALUES($1) RETURNING id`, id)
5 err := row.Scan(&tenant.Id, &tenant.Name)
6
7 if row.Err() == sql.ErrNoRows {
8 return nil, nil
9 }
10
11 if err != nil {
12 return nil, nil
13 }
14
15 return tenant, nil
16}That will be all for now, and don’t forget to take a look at the links I’ve listed under references.
Cheers.