golang null.String解码无法正常工作

Question

Trying to fix this problem i'm having with my api im building.

db:

DROP TABLE IF EXISTS contacts CASCADE;
CREATE TABLE IF NOT EXISTS contacts (
        uuid UUID UNIQUE PRIMARY KEY,
        first_name varchar(150),
);



DROP TABLE IF EXISTS workorders CASCADE;
CREATE TABLE IF NOT EXISTS workorders (
        uuid UUID UNIQUE PRIMARY KEY,
        work_date timestamp WITH time zone,
        requested_by UUID REFERENCES contacts (uuid) ON UPDATE CASCADE ON DELETE CASCADE,

);

struct:

https://gopkg.in/guregu/null.v3

type WorkorderNew struct {
    UUID               string      `json:"uuid"`
    WorkDate           null.Time   `json:"work_date"`
    RequestedBy        null.String `json:"requested_by"`
}

api code:

workorder := &models.WorkorderNew{}
if err := json.NewDecoder(r.Body).Decode(workorder); err != nil {
    log.Println("decoding fail", err)
}
// fmt.Println(NewUUID())
u2, err := uuid.NewV4()
if err != nil {
    log.Fatalf("failed to generate UUID: %v", err)
}

q := `
            INSERT
            INTO workorders
                (uuid,
                work_date,
                requested_by
                )
            VALUES
                ($1,$2,$3)
            RETURNING uuid;`

statement, err := global.DB.Prepare(q)
global.CheckDbErr(err)

fmt.Println("requested by", workorder.RequestedBy)

lastInsertID := ""
err = statement.QueryRow(
    u2,
    workorder.WorkDate,
    workorder.RequestedBy,
).Scan(&lastInsertID)
global.CheckDbErr(err)

json.NewEncoder(w).Encode(lastInsertID)

When I send an API request with null as the value it works as expected but when i try to send a "" as the value for the null.String or the null.Time it fails

works:

{  
   "work_date":"2016-12-16T19:00:00Z",
   "requested_by":null
}

not working:

{  
   "work_date":"2016-12-16T19:00:00Z",
   "requested_by":""
}

Basically when i call the QueryRow and save to database the workorder.RequestedBy value should be a null and not the "" value im getting thanks

Answer 1

If you want to treat empty strings as nulls you have at least two options.

"Extend" null.String:

type MyNullString struct {
    null.String
}

func (ns *MyNullString) UnmarshalJSON(data []byte) error {
    if string(data) == `""` {
        ns.Valid = false
        return nil
    }
    ns.String.UnmarshalJSON(data)
}

Or use NULLIF in the query:

INSERT INTO workorders (
    uuid
    , work_date
    , requested_by
) VALUES (
    $1
    , $2
    , NULLIF($3, '')
)
RETURNING uuid

---

Update:

To extend the null.Time you have to understand that the type of null.Time.Time is a struct. The builtin len function works on slices, arrays, pointers to arrays, maps, channels, and strings. Not structs. So in this case you can check the data argument, which is a byte slice, by converting it to a string and comparing it to a string that contains an empty string, i.e. it has two double quotes and nothing else.

type MyNullTime struct {
    null.Time
}

func (ns *MyNullTime) UnmarshalJSON(data []byte) error {
    if string(data) == `""` {
        ns.Valid = false
        return nil
    }
    return ns.Time.UnmarshalJSON(data)
}