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?

do you want to prevent from setting `null` ? or you can`t set null? — ttrasn, Aug 16 '20 at 06:12
@ttrasn, I can't set it to null, I would like to know how I can set it back to NULL — user2002692, Aug 16 '20 at 12:03

ttrasn · Answer 1 · 2020-08-17T03:52:51.317

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",
    },
)

edited Aug 17 '20 at 03:52

answered Aug 16 '20 at 06:10

ttrasn

4,322
4
26
43

I tried this solution, I updated my gender field to exactly you suggested, and tried to set gender to NULL by using `gender := null.StringFromPtr(nil)`, gender still **not** getting updated – user2002692 Aug 16 '20 at 12:06
I am using: `github.com/jinzhu/gorm v1.9.15` `gopkg.in/guregu/null.v4 v4.0.0` `go version go1.14.7 darwin/amd64` – user2002692 Aug 16 '20 at 15:02

score 2 · Answer 2 · answered Aug 16 '20 at 16:42

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.

I suggest you to prevent from using `interface{}`. Its not best solution for your code. — mr repo, Aug 17 '20 at 07:27
However according to doc, the zero value will not be updated using struct update, what is the best practice here? — user2002692, Aug 17 '20 at 08:14
see ttrasn's answer. https://stackoverflow.com/a/63433706/5902688 — mr repo, Aug 21 '20 at 05:34

score 0 · Answer 3 · answered Jul 14 '22 at 11:38

0

Use sql.Nullstring

example: Unable to use type string as sql.NullString

answered Jul 14 '22 at 11:38

Leroy A.

9
2

1

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/32246565) – Rohit Gupta Jul 19 '22 at 04:08
Good to have: If you can add example. – Yash Jagdale Jul 21 '22 at 15:22

Yiannis · Answer 4 · 2022-07-19T09:19:13.190

This does not work with the Updates command of GORM. If you configure it correctly, in the best case scenario you will have an empty string in the DB.

You need to use the Save method of GORM in order to store NULL in your DB.

If you are working on an API, this is a PUT method not a PATCH.

You also don't need an extra package for the struct. You can use

type User struct {
    gorm.Model

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

and have a function to transform the string to sql.NullString

func StringToSQLNullString(s string) sql.NullString {
    if s != "" {
        return sql.NullString{
            String: s,
            Valid:  true,
        }
    }
    return sql.NullString{}
}

In the end, you need to get your User from DB, to keep the CreatedAt property and then save it. Your code will be like this (For null, you don't need the gender)

updatedUser := &User{
  ID: 1,
  NickName: "nick name",
}

existingUser := &User{}
_ = db.First(existingUser, updatedUser.ID).Error

updatedUser.CreatedAt = existingUser.CreatedAt

_ = db.Save(updatedUser).Error

Including the gender, the struct is like this

updatedUser := &User{
  ID: 1,
  NickName: "nick name",
  Gender: StringToSQLNullString("male")
}

EDIT

Else, if you only want to update the gender your own answer is the way

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

nil does not set database record to NULL

4 Answers4