How to get uuid of last inserted row using sqlx in postgres

Question

I am inserting some data in table using SQLx like this

func (*RideRepositoryImpl) insert(entity interface{}, tx persistence.Transaction) (sql.Result, error) {
    ride := entity.(*model.Ride)

    placeHolders := repository.InsertPlaceholders(len(rideColumns))
    sql := fmt.Sprintf("INSERT INTO %s(%s) VALUES(%s)", TableName, strings.Join(Columns, ","), placeHolders)
    return tx.Exec(sql, ride.ID.String(), ride.DeviceIotID, ride.VehicleID.String(), ride.UserID.String(),ride.AdditionComments)
}

and calling this function like this

func (p *RideRepositoryImpl) Save(ride *model.Ride, tx persistence.Transaction) error {
    return repository.Save(ride, p.insert, tx)

Now I want to get UUID of saved record instantly after saving this record . Is there any clean way to do this instantly ?

mkopriva · Accepted Answer · 2021-04-11 19:23:10Z

2

PostgreSQL has the RETURNING clause for this.

Sometimes it is useful to obtain data from modified rows while they are being manipulated. The INSERT, UPDATE, and DELETE commands all have an optional RETURNING clause that supports this. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.

// add the RETURNING clause to your INSERT query
sql := fmt.Sprintf("INSERT INTO %s(%s) VALUES(%s) RETURNING <name_of_uuid_column>", TableName, strings.Join(Columns, ","), placeHolders)

// use QueryRow instead of Exec
row := tx.QueryRow(sql, ride.ID.String(), ride.DeviceIotID, ride.VehicleID.String(), ride.UserID.String(),ride.AdditionComments)

// scan the result of the query
var uuid string
if err := row.Scan(&uuid); err != nil {
    panic(err)
}

// ...

For additional INSERT-specific info related to RETURNING you can go to the INSERT documentation and search the page for "returning" with CTRL/CMD+F.

If, in addition, you need your function to still return an sql.Result value to satisfy some requirement, then you can return your own implementation.

var _ sql.Result = sqlresult{} // compiler check

type sqlresult struct { lastid, nrows int64 }

func (r sqlresult) LastInsertId() (int64, error) { return r.lastid, nil }
func (r sqlresult) RowsAffected() (int64, error) { return r.nrows, nil }

func (*RideRepositoryImpl) insert(entity interface{}, tx persistence.Transaction) (sql.Result, error) {
    ride := entity.(*model.Ride)

    placeHolders := repository.InsertPlaceholders(len(rideColumns))
    sql := fmt.Sprintf("INSERT INTO %s(%s) VALUES(%s) RETURNING <name_of_uuid_column>", TableName, strings.Join(Columns, ","), placeHolders)
    row := tx.QueryRow(sql, ride.ID.String(), ride.DeviceIotID, ride.VehicleID.String(), ride.UserID.String(),ride.AdditionComments)
    if err := row.Scan(&ride.<NameOfUUIDField>); err != nil {
        return nil, err
    }
    return sqlresult{0, 1}, nil
}

edited Apr 11, 2021 at 19:23

answered Apr 11, 2021 at 15:41

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Ninja Over a year ago

thanks for answer sir , there is one issue as you can see my Insert funciton I want to return (sql.Result, error) so in this case hoe I will return (sql.Result, error) ?

mkopriva Over a year ago

Why would you want to do that? What do you need it for? If you need it to satisfy an interface then just return a nil sql.Result.

Ninja Over a year ago

test have been written on basis of this returning . like its pre-implemented architecture for CRUD in app that I need (sql.Result, error) in return which is default returning of tx.Exec

mkopriva Over a year ago

@Ninja sql.Result is an interface, you can always return your own dummy implementation, very easy.

Ninja Over a year ago

I can't get same result from QueryRow somehow ? As It may change other scenarios for insertions

|

Collectives™ on Stack Overflow

How to get uuid of last inserted row using sqlx in postgres

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related