在使用 Gorm 進(jìn)行 Postgres 數(shù)據(jù)庫查詢時，有時會遇到一個常見的問題：“從 Gorm 傳入查詢的 Postgres 數(shù)據(jù)類型不正確”。這個問題可能會導(dǎo)致查詢結(jié)果不準(zhǔn)確，給開發(fā)者帶來困擾。在本文中，php小編魚仔將為您解析這個問題的原因，并提供解決方案，幫助您正確處理數(shù)據(jù)類型，確保查詢結(jié)果的準(zhǔn)確性。
問題內(nèi)容
我正在嘗試在 api 中創(chuàng)建一個端點來創(chuàng)建公司。在公司模型中，我有一個 []string 用于存儲與允許用戶注冊的電子郵件相關(guān)的允許列出的域。
[]字符串最初是從數(shù)組的 json post 請求映射的，并在 postgres 中分配了 text[] 類型。
alloweddomains []string `gorm:"type:text[];default:null" json:"alloweddomains" binding:"required"`
登錄后復(fù)制
使用 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
}
}
登錄后復(fù)制
在實現(xiàn)這個過程中，我遇到了兩個問題。
首先，當(dāng) alloweddomains 包含單個字符串時，該值不會作為數(shù)組寫入 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"
登錄后復(fù)制
其次，當(dāng) json 數(shù)組包含 > 1 值時，寫入數(shù)據(jù)庫的類型為 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)
登錄后復(fù)制
當(dāng)我設(shè)置斷點并在序列化后分析 company 類型時，很明顯 alloweddomains 的類型是正確的。
我在這里缺少什么想法或者解決這個問題的最佳方法嗎？
解決方法
根據(jù) 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
}
}
登錄后復(fù)制
