Your Question

How do I achieve a query like below with this library?

INSERT INTO "users" ("user_id", "username", "name","created_at","updated_at") 
  VALUES 
    (33,'awesomeusername', NULL,'2023-08-09 08:42:47.555','2023-08-09 08:42:47.555') 
    ON CONFLICT (LOWER("username")) 
    DO UPDATE SET "user_id"=33 RETURNING "id"

Background

There is an index on table users created with below command: CREATE UNIQUE INDEX IF NOT EXISTS idx_uniq_lowercase_username ON users(LOWER(username));

Source Code(sensitive text has been removed) snippet looks like this:

user.UserID = 33
user.Username = "awesomeusername"
err = tx.Table("users").Clauses(clause.OnConflict{
    Columns:   []clause.Column{{Name: "username"}},
    DoUpdates: []clause.Assignment{{Column: clause.Column{Name: "user_id"}, Value: user.UserID}},
}).Create(&user).Error

Query Generated by above code is as below:

INSERT INTO "users" ("user_id", "username", "name","created_at","updated_at") 
  VALUES 
    (33,'awesomeusername', NULL,'2023-08-09 08:42:47.555','2023-08-09 08:42:47.555') 
    ON CONFLICT ("username") 
    DO UPDATE SET "user_id"=33 RETURNING "id"

Query fails with error below(which is obvious I guess):

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification (SQLSTATE 42P10)

The document you expected this should be explained

Official Document

Expected answer

Example code snippet to achieve expected query above

Comment From: keyvangholami

To write the query using GORM, you may need to use raw SQL expressions, as it seems like the GORM may not support this specific use case directly. You could write this using GORM's raw SQL execution functionality:

sql := `INSERT INTO "users" ("user_id", "username", "name","created_at","updated_at") 
        VALUES 
        (?, ?, NULL, ?, ?) 
        ON CONFLICT (LOWER("username")) 
        DO UPDATE SET "user_id"=? RETURNING "id"`

var id int64
err := tx.Raw(sql, 33, "awesomeusername", "2023-08-09 08:42:47.555", "2023-08-09 08:42:47.555", 33).Scan(&id).Error
if err != nil {
    // Handle error
}

Comment From: aniketdivekar

Thank you @keyvangholami. I did fallback to approach you shared. But I wanted to know if this is possible or is in plan or not for future release.