Gorm How to use OnConstraint correctly with Gorm

Your Question

How do we use OnConstraint correctly? The SQL statement that gorm generated with OnConstraint has syntax error

    result := t.DB.Orm.
        WithContext(ctx).Table(total.TableName()).Clauses(clause.OnConflict{
        OnConstraint: "totals_date",
        UpdateAll:    true,
    }).Create(&total)
    if err := db.HandlePgResult(result, "total repository create total"); err != nil {
        return nil, err
    }

{"elapsed":4807583,"error":"ERROR: syntax error at or near \"ON\" (SQLSTATE 42601)","message":"error executing sql","rowsAffected":0,"severity":"error","sql":"INSERT INTO \"totals\" (\"id\",\"created_at\",\"updated_at\",\"shop_id\",\"date\",\"cash\",\"credit\",\"gift_card\",\"custom\") VALUES ('dadeb539-eae2-4f4e-9bc5-9ebd67c83132','2023-07-20 22:48:41.869','2023-07-20 22:48:41.869','6f460f46-9683-4caa-9d93-e6acd104a646','2023-07-20 00:00:00',5,4,45,64) ON CONFLICT (\"id\") ON CONSTRAINT totals_date DO UPDATE SET \"updated_at\"='2023-07-20 22:48:41.869',\"shop_id\"=\"excluded\".\"shop_id\",\"date\"=\"excluded\".\"date\",\"cash\"=\"excluded\".\"cash\",\"credit\"=\"excluded\".\"credit\",\"gift_card\"=\"excluded\".\"gift_card\",\"custom\"=\"excluded\".\"custom\"","tag":"nails_backend","timestamp":"2023-07-20T15:48:41.872911-07:00"}

This is what gorm generated in SQL

INSERT INTO "totals" ("id","created_at","updated_at","shop_id","date","cash","credit","gift_card","custom")
VALUES ('dadeb539-eae2-4f4e-9bc5-9ebd67c83132','2023-07-20 22:48:41.869','2023-07-20 22:48:41.869','6f460f46-9683-4caa-9d93-e6acd104a646','2023-07-20 00:00:00',5,4,45,64)
ON CONFLICT ("id") ON CONSTRAINT totals_date 
DO UPDATE SET "updated_at"='2023-07-20 22:48:41.869',
"shop_id"="excluded"."shop_id","date"="excluded"."date",
"cash"="excluded"."cash","credit"="excluded"."credit",
"gift_card"="excluded"."gift_card","custom"="excluded"."custom"

Is there a way to get rid of the ("id") so that ON CONFLICT ("id") ON CONSTRAINT totals_date turns into ON CONFLICT ON CONSTRAINT totals_date

Comment From: solumna

This might be a little different from what you ask, but it you're sure about the SQL you're giving, you can send the Raw SQL https://gorm.io/docs/sql_builder.html

Comment From: jinzhu

Are you using MySQL? it doesn't this kind of SQL

Comment From: huydinhle

@jinzhu I am using postgres. Would this ON CONSTRAINT only work with mysql?