Postgres 数据库中使用 Gorm 传入的查询数据类型错误

本篇文章主要是结合我之前面试的各种经历和实战开发中遇到的问题解决经验整理的，希望这篇《Postgres 数据库中使用 Gorm 传入的查询数据类型错误》对你有很大帮助！欢迎收藏，分享给更多的需要的朋友学习~

我正在尝试在 api 中创建一个端点来创建公司。在公司模型中，我有一个 []string 用于存储与允许用户注册的电子邮件相关的允许列出的域。

[]字符串最初是从数组的 json post 请求映射的，并在 postgres 中分配了 text[] 类型。

alloweddomains        []string `gorm:"type:text[];default:null" json:"alloweddomains" binding:"required"`

使用 create() 的完整模型

// company is the primary struct type for companies
type company struct {
    common.base
    name                  string   `gorm:"unique;default:not null" json:"name" binding:"required"`
    primarycontactname    string   `gorm:"unique;default:not null" json:"primarycontactname" binding:"required"`
    primarycontactemail   string   `gorm:"unique;default:not null" json:"primarycontactemail" binding:"required"`
    primarycontactphone   string   `gorm:"unique;default:not null" json:"primarycontactphone" binding:"required"`
    secondarycontactname  string   `gorm:"unique;default:null" json:"secondarycontactname"`
    secondarycontactemail string   `gorm:"unique;default:null" json:"secondarycontactemail"`
    secondarycontactphone string   `gorm:"unique;default:null" json:"secondarycontactphone"`
    primarydomain         string   `gorm:"unique;default:not null" json:"primarydomain" binding:"required"`
    alloweddomains        []string `gorm:"type:text[];default:null" json:"alloweddomains" binding:"required"`
    mfaenabled            bool     `gorm:"not null" json:"mfaenabled" binding:"required"`
    isvalidated           bool     `gorm:"not null"`
}

func (c *company) create() error {
    if result := common.db.create(c); result.error != nil {
        log.printf("error creating company: %s", c.name)
        return result.error
    } else {
        log.printf("successfully created company: %s", c.name)
        return nil
    }
}

在实现这个过程中，我遇到了两个问题。

首先，当 alloweddomains 包含单个字符串时，该值不会作为数组写入 postgres，而是作为单个字符串写入。

api               | 2023/04/10 19:05:50 /go/src/api/company/model.go:25 error: malformed array literal: "website.co.uk" (sqlstate 22p02)
api               | [2.006ms] [rows:0] insert into "companies" ("created_at","updated_at","deleted_at","name","primary_contact_name","primary_contact_email","primary_contact_phone","primary_domain","mfa_enabled","is_validated","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains") values ('2023-04-10 19:05:50.551','2023-04-10 19:05:50.551',null,'foo company ltd.','foo','bar','00000000000','website.com',true,false,'foo2','bar2','11111111111',('website.co.uk')) returning "id","uuid","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains"
api               | [gin] 2023/04/10 - 19:05:50 | 500 |    3.043083ms |      172.21.0.1 | post     "/api/company/register"
api               | 2023/04/10 19:05:50 error creating company: foo company ltd.
api               | 2023/04/10 19:05:50 error: malformed array literal: "website.co.uk" (sqlstate 22p02)
postgres          | 2023-04-10 19:06:35.523 utc [19] error:  column "allowed_domains" is of type text[] but expression is of type record at character 336
postgres          | 2023-04-10 19:06:35.523 utc [19] hint:  you will need to rewrite or cast the expression.
postgres          | 2023-04-10 19:06:35.523 utc [19] statement:  insert into "companies" ("created_at","updated_at","deleted_at","name","primary_contact_name","primary_contact_email","primary_contact_phone","primary_domain","mfa_enabled","is_validated","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains") values ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,($14,$15)) returning "id","uuid","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains"

其次，当 json 数组包含 > 1 值时，写入数据库的类型为 record 类型，而不是 text[]

api               | 2023/04/10 19:06:35 /go/src/api/company/model.go:25 ERROR: column "allowed_domains" is of type text[] but expression is of type record (SQLSTATE 42804)
api               | [2.502ms] [rows:0] INSERT INTO "companies" ("created_at","updated_at","deleted_at","name","primary_contact_name","primary_contact_email","primary_contact_phone","primary_domain","mfa_enabled","is_validated","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains") VALUES ('2023-04-10 19:06:35.522','2023-04-10 19:06:35.522',NULL,'Foo Company Ltd.','Foo','Bar','00000000000','website.com',true,false,'Foo2','Bar2','11111111111',('website.co.uk','website.net')) RETURNING "id","uuid","secondary_contact_name","secondary_contact_email","secondary_contact_phone","allowed_domains"
api               | [GIN] 2023/04/10 - 19:06:35 | 500 |    3.256334ms |      172.21.0.1 | POST     "/api/company/register"
api               | 2023/04/10 19:06:35 Error creating company: Foo Company Ltd.
api               | 2023/04/10 19:06:35 ERROR: column "allowed_domains" is of type text[] but expression is of type record (SQLSTATE 42804)

当我设置断点并在序列化后分析 company 类型时，很明显 alloweddomains 的类型是正确的。

我在这里缺少什么想法或者解决这个问题的最佳方法吗？

正确答案

根据 mkopriva 的评论，解决方案是使用 pq 包，如下所示。

package company

import (
    "github.com/lib/pq"
    "log"
)

// Company is the primary struct type for companies
type Company struct {
    ...
AllowedDomains        pq.StringArray `gorm:"type:text[];default:NULL" json:"allowedDomains" binding:"required"`
}

func (c *Company) Create() error {
    a := pq.StringArray{}
    if c.AllowedDomains != nil && len(c.AllowedDomains) > 0 {
        for _, v := range c.AllowedDomains {
            a = append(a, v)
        }
        c.AllowedDomains = a
    }
    if result := common.Db.Create(c); result.Error != nil {
        log.Printf("Error creating company: %s", c.Name)
        return result.Error
    } else {
        log.Printf("Successfully created company: %s", c.Name)
        return nil
    }
}

文中关于的知识介绍，希望对你的学习有所帮助！若是受益匪浅，那就动动鼠标收藏这篇《Postgres 数据库中使用 Gorm 传入的查询数据类型错误》文章吧，也可关注golang学习网公众号了解相关技术文章。