业务上想通过 PostgreSQL Generated Columns 自动把 JSON 字段生成为新的列,如下所示,
type AdPlatformEventTrackReport struct {
// Your regular fields
Data datatypes.JSON `gorm:"type:jsonb;column:data"` // Explicit column name
// Generated column with explicit column name
TrackPayAmount float64 `gorm:"<-:false;column:track_pay_amount;type:float GENERATED ALWAYS AS ((data->>'track_pay_amount')::float) STORED"`
}
第一次执行迁移是可以正常创建列的,但是第二次执行就会报如下错误,
ERROR: syntax error at or near "GENERATED" (SQLSTATE 42601)
[0.821ms] [rows:0] ALTER TABLE "ad_platform_event_track_reports" ALTER COLUMN "track_pay_amount" TYPE float GENERATED ALWAYS AS ((data->>'track_pay_amount')::float) STORED USING "track_pay_amount"::float GENERATED ALWAYS AS ((data->>'track_pay_amount')::float) STORED
尝试了几次仍然无法解决,最后只能先用以下方式解决。
type AdPlatformEventTrackReport struct {
// Your regular fields
Data datatypes.JSON `gorm:"type:jsonb;column:data"` // Explicit column name
// Generated column with explicit column name
TrackPayAmount *float64 `json:"track_pay_amount" gorm:"->;-:migration;column:track_pay_amount"`
}
if err := db.Exec(
`ALTER TABLE "ad_platform_event_track_reports"
ADD COLUMN IF NOT EXISTS "track_pay_amount" float GENERATED ALWAYS AS ((data->>'track_pay_amount')::float) STORED;`,
).Error; err != nil {
return err
}