I have a table in postgres with a UUID field type that must be unique but can be null

with a table & model like this

CREATE TABLE IF NOT EXISTS asdf(
    id bigserial primary key,
    name varchar(255) NOT NULL,
    key uuid unique,
    created_at timestamptz,
    updated_at timestamptz
);

and the go model defined as

type Asdf struct {
    ID          uint64    `json:"id" gorm:"type:uuid;column:id"`
    Name        string    `json:"name" gorm:"column:name"`
    Key         uuid.UUID `json:"key" gorm:"column:key"`
    CreatedAt   time.Time `json:"created_at" gorm:"column:created_at"`
    UpdatedAt   time.Time `json:"updated_at" gorm:"column:updated_at"`
}

result := db.Connect().Create(asdf.Asdf{ID:123,Name:"This is the name"})

and prints the following sql query to the terminal

INSERT INTO "asdf" ("id","name","key","created_at","updated_at")
VALUES('123','This is the name','00000000-0000-0000-0000-000000000000','2022-04-27 03:41:49.338','2022-04-27 03:41:49.338')

it inserted the model into the database with 00000000-0000-0000-0000-000000000000as the key value instead of NULL

I also notice this happen with string type where it inserted an empty string '' instead of NULL

how do i make gorm insert NULL instead of zeros/empty string as the value ?

CodePudding user response：

Try changing your field's type to a pointer type :

type Asdf struct {
    ID          uint64     `json:"id" gorm:"type:uuid;column:id"`
    Name        string     `json:"name" gorm:"column:name"`
    Key         *uuid.UUID `json:"key" gorm:"column:key"`
    CreatedAt   time.Time  `json:"created_at" gorm:"column:created_at"`
    UpdatedAt   time.Time  `json:"updated_at" gorm:"column:updated_at"`
}

You would obviously have to adapt your go code too (e.g: check if record.Key != nil, access *record.Key instead of record.Key, etc ...)

I think gorm also honors the regular sql interfaces, so you may also try to define a custom type which implements :

• sql.Scanner to turn null into "" on sql -> go conversions,
• driver.Valuer (from the sql/driver package) to turn "" to null on go -> sql conversions.

I haven't tested it though, so you would have to try it yourself.