在hstore键上创建唯一索引或约束条件

Question

如果存在特定键，我希望在hstore列上创建唯一索引或约束。我希望能够在以下问题中找到答案：Practical limitations of expression indexes in PostgreSQL，但是我尝试了各种语法的版本，但都没有起作用。我的表是"hstore_table"，hstore字段是"hstore_value"，当存在"foo"和"bar"时，我想强制它们成为唯一的键。我的PostgreSQL版本是8.4.13。

- trex005

Answer 1

如果我理解你的要求正确，你需要一个部分唯一的功能性索引：

CREATE TABLE hstest ( x hstore not null );

CREATE UNIQUE INDEX hstest_key_k1_values_unique 
ON hstest((x -> 'k1'))
WHERE ( x ? 'k1' );

“WHERE”子句并不是必需的，因为如果未找到键，则关键查找将为空。是否适当取决于您的查询。

如果您需要多个键，请使用两个索引，如果您想让它们独立，则使用两个索引，或者如果您想将它们链接起来，使唯一约束允许(1,2)和(1,3)或(2,2)，但不允许另一个(1,2)，请像这样索引两个表达式：“

CREATE UNIQUE INDEX hstest_key_k1k2_values_unique 
ON hstest ((x -> 'k1'), (x -> 'k2'));

- Craig Ringer

---

文档中指出：“hstore 中的每个键都是唯一的。” 我不明白为什么我们需要添加您展示的索引类型。我有什么遗漏吗？ - IamIC

@IamIC 原帖的意图是确保对于不同的行 A 和 B，hstore 字段'h' 不能在两行中包含相同的键 'k' 并且该键的值也相同。即如果存在行 A.h{k=1}，则不允许插入行 B.h{k=1}，但允许插入 B.h{k=2} 或者 B.h{x=1}。类似于唯一约束条件，但是应用于 hstore 键的值，而不是整个列。 - Craig Ringer

@CraigRinger 谢谢。这很有道理。看起来需要在索引定义中指定要约束的每个键。对于大量键来说并不实用。我猜在这种情况下，upsert 触发器会起作用。 - IamIC

@IamIC 因为hstore有一个=运算符，你可能可以使用hstore子集运算符hstore -> text []来完成它，但前提是你只对固定的键列表感兴趣，并且对于空值处理语义没有问题。由于MVCC可见性规则，插入之前的触发器将无法工作；你不能使用触发器正确实现unique约束。 - Craig Ringer