Gorm How to make batch update using FROM (VALUES) ?

How to write this PostgreSQL query with gorm?

UPDATE nodes SET removed_at = _data.removed_at
FROM (VALUES ('1bc80b35-c53c-4773-a7ad-bbaf66d0e956', '2023-09-11 13:00:16.740'),('36fc4528-2e98-48c2-884b-bd02119b8d3a', '2023-09-10 13:00:16.740'))
AS _data (node_uid, removed_at)
WHERE nodes.uid = _data.node_uid

Without using Raw()

Comment From: anoruxylene

Hi! Please let me know if you still need help with this question.

Comment From: AlexeyBazhin

@anoruxylene hi! It's still relevant :)

Comment From: anoruxylene

@AlexeyBazhin One of the solutions to execute your PostgreSQL query without using Raw() might be the following one:

package main

import (
    "log"
    "time"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

const dsn = "YOUR_DNS"

type Node struct {
    UID       string `gorm:"primaryKey"` // string for the sake of simplicity
    RemovedAt time.Time
}

func (n *Node) TableName() string {
    return "nodes"
}

func main() {
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    fatalOnError("Failed to init gorm", err)

    nodes := []Node{
        {UID: "1bc80b35-c53c-4773-a7ad-bbaf66d0e956", RemovedAt: time.Now()},
        {UID: "36fc4528-2e98-48c2-884b-bd02119b8d3a", RemovedAt: time.Now().Add(-time.Hour)},
    }
    err = db.Save(&nodes).Error
    fatalOnError("Failed to update nodes", err)

    log.Println("Updated nodes!")
}

func fatalOnError(message string, err error) {
    if err != nil {
        log.Fatalln(message, err)
    }
}

I use Save() method to update existing nodes. This approach executes on conflict update under the hood.

Comment From: anoruxylene

Please let me know if you have any questions regarding the solution.

Comment From: AlexeyBazhin

@anoruxylene Thank you so much! But I got a bunch of other fields. One of them (not primaryKey) is uuid type. So i have an error "ERROR: invalid input syntax for type uuid: "" (SQLSTATE 22P02)". I presume that Save() method updates all fields of db record, which is not good for my case.

Comment From: anoruxylene

@AlexeyBazhin, Could you please clarify if nodes.uid is a primary or unique key?

Comment From: AlexeyBazhin

@anoruxylene primary varchar :) I was talking about another field (nodes.cluster_id) - not null foreign key of uuid type

Comment From: anoruxylene

@AlexeyBazhin In this case, let's try the following query instead of Save()

    res := db.Clauses(clause.OnConflict{
        Columns:   []clause.Column{{Name: "uid"}},
        DoUpdates: clause.AssignmentColumns([]string{"removed_at"}),
    }).Create(&nodes)
    fatalOnError("Failed to update nodes", res.Error)

Comment From: AlexeyBazhin

@anoruxylene Thanks, but already know about this one. Can't do this in my project.

Comment From: AlexeyBazhin

@anoruxylene I also thought about clause.Values too, but couldn't make an appropriate statement.

Comment From: anoruxylene

@AlexeyBazhin You should have mentioned it in the description of the issue. It could have saved us time. So, what is the reason you can't use the approach in your project? And what is the reason not to use Exec() in your case? Your clarifications are crucial for finding the best solution.

Comment From: jeevanragula

Any one able to find a solution for this in gorm? As a workaround, I have executed the query by preparing and running with exec method.

        query := `update %s.products AS p set score = v.score from (VALUES %s) AS v(id, score) where v.id = p.id`;
    var placeholders []string
    for id, score := range scoreMap {
        value := fmt.Sprintf("('%s', %d)", id, score)
        placeholders = append(placeholders, value)
    }

    query := fmt.Sprintf(query, schemaName, strings.Join(placeholders, ", "))
    result := c.db.Exec(query)