postgresql 从Gorm传入查询的Postgres数据类型不正确

ca1c2owp 于 2023-04-11 发布在 PostgreSQL

关注(0)|答案(1)|浏览(216)

我正在尝试在我的API中创建一个端点，用于创建公司。在公司模型中，我有一个[]string，用于存储与允许用户注册的电子邮件相关的允许列出的域。
[]字符串最初是从数组中的JSON POST请求Map而来的，并在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是正确的类型。

你知道我错过了什么吗？或者解决这个问题的最佳方法是什么？

postgresql

来源：https://stackoverflow.com/questions/75980058/incorrect-data-types-for-postgres-being-passed-in-to-query-from-gorm

1条答案

按热度按时间

nc1teljy1#

根据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
    }
}

赞(0）回复(0）举报 2023-04-11

我来回答

postgresql 从Gorm传入查询的Postgres数据类型不正确

1条答案

相关问题

热门标签

最新问答