英文:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
问题
我正在尝试对名为feature_to_model的表执行upsert操作。然而，我遇到了以下错误：
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification (SQLSTATE 42P10)
以下是我的表规范：
CREATE TABLE IF NOT EXISTS feature_to_model (
training_job_id varchar NOT NULL,
feature_name varchar NOT NULL,
feature_set_name varchar NOT NULL,
model_name varchar NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
PRIMARY KEY (training_job_id, feature_name, feature_set_name)
我在这里使用Gorm来查询数据库，以下是我的函数调用：
func (s *store) UpsertFeatureToModel(f2m *model.FeatureToModel) (*model.FeatureToModel, error) {
result := s.db.Table(f2mTable).Clauses(clause.OnConflict{
UpdateAll: true,
}).Create(f2m)
if result.Error != nil {
return nil, result.Error
}
return f2m, nil
}
我漏掉了什么？我不能在任何索引上使用UNIQUE约束（training_job_id、feature_name、feature_set_name是索引），因为它们都不是唯一的。
英文:
I'm trying to perform upsert against a table named feature_to_model. However, I get the following error:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification (SQLSTATE 42P10)
Here's my table specifications:
CREATE TABLE IF NOT EXISTS feature_to_model (
training_job_id varchar NOT NULL,
feature_name varchar NOT NULL,
feature_set_name varchar NOT NULL,
model_name varchar NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
PRIMARY KEY (training_job_id, feature_name, feature_set_name)
I use Gorm to query the db here's my function call for that:
func (s *store) UpsertFeatureToModel(f2m *model.FeatureToModel) (*model.FeatureToModel, error) {
result := s.db.Table(f2mTable).Clauses(clause.OnConflict{
UpdateAll: true,
}).Create(f2m)
if result.Error != nil {
return nil, result.Error
}
return f2m, nil
}
What am I missing? I cannot use UNIQUE constraint on any of the indices (training_job_id, feature_name, feature_set_name are index), because none of them are unique
答案1
得分: 3
错误：在ON CONFLICT规范中没有匹配的唯一或排除约束
这是因为training_job_id是在FOREIGN KEY CONSTRAINT中引用的列，而不是索引。实际上，如果你希望它更快，可以考虑额外添加一个索引。根据ON CONFLICT的文档，你可能想要像这样创建表：
CREATE TABLE IF NOT EXISTS feature_to_model (
training_job_id varchar NOT NULL,
feature_name varchar NOT NULL,
feature_set_name varchar NOT NULL,
model_name varchar NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
PRIMARY KEY (training_job_id, feature_name, feature_set_name),
CONSTRAINT UC_feature_to_model UNIQUE (training_job_id)
)
英文:
ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification in
This because training_job_id is a column referenced in a FOREIGN KEY CONSTRAINT and not an index. In fact, if you want that to be faster, you may consider additionally adding an index. From the docs on ON CONFLICT
You probably want something like this,
CREATE TABLE IF NOT EXISTS feature_to_model (
training_job_id varchar NOT NULL,
feature_name varchar NOT NULL,
feature_set_name varchar NOT NULL,
model_name varchar NOT NULL,
created_at timestamp with time zone DEFAULT now() NOT NULL,
PRIMARY KEY (training_job_id, feature_name, feature_set_name)
CONSTRAINT UC_feature_to_model UNIQUE (training_job_id)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论