how to handle INSERT on conflict in a has many association in GORM Create, ERROR (SQLSTATE 23505)

Question

I have two models as follows:

type OHLCV struct {
    gorm.Model
    Interval         string        `gorm:"uniqueIndex:idx_ohlcv"`
    Pair             string        `gorm:"uniqueIndex:idx_ohlcv"`
    OpenTime         time.Time     `gorm:"uniqueIndex:idx_ohlcv"`
    CloseTime        time.Time     `gorm:"uniqueIndex:idx_ohlcv"`
    Open             float64       `json:"open"`
    High             float64       `json:"high"`
    Low              float64       `json:"low"`
    Close            float64       `json:"close"`
    Volume           float64       `json:"volume"`
    QuoteAssetVolume float64       `json:"quoteAssetVolume"`
    NumberOfTrades   float64       `json:"numberOfTrades"`
    Calculations     []Calculation `gorm:"foreignKey:OhlcvRefer"`
}

and

type Calculation struct {
    gorm.Model
    OhlcvRefer uint   `gorm:"uniqueIndex:idx_calculation"`
    Key        string `gorm:"uniqueIndex:idx_calculation"`
    Config     string `gorm:"uniqueIndex:idx_calculation"`
    Value      float64
}

As you see both tables have unique indexes to prevent inserting duplicate data. The first table foreignKey is a part of the second table's unique index. The problem is how can I handle ON CONFLICT DO NOTHING behavior for both tables with a single GORM Create statement?

Before adding the Calculation association I was able handle CONFLICTS with

err = db.Clauses(clause.OnConflict{DoNothing: true,
        Columns: []clause.Column{{Name: "interval"}, {Name: "pair"}, {Name: "open_time"}, {Name: "close_time"}},
    }).Create(ohlcvs).Error

But now I get the following error:

ERROR: duplicate key value violates unique constraint "idx_calculation" (SQLSTATE 23505)

What I need is to DO NOTHING for the Calculation conflicts as well.

Answer 1

To achieve what you need, it should be enough to use the Unique index constraint on the two structs. Let's see how you can implement it.

package main

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

type User struct {
    Id    int
    Name  string `gorm:"uniqueIndex:idx_name"`
    Posts []Post
}

type Post struct {
    Id     int
    Title  string `gorm:"uniqueIndex:idx_title"`
    UserId int
}

func main() {
    dsn := "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        panic(err)
    }

    db.AutoMigrate(&Post{})
    db.AutoMigrate(&User{})

    db.Create(&User{
        Name: "john doe",
        Posts: []Post{
            {Title: "first"},
            {Title: "second"}, // to generate an error change to "first"
        },
    })
}

In this way, if you're entering duplicates value the db itself will block you. This is valid ono either on the users table and on the posts one. IMO, it's a very clean approach and you can be as flexible as you wish.
Let me know if this solves your issue or there is something else!