更新插入 - 插入...ON CONFLICT 失败，基于函数的索引作为唯一约束'lower()'

Question

我有一个数据库表，其中包含一个code列，该列使用小写索引来防止仅在大小写上不同的代码值(例如，"XYZ"="xYZ"="xyz"(。Postgresql 中的典型方法是创建一个基于函数的索引，如下所示：CREATE UNIQUE INDEX mytable_lower_code_idx ON mytable (lower(code)) 。

现在我有一个案例，我需要在该列上执行更新插入行为：

-- first insert
INSERT INTO mytable (code) VALUES ('abcd');
-- second insert, with upsert behaviour
INSERT INTO mytable (code) VALUES ('Abcd')
  ON CONFLICT (code) DO UPDATE
  SET code='Abcd';

对于第二个插入，我得到一个唯一的键冲突：ERROR: duplicate key value violates unique constraint "mytable_lower_code_idx"

(我也尝试使用ON CONFLICT ON CONSTRAINT mytable_lower_code_idx但Postgresql告诉我这个约束不存在，所以它可能没有将索引视为约束。

我的最后一个问题：有没有办法使插入...关于冲突与表达式索引一起工作？还是必须引入物理索引小写列才能完成任务？

Answer 1

使用 ON CONFLICT (lower(code)) DO UPDATE ：

CREATE TABLE mytable (
    code text
);
CREATE UNIQUE INDEX mytable_lower_code_idx ON mytable (lower(code));
INSERT INTO mytable VALUES ('abcd');
INSERT INTO mytable (code) VALUES ('Abcd')
  ON CONFLICT (lower(code)) DO UPDATE
  SET code='Abcd';
SELECT * FROM mytable;

收益 率

| code |
|------|
| Abcd |

---

请注意，ON CONFLICT语法允许冲突目标成为index_expression(我的强调(：

ON CONFLICT conflict_target 其中conflict_target可以是以下之一：

( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

和index_expression：

与index_column_name类似，但用于推断表达式 索引定义中出现的table_name列(不简单( 列(。遵循创建索引格式。

Answer 2

尝试按如下方式添加索引：

CREATE UNIQUE INDEX CONCURRENTLY mytable_lower_code_idx 
ON mytable (lower(code));
ALTER TABLE mytable 
ADD CONSTRAINT unique_mytab_code
UNIQUE USING INDEX mytable_lower_code_idx ;

然后：

INSERT INTO mytable (code) VALUES ('abcd');
-- second insert, with upsert behaviour
INSERT INTO mytable (code) VALUES ('Abcd')
  ON CONFLICT ON CONSTRAINT unique_mytab_code DO UPDATE
  SET code='Abcd';