Why null string values do not work on UPDATE?

1.4k Views Asked by At

I'm using sqlboiler to generated Go models from SQL and I've came across the following problem. When specifying ON CONFLICT via sqlboiler code:

dev := models.Device{
    ID:     deviceID,
    Type:   null.StringFrom("device"),
    // Name:        null.StringFrom(""),
    // Alias:       null.StringFrom(""),
}
err = dev.Upsert(context.Background(),
    db,
    true,
    []string{"id"},
    boil.Whitelist("name", "alias", "type"),
    boil.Whitelist("id", "name", "alias", "type"),
)

I get the following query:

INSERT INTO "devices" ("id", "type") VALUES ($1,$2) ON CONFLICT ("id") DO UPDATE SET "type" = EXCLUDED."type","updated" = EXCLUDED."updated"
[60b16bd2890814e5 {device true} ]

and the following error: (X because of minified example)

sql: Scan error on column index X, name \"name\": converting NULL to string is unsupported'

When I initialize Name and Alias with the commented code it all works OK. Why do I need to initialize it? Why can't zero values work in this scenario?

EDIT

Adding Device definition:

import (
    "github.com/volatiletech/null"
)

// type StringArray []string
// type PropertyMap map[string]interface{}

// ...

type Device struct {
    ID          string             `boil:"id" json:"id" toml:"id" yaml:"id"`
    Type        null.String        `boil:"type" json:"type,omitempty" toml:"type" yaml:"type,omitempty"`
    Status      null.String        `boil:"status" json:"status,omitempty" toml:"status" yaml:"status,omitempty"`
    Name        null.String        `boil:"name" json:"name,omitempty" toml:"name" yaml:"name,omitempty"`
    Alias       null.String        `boil:"alias" json:"alias,omitempty" toml:"alias" yaml:"alias,omitempty"`
    Fleet       string             `boil:"fleet" json:"fleet" toml:"fleet" yaml:"fleet"`
    Hardware    string             `boil:"hardware" json:"hardware" toml:"hardware" yaml:"hardware"`
    DeviceToken string             `boil:"device_token" json:"device_token" toml:"device_token" yaml:"device_token"`
    Tags        types.StringArray  `boil:"tags" json:"tags,omitempty" toml:"tags" yaml:"tags,omitempty"`
    Properties  models.PropertyMap `boil:"properties" json:"properties,omitempty" toml:"properties" yaml:"properties,omitempty"`
    Created     time.Time          `boil:"created" json:"created" toml:"created" yaml:"created"`
    Updated     null.Time          `boil:"updated" json:"updated,omitempty" toml:"updated" yaml:"updated,omitempty"`

    R *deviceR `boil:"-" json:"-" toml:"-" yaml:"-"`
    L deviceL  `boil:"-" json:"-" toml:"-" yaml:"-"`
}

Upsert

// Generated
func (o *Device) Upsert(ctx context.Context, exec boil.ContextExecutor, updateOnConflict bool, conflictColumns []string, updateColumns, insertColumns boil.Columns) error {
    if o == nil {
        return errors.New("models: no devices provided for upsert")
    }

    if err := o.doBeforeUpsertHooks(ctx, exec); err != nil {
        return err
    }

    nzDefaults := queries.NonZeroDefaultSet(deviceColumnsWithDefault, o)

    // Build cache key in-line uglily - mysql vs psql problems
    buf := strmangle.GetBuffer()
    if updateOnConflict {
        buf.WriteByte('t')
    } else {
        buf.WriteByte('f')
    }
    buf.WriteByte('.')
    for _, c := range conflictColumns {
        buf.WriteString(c)
    }
    buf.WriteByte('.')
    buf.WriteString(strconv.Itoa(updateColumns.Kind))
    for _, c := range updateColumns.Cols {
        buf.WriteString(c)
    }
    buf.WriteByte('.')
    buf.WriteString(strconv.Itoa(insertColumns.Kind))
    for _, c := range insertColumns.Cols {
        buf.WriteString(c)
    }
    buf.WriteByte('.')
    for _, c := range nzDefaults {
        buf.WriteString(c)
    }
    key := buf.String()
    strmangle.PutBuffer(buf)

    deviceUpsertCacheMut.RLock()
    cache, cached := deviceUpsertCache[key]
    deviceUpsertCacheMut.RUnlock()

    var err error

    if !cached {
        insert, ret := insertColumns.InsertColumnSet(
            deviceAllColumns,
            deviceColumnsWithDefault,
            deviceColumnsWithoutDefault,
            nzDefaults,
        )
        update := updateColumns.UpdateColumnSet(
            deviceAllColumns,
            devicePrimaryKeyColumns,
        )

        if updateOnConflict && len(update) == 0 {
            return errors.New("models: unable to upsert devices, could not build update column list")
        }

        conflict := conflictColumns
        if len(conflict) == 0 {
            conflict = make([]string, len(devicePrimaryKeyColumns))
            copy(conflict, devicePrimaryKeyColumns)
        }
        cache.query = buildUpsertQueryPostgres(dialect, "\"devices\"", updateOnConflict, ret, update, conflict, insert)

        cache.valueMapping, err = queries.BindMapping(deviceType, deviceMapping, insert)
        if err != nil {
            return err
        }
        if len(ret) != 0 {
            cache.retMapping, err = queries.BindMapping(deviceType, deviceMapping, ret)
            if err != nil {
                return err
            }
        }
    }

    value := reflect.Indirect(reflect.ValueOf(o))
    vals := queries.ValuesFromMapping(value, cache.valueMapping)
    var returns []interface{}
    if len(cache.retMapping) != 0 {
        returns = queries.PtrsFromMapping(value, cache.retMapping)
    }

    if boil.IsDebug(ctx) {
        writer := boil.DebugWriterFrom(ctx)
        fmt.Fprintln(writer, cache.query)
        fmt.Fprintln(writer, vals)
    }
    if len(cache.retMapping) != 0 {
        err = exec.QueryRowContext(ctx, cache.query, vals...).Scan(returns...)
        if err == sql.ErrNoRows {
            err = nil // Postgres doesn't return anything when there's no update
        }
    } else {
        _, err = exec.ExecContext(ctx, cache.query, vals...)
    }
    if err != nil {
        return errors.Wrap(err, "models: unable to upsert devices")
    }

    if !cached {
        deviceUpsertCacheMut.Lock()
        deviceUpsertCache[key] = cache
        deviceUpsertCacheMut.Unlock()
    }

    return o.doAfterUpsertHooks(ctx, exec)
}
0

There are 0 best solutions below