CODEWITHSUNDEEP
gobulkinsertgo-gorm
Anthony O
Anthony O

Reputation: 672

How can I use the gorm library to bulk upsert records into a postgres DB and return the IDs of those that are inserts and not updates?

Although this can be done with a raw query, I would like to bulk insert a small number of records and handle upsert conflicts through the gorm Create API. I need to return the IDs of records that are inserted and not updated.

        RETURNING CASE WHEN xmax = 0 THEN id ELSE NULL END

However, the library appears to limit the returning clause to contain only columns.

Rather than make another query to the DB, I'd prefer to do this in one round trip. Is there another usage of the API that would enable this use case through gorm, or must I resort to a raw query here?

This related question assumes that the existing records are populated before the upsert query. There is nothing on the returned structs to distinguish inserted from updated.

Upvotes: 2

Views: 48

Answers (1)

MJepbarov
MJepbarov

Reputation: 152

Gorm does not provide a method to directly return different data based on inserted or updated info of record. A raw query is necessary for this type of behavior.

type User struct {
    ID    uint   `gorm:"primaryKey"`
    Name  string
    Email string `gorm:"unique"`
}

create a slice of records to upsert

users := []User{
    {Name: "John Doe", Email: "[email protected]"},
    {Name: "Jane Smith", Email: "[email protected]"},
}

example:

var insertedIDs []uint

//  INSERT ... ON CONFLICT ... handles the upsert logic
query := `
    INSERT INTO users (name, email)
    VALUES ($1, $2), ($3, $4)
    ON CONFLICT(email) DO UPDATE
    SET name = EXCLUDED.name
    RETURNING id
`

// then execute a RAW query
rows, err := db.Raw(query, users[0].Name, users[0].Email, users[1].Name, users[1].Email).Rows()
if err != nil {
    log.Fatal(err)
}
defer rows.Close()

for rows.Next() {
    var id uint
    if err := rows.Scan(&id); err != nil {
        log.Fatal(err)
    }
    insertedIDs = append(insertedIDs, id)
}

if err := rows.Err(); err != nil {
    log.Fatal(err)
}

fmt.Println("Inserted IDs:", insertedIDs)

Upvotes: -1

Related Questions