sql：列索引 19 上的扫描错误，名称“L2Name”：不支持的扫描

Question

使用 Golang 和内置的数据库/sql 库和 postgres lib/pq 库，我试图从一个数据库中读取一些记录中有一些空值。代码可以编译，但是当我尝试运行它时出现以下错误： 

sql: Scan error on column index 19, name "L2Name": unsupported Scan, storing driver.Value type <nil> into type *string

我有这样的结构：

// Assets Info Dataset

type AssetInfo struct {

  Asset_id string

  Asset_name string

  Organisation_id string

  LastCheckIn string

  Asset_Status string

  Asset_latitude string

  Asset_longitude string

  Organisation_name string

  CurrentDevice_name string

  AssetActiveDeviceType string

  AssetSafetyTimer float32

  TemplateName string

  TemplateL2name string

  TemplateL2contact string

  TemplateL3name string

  TemplateL3contact string

  TemplateL4name string

  TemplateL4contact string

  TemplateEscalationNotes string

}

代码的输出没问题，我可以从数据库中获取我想要的所有数据。除了 SQL 错误也打印在控制台上。并且 L2Name 在这里不是空值，我可以在控制台上打印该值。所以不知道为什么显示类型错误？

Answer 1

最简单的修复方法是使用 COALESCE(templates.L2Name, '') 将可为空的列包装在您的 sql 语句中，  sqlstatement := "SELECT" +    " assets.ID, assets.Name, assets.LastCheckIn, assets.Status, assets.OffTimer," +    " assets.SafetyTimer, assets.HazardTimer, assets.HazardTimerStartedTime, assets.LastSignedOn," +    " assets.Latitude, assets.Longitude, assets.TemplateID, assets.ActiveDeviceType, assets.CurrentDeviceID," +    " assets.OffTimerTemp, assets.OrganisationID," +    " organisations.Name As OrganisationName," +    " devices.Label As CurrentDeviceName," +    " templates.Name As TemplateName, COALESCE(templates.L2Name, ''), COALESCE(templates.L2Contact, '')," +    " COALESCE(templates.L3Name, ''), COALESCE(templates.L3Contact, '')," +    " COALESCE(templates.L4Name, ''), COALESCE(templates.L4Contact, ''), templates.Note" +    " FROM assets" +    " LEFT JOIN organisations ON assets.OrganisationID = organisations.ID" +    " LEFT JOIN devices ON assets.CurrentDeviceID = devices.ID" +    " JOIN templates ON assets.TemplateID = templates.ID" +    " WHERE assets.Status != 'Not monitoring' AND assets.AssetStatus = 'Active' AND assets.Display != '0'"

Answer 2

对于可以为 NULL 的字段，您应该使用 sql.NullString 数据结构。