SQL Upsert语句不适用于非索引列

Question

我使用的是PostgreSQL v9。6.6. 我正在运行以下SQL：

insert into t_vs_config_key (name, description, is_brand_dependent)
  values ('ucp.cluster','UCP Cluster', true)
  ON CONFLICT (name) DO UPDATE SET is_brand_dependent=true;

这将导致以下错误，因为name列的索引不是唯一的。我不能添加索引，因为有一些存量数据不是唯一的。

错误：不存在与冲突规范匹配的唯一或排除约束

问：有没有办法在没有索引的情况下执行upsert语句？

谢谢

Answer 1

正如评论中已经提到的，如果没有唯一的索引，就不能对冲突使用。

但是，您可以通过向表中添加一个指示行是否有效的新列，并根据该标志创建一个部分唯一索引来确保未来的插入是唯一的：

alter t_vs_config_key 
  add column valid boolean not null default false;

这将初始化所有现有的行有效=false

现在，对于未来的行，将此列的默认值更改为true

alter table t_vs_config_key
    alter column valid set default true;

并通过添加检查约束来确保不再插入“无效”行：

alter table t_vs_config_key  
  add constraint check_valid check (valid);

现在，您可以创建一个唯一的索引：

create unique index on t_vs_config_key(name)
where valid;

它可以用于冲突子句的：

insert into t_vs_config_key (name, description, is_brand_dependent)
values ('ucp.cluster','UCP Cluster', true)
ON CONFLICT (name) where valid DO UPDATE 
  SET is_brand_dependent = true;

注意语句中的在哪里有效子句。