Gorm Insert NULL values issue with generated sql

The generated sql seems to be not correct (eg '\<nil>') But in mysql surprisingly it still works.

My Model

type Table struct {
    Id             uint32         `gorm:"column:id; primary_key:yes"`
    CustomerId     uint64         `gorm:"column:customer_id;"`
    AllocatedGroup *string        `gorm:"column:allocated_group;"` //Nullable
    Session1       sql.NullString `gorm:"column:session_1;"`             //Nullable
    RegisteredTime *time.Time     `gorm:"column:registered_time;"` //Nullable
    Preferences    sql.NullString `gorm:"column:preferences;"`         //Nullable
    PushToken      string         `gorm:"column:push_token;"`
    Latitude       *float32       `gorm:"column:latitude;"`
    GatewayId      *uint32        `gorm:"column:gateway_id;"` //Nullable
}

My Code

tbl := Table{
        CustomerId:     123,
        AllocatedGroup: nil,
        Session1:       sql.NullString{},
        PushToken:      "abc",
        RegisteredTime: nil,
        GatewayId:      nil,
    }

db.Create(&tbl)

Generated SQL

INSERT INTO `table` 
(`customer_id`,`allocated_group`,`session_1`,`registered_time`,`preferences`,`push_token`,`latitude`,`gateway_id`) 
VALUES 
('123','<nil>',NULL,'<nil>',NULL,'abc','<nil>','2') 

You can see that Session1 is correctly NULL while AllocatedGroup is '\<nil>'

Comment From: YanshuoH

Hello, Why use *string instead of string ? Do you really need nil string assertion in your code?

https://dhdersch.github.io/golang/2016/01/23/golang-when-to-use-string-pointers.html

Comment From: wilsontamarozzi

To send null to the database, simply omit the field

Try this

tbl := Table{
    CustomerId:     123,
    PushToken:      "abc",
}

db.Create(&tbl)

Comment From: pjebs

@wilsontamarozzi Omitting the field is equivalent to the type's "zero value", which for pointers is exactly equivalent to nil.

Comment From: pjebs

@jinzhu I think I didn't explain myself clearly.

I was indicating that for sql.NullString, gorm correctly generates the sql: NULL. For a nil pointer such as *string or *float32, gorm incorrectly generates the sql: '<nil>'.

The generated sql is incorrect for nil pointers, despite the fact that it somehow works for MySQL.

Comment From: wilsontamarozzi

Sorry about my English.

To send null to the database you must hide the fields at the time of .create .save. In the database the field should have the default set to null.

Use a sql tag in the structure: sql: "default: null"

Comment From: jinzhu

Hi @pjebs

The generated SQL is just a formatted string for logging, not really the SQL run in database.

BTW, I have updated the logger, to fix the wrong formatted SQL, please update GORM.

Comment From: MrAkblt

Sorry to bring this topic back to life, I just wanted to point out an important detail that might have been missed.

You can use gorm:"default:NULL" but you should be careful that ​​"", 0 and false values will be written to the database as null and that might be something you don't want