英文:
Incorrect data types for Postgres being passed in to query from Gorm
问题
我正在尝试在我的API中创建一个用于创建公司的端点。在Company模型中，我有一个[]string用于存储与用户允许使用的电子邮件相关的允许列表域名。
[]string最初是从JSON POST请求中的数组映射而来，并在Postgres中分配了text[]类型。
在实现这个过程中，我遇到了两个问题。
首先，当AllowedDomains只包含一个字符串时，该值不会被写入Postgres作为数组，而是作为单个字符串。
其次，当JSON数组包含多个值时，写入数据库的类型是record而不是text[]。
当我设置断点并分析序列化后的company类型时，很明显AllowedDomains的类型是正确的。
你有什么建议或解决这个问题的最佳方法吗？
英文:
I am trying to create an endpoint within my API for creating companies. Within the Company model I have a []string for storing allow listed domains related to the emails users are allowed to use to sign up with.
The []string is initially mapped from a JSON POST request from an array and assigned the text[] type within Postgres.
AllowedDomains []string `gorm:"type:text[];default:NULL" json:"allowedDomains" binding:"required"`
Full model with 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
}
}
In implementing this I am encountering two problems however.
First when AllowedDomains contains a single string the value is not being written to Postgres as an array, but rather as a single string.
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"
Secondly, when the JSOn array contains > 1 values, the type being written to the database is of type record and not 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)
When I set a breakpoint and analyse the company type after serialization however, it's clear that AllowedDomains is of the correct type.
Any ideas what I'm missing here or the best way to resolve this?
答案1
得分: 1
根据mkopriva的评论，解决方案是使用以下方式使用pq包。
package company
import (
"github.com/lib/pq"
"log"
)
// Company是公司的主要结构类型
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("创建公司时出错：%s", c.Name)
return result.Error
} else {
log.Printf("成功创建公司：%s", c.Name)
return nil
}
}
英文:
As per the comment by mkopriva, the solution was to use the pq package as below.
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
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论