postgresql - 为什么空字符串值在 UPDATE 上不起作用？

Question

我正在使用sqlboiler从 SQL 生成 Go 模型，我遇到了以下问题。ON CONFLICT通过 sqlboiler 代码指定时：

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"),
)

我收到以下查询：

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

和以下错误：（X因为缩小的例子）

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

当我初始化Name并Alias使用注释代码时，一切正常。为什么我需要初始化它？为什么零值在这种情况下不起作用？

编辑

添加Device定义：

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)
}

标签： postgresqlgosqlboiler