Gorm: Insert NULL values issue with generated sql

[pjebs]

The generated sql seems to be not correct (eg '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 '

[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

[wilsontamarozzi]

To send null to the database, simply omit the field

Try this

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

db.Create(&tbl)

[pjebs]

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

[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.

[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"

[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.