Update RETURNING with Gorm

In some situations it’s not obvious how to use the RETURNING SQL clause with Gorm and databases that support it, like CockroachDB.

The particular use case where I found it challenging is the following: I want to update multiple records with a WHERE clause, ORDER BY clause and LIMIT clause and immediately return the updated records without a second query.

The simplified CockroachDB table schema without indices and noise is:

  column_name | data_type | is_nullable | column_default  
--------------+-----------+-------------+----------------
  field1      | INT8      |    true     | NULL                                  
  field2      | INT8      |    true     | NULL                                  
  created_at  | INT8      |    true     | NULL

My initial attempts were as follows.

type Record struct {
	Field1    int
	Field2    int
	CreatedAt int64
}         

func update(field1 int, field2 int, limit int) ([]Record, error) {
    var records []Record
    err := db.Model(&records).
    	Table("table_name").
        Clauses(clause.Returning{}).
        Where("field1 = ?", field1).
    	Updates(map[string]interface{}{"field2":field2}).
    	Order("created_at ASC").
    	Limit(limit).
    	Error
    if err != nil {
        return nil, err
    }
    
    return records, nil
}

Gorm builds the SQL statement successfully, but for some reason doesn’t include the ORDER BY and LIMIT clauses, which are very important in my particular case. Ok, let’s try with a raw SQL statement.

func update(field1 int, field2 int, limit int) ([]Record, error) {
    var records []Record
    sql := `UPDATE table_name SET field2 = $1 WHERE field1 = $2 ORDER BY created_at ASC LIMIT $3 RETURNING *`
    if err := db.Model(&records).Table("table_name").Exec(sql, field1, field2, limit).Error; err != nil {
        return nil, err
    }
    return records, nil
}

Gorm executes the complete correct query, but I couldn’t find a way to return/populate the records slice - it was always empty (tried with different arrangements and examples from the docs). Without any deep investigation on why that happens, it seems that Gorm just ignores the RETURNING data when executing raw SQL statements.

What finally worked for me seems a bit like a hack, but it does the job. By using the ORDER BY and LIMIT clauses directly in the WHERE clause, the query works correctly as I need it.

func update(field1 int, field2 int, limit int) ([]Record, error) {
    var records []Record
    err := db.Model(&records).
    	Table("table_name").
    	Clauses(clause.Returning{}).
    	Where("field1 = $1 ORDER BY created_at LIMIT $2", field1, limit).
    	Updates(map[string]interface{}{"field2": field2}).
    	Error
    if err != nil {
        return nil, err
    }
    return records, nil

The examples use Updates with map[string]interface{} but the issue described here is not affected by that. Using struct or other ways for updating produces the same results.

note

It looks like PostgreSQL does not work with LIMIT when using a RETURNING clause so another solution could be used, like SELECT FOR UPDATE -> then update, and return the updated record.

Table of Contents