nil does not set database record to NULL

Question

I am using gorm, and I have a nullable column gender which can be set either male or female, but I am not able to set it back to NULL using nil once the column is set with a value of either male or female

The following is my code. To simplify things a bit, I only include the fields related to my question

type User struct {
    gorm.Model

    Username    string       `gorm:"type:varchar(40);unique" json:"username"`
    NickName    string       `gorm:"type:varchar(32)" json:"nickname"`
    Gender      *string      `gorm:"type:enum('male', 'female');default:null" json:"gender"`
}

and then I update my gender to male:

db.Model(&User{}).Where(&User{
    Model: gorm.Model{ID: 1},
}).Update(
    &User{
        Gender:   utils.GetStringPtr("female"),
        NickName: "nick name",
    },
)

and then I tried to update the gender to NULL

db.Model(&User{}).Where(&User{
    Model: gorm.Model{ID: 1},
}).Update(
    &User{
        Gender:   nil,
        NickName: "nick name",
    },
)

However, the gender stays the same female, and it is NOT changed to NULL.

Any ideas how I can fix this?

Answer 1

from the official doc, I found this http://gorm.io/docs/update.html

// Update multiple attributes with `struct`, will only update those changed & non blank fields
db.Model(&user).Updates(User{Name: "hello", Age: 18})
//// UPDATE users SET name='hello', age=18, updated_at = '2013-11-17 21:34:10' WHERE id = 111;

// WARNING when update with struct, GORM will only update those fields that with non blank value
// For below Update, nothing will be updated as "", 0, false are blank values of their types
db.Model(&user).Updates(User{Name: "", Age: 0, Actived: false})

If I do:

db.Model(&user).Updates(map[string]interface{"gender": nil})

this can successfully updates the model column to NULL.

However, I realized this is kinda bad, because if I set a bool column to True, that means I will never be able to set it back to False using struct update. Is there any way to work around this ? What is the design purpose of this.

Answer 2

You must use null package.

for example :

type User struct {
    gorm.Model

    Username    string       `gorm:"type:varchar(40);unique" json:"username"`
    NickName    string       `gorm:"type:varchar(32)" json:"nickname"`
    Gender      null.String  `gorm:"type:enum('male', 'female');default:null" json:"gender"`
}

then for set element to null you must do this :

gender := null.StringFromPtr(nil)

from your code, you must do this:

db.Model(&User{}).Where(&User{
    Model: gorm.Model{ID: 1},
}).Update(
    &User{
        Gender:   null.StringFromPtr(nil),
        NickName: "nick name",
    },
)