Postgresql Has-Many relationship insert same id error

user_11802172 0
Viewed 200

I have 2 structs in has-many relationship like this:

type Domain struct {
    ID     uint    `gorm:"primaryKey;type:serial"`
    Emails []Email `gorm:"foreignKey:ID" "column:emails"`
}

type Email struct {
    ID      uint   `gorm:"primaryKey;type:serial"`
    Address string `gorm:"column:address"`
}

I follow document of gorm here: https://gorm.io/docs/associations.html#Association-Mode

As far as I understand, when we insert a Domain record, gorm had insert array email ([]Email) into database first, I met a problem like this when try to insert into database:

var domain models.Domain
var emails []models.Email
for key, _ := range Output {
  emails = append(emails, models.Email{Address: key})
}
domain = models.Domain{
  Emails: emails,
}
dataAccess.Domain.Insert(domain) // db.Create(&domain)

And this is output in my console:

2020/09/10 10:37:46 /Users/tho/go/src/txxxx/exxxx/dataAccess/domains.go:18 ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time (SQLSTATE 21000)
[362.162ms] [rows:0] INSERT INTO "emails" ("address","id") VALUES ('info@example.com',2),('d.apple@example.com',2) ON CONFLICT ("id") DO UPDATE SET "id"="excluded"."id" RETURNING "id"

2020/09/10 10:37:46 /Users/tho/go/src/txxxx/exxxx/dataAccess/domains.go:18 ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time (SQLSTATE 21000)
[1078.499ms] [rows:1] INSERT INTO "domains" DEFAULT VALUES RETURNING "id"
ERRO[0050] Err: ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time (SQLSTATE 21000)

Gorm insert 2 emails with the same id so the postresql error show like above. How can i avoid same id because it is primaryKey and already serial (autoIncrement).

go-gorm go postgresql
1 Answers

You need change

Emails []Email `gorm:"foreignKey:ID" "column:emails"`

to

Emails []Email `gorm:"foreignKey:DomainId" "column:domain_id"`

And add

DomainId uint   `gorm:"column:domain_id"`

in type Email struct

Try:

type Domain struct {
    ID     uint    `gorm:"primaryKey;type:serial"`
    Domain string  `gorm:"column:domain"`
    Emails []Email `gorm:"foreignKey:DomainId" "column:domain_id"`
}

type Email struct {
    ID       uint   `gorm:"primaryKey;type:serial"`
    Address  string `gorm:"column:address"`
    DomainId uint   `gorm:"column:domain_id"`
}
user_124090300
Related