I'm getting this error when I try to insert two or more register into for loop, the first one works fine but then the error appears, the database is new and I have had recreated many times

pq: duplicate key value violates unique constraint \"movements_pkey\

I have this model of my movements

type Movement struct {
    ID        int        `gorm:"primary_key" json:"id"`
    Amount    float32    `json:"amount"`
    FkType    *int       `gorm:"column:fk_type" json:"fk_type"`
    Type      Type       `gorm:"foreignkey:FkType"`
    FkIncome  int        `gorm:"column:fk_income" json:"fk_income"`
    Income    Incoming   `gorm:"foreignkey:FkIncome"`
    FkOrder   *int       `gorm:"column:fk_order" json:"fk_order,omitempty"`
    CreatedAt *time.Time `json:"created_at"`
}

And here is where I call the function to save in database (MovementCreate) into in a for loop, the first one always is created successfully but the second give me this error, where try to create a new record but apparently postgresql take the same last id

func (w *WebServices) CreateMovementMoneyOut(dataMovement *validators.MovementValidator) ([]models.IncomingResult, error) {
    nameFile := "Movement Services"
    dataWallet, err := w.wallet.FindWalletByCustomerID(dataMovement.FKCustomer)
    if err != nil {
        utils.LogService(nameFile, "Wallet Find", err.Error(), "error")
        return nil, err
    }
    if (dataWallet.Amount <= 0) || !(*dataMovement.FkType == 2) {
        utils.LogService(nameFile, "Wallet Update", "Insufficients funds", "error")
        return nil, fmt.Errorf("Insufficients funds")
    }
    incomingUpdate, err := w.incoming.UpdateIncomingsByFkWallet(&dataWallet.ID, *dataMovement.Amount)
    var movement models.Movement
    for _, val := range incomingUpdate {
        movement.Amount = val.Amount
        movement.FkIncome = val.ID
        movement.FkType = dataMovement.FkType
        movement.FkOrder = dataMovement.FkOrder
        _, err := w.movement.MovementCreate(&movement)

        if err != nil {
            utils.LogService(nameFile, "CreateMovementMoneyOut", err.Error(), "error")
            return nil, err
        }
    }
    newAmount, err := w.incoming.CalculateWalletAmount(&dataWallet.ID)
    if err != nil {
        utils.LogService(nameFile, "Wallet Update", err.Error(), "error")
    }
    _, err = w.wallet.WalletUpdateAmount(dataWallet, newAmount)
    if err != nil {
        utils.LogService(nameFile, "Wallet Update", err.Error(), "error")
        return nil, err
    }
    return incomingUpdate, nil
}

Here when save on database

func (s *WalletService) MovementCreate(data *Movement) (*Movement, error) {
  result := s.Create(data)
  return data, result.Error
}

The complete response of the error in console is this

2021/12/08 20:57:18 /usr/src/app/pkg/datalayers/models/movement.go:45 pq: duplicate key value violates unique constraint "movements_pkey"
[1.249ms] [rows:0] INSERT INTO "movements" ("amount","fk_type","fk_income","fk_order","created_at","id") VALUES (1000.000000,2,7,1,'2021-12-08 20:57:16.173',16) RETURNING "id"

16 is the last id created successfully in the first iteration in the loop, and the next iteration try with the same id but the correct data of the new record

CodePudding user response：

If you are using gorm V2 then replace primary_key to primaryKey and also add autoIncrement by default it will be true. If you want you can add Unique constraints it's upto you.

type Movement struct {
    ID        uint64     `gorm:"primaryKey;autoIncrement" json:"id"` //Change it
    Amount    float32    `json:"amount"`
    FkType    *int       `gorm:"column:fk_type" json:"fk_type"`
    Type      Type       `gorm:"foreignkey:FkType"`
    FkIncome  int        `gorm:"column:fk_income" json:"fk_income"`
    Income    Incoming   `gorm:"foreignkey:FkIncome"`
    FkOrder   *int       `gorm:"column:fk_order" json:"fk_order,omitempty"`
    CreatedAt *time.Time `json:"created_at"`
}

For each set of data you need to create an object. Add this var movement models.Movement inside the for loop.

And you don't need to mention movement.ID by default it will be handled by gorm very nicely.

    for _, val := range incomingUpdate {
    var movement models.Movement             //Update it
    movement.Amount = val.Amount
    movement.FkIncome = val.ID
    movement.FkType = dataMovement.FkType
    movement.FkOrder = dataMovement.FkOrder
    _, err := w.movement.MovementCreate(&movement)

    if err != nil {
        utils.LogService(nameFile, "CreateMovementMoneyOut", err.Error(), "error")
        return nil, err
    }
}