如何基于关联表字段创建Postgres唯一部分覆盖索引？

原本的表结构及唯一部分覆盖索引如下：

CREATE TABLE triples(
  cardinality text NOT NULL,
  entity_id text NOT NULL,
  attribute text NOT NULL,
  value jsonb NOT NULL,
);

CREATE UNIQUE INDEX triples_ea ON triples(entity_id, attribute) INCLUDE(value) WHERE cardinality = 'one';

该索引基于cardinality字段值，为entity_id和attribute创建了唯一覆盖索引。

现在调整表结构，cardinality字段移至关联表schema中，新表结构如下：

CREATE TABLE schema(
  id text PRIMARY KEY,
  cardinality text NOT NULL,
);
CREATE TABLE triples(
  entity_id text,
  attribute text REFERENCES schema(id),
  value jsonb
);

需要实现类似的唯一覆盖索引逻辑：仅当关联的schema.cardinality = 'one'时，约束triples.entity_id和triples.attribute的唯一性，并覆盖value字段。

PostgreSQL的普通索引无法直接引用关联表的字段，可通过以下两种方式实现需求：

方法1：使用稳定函数关联查询字段 #

创建STABLE级别的函数，通过attribute关联schema表获取对应cardinality值，再基于该函数创建索引：

-- 创建获取cardinality的函数
CREATE OR REPLACE FUNCTION get_cardinality(p_attribute text)
RETURNS text AS $$
SELECT cardinality FROM schema WHERE id = p_attribute;
$$ LANGUAGE sql STABLE;

-- 创建唯一部分覆盖索引
CREATE UNIQUE INDEX triples_ea ON triples(entity_id, attribute)
INCLUDE(value)
WHERE get_cardinality(attribute) = 'one';

注意：该方法依赖函数触发关联查询，索引维护和查询时会产生额外性能开销；若schema表的cardinality值更新，索引不会自动同步，需手动重建索引。

方法2：触发器同步字段到triples表 #

在triples表中新增cardinality字段，通过触发器同步schema表的对应值，之后即可按原逻辑创建索引：

-- 修改triples表，新增cardinality字段
ALTER TABLE triples ADD COLUMN cardinality text;

-- 初始化现有数据的cardinality值
UPDATE triples t
SET cardinality = s.cardinality
FROM schema s
WHERE t.attribute = s.id;

-- 创建触发器函数，同步cardinality值
CREATE OR REPLACE FUNCTION sync_cardinality()
RETURNS TRIGGER AS $$
BEGIN
  NEW.cardinality = (SELECT cardinality FROM schema WHERE id = NEW.attribute);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 插入触发器
CREATE TRIGGER trigger_triples_insert_cardinality
BEFORE INSERT ON triples
FOR EACH ROW EXECUTE FUNCTION sync_cardinality();

-- 更新触发器（当attribute变更时同步）
CREATE TRIGGER trigger_triples_update_cardinality
BEFORE UPDATE OF attribute ON triples
FOR EACH ROW EXECUTE FUNCTION sync_cardinality();

-- 当schema表的cardinality更新时，同步triples表
CREATE OR REPLACE FUNCTION sync_triples_cardinality_on_schema_update()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE triples
  SET cardinality = NEW.cardinality
  WHERE attribute = NEW.id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_schema_update_cardinality
AFTER UPDATE OF cardinality ON schema
FOR EACH ROW EXECUTE FUNCTION sync_triples_cardinality_on_schema_update();

-- 创建原逻辑的唯一部分覆盖索引
CREATE UNIQUE INDEX triples_ea ON triples(entity_id, attribute)
INCLUDE(value)
WHERE cardinality = 'one';

优点：索引维护和查询性能与原逻辑一致，数据同步自动完成；缺点：需要冗余存储cardinality字段，增加少量存储开销。

内容的提问来源于stack exchange，提问作者Stepan Parunashvili