Pg: float64 with value 0 generates an SQL with NULL value instead of 0

[tapir]

I'm selecting a numeric field with a value 0 from a postgre 10 to a go type of float64. then I try to update the same row without changing anything and I get the error that the field is NULL whereas it shouldn't be. I've looked at the generated SQL and indeed instead of setting the value to "0" it tries to set it to NULL.

[siteshen]

There is a workaround:

type Storage struct {
    ID     int64
    SomeID int64 `sql:",notnull"`
}

[vmihailenco]

Use notnull as was suggested.

[tapir]

Why is it treating 0 as NULL though? What if I wanted to update a numeric field with value 0?

[vmihailenco]

Then use ,notnull. Readme says

All struct fields are nullable by default and zero values (empty string, 0, zero time) are marshalled as SQL NULL. sql:",notnull" tag is used to reverse this behaviour.

[5k3105]

I think this is bad behavior.
I have a Status table (simplified here):
(Id, UploadStart, UploadEnd, NumErrors)

Operation starts and Status record is inserted with UploadStart time.

If completed it will update UploadEnd and NumErrors on that same row (using Id).
If operation never completed, UploadEnd and NumErrors will be NULL because it was not updated.

This row needs to allow null in UploadEnd and NumErrors because it inserts as UploadStart only.
It also needs to allow zero in NumErrors because when (if) operation completes, UploadEnd and NumErrors will be updated. NumErrors as NULL or zero or number are all distinct values.

    stat := Statusrec{
        UploadStart: time.Now(),
    }
    _, err := db.Model(&stat).Insert()
    statusId = stat.Id /// used later for update

    stat := Statusrec{
        Id:        statusId,
        UploadEnd: time.Now(),
        NumErrors: num_errors,
    }
    _, err := db.Model(&stat).Column("upload_end", "num_errors").WherePK().Update()

For now, when NumErrors is updated as zero and db row says NULL, I believe this is false information.
Btw, thank you for this excellent library :)

Also, if I insert a record and some fields are empty strings, I would want them to be entered as NULL.

[mewwts]

I also find this pretty weird.

I'd want to be able to have both NULLs and 0s in an integer field. I don't understand how that's _not_ a common usecase?

[vmihailenco]

Use sql.NullInt64

[mewwts]

Thanks @vmihailenco!