ruby-on-rails - Active Record 添加普通 sql 索引失败，区分大小写

Question

问题：我想向我的 POSTGRESQL 数据库添加唯一索引，这些索引区分大小写。我可以像这样手动直接在数据库上执行此操作：

CREATE UNIQUE INDEX participations_uniqueness_one ON participations (event_id, UPPER(email), UPPER(lastname)) WHERE firstname IS NULL;
CREATE UNIQUE INDEX participations_uniqueness_two ON participations (event_id, UPPER(email), UPPER(firstname)) WHERE lastname IS NULL;
CREATE UNIQUE INDEX participations_uniqueness_three ON participations (event_id, UPPER(email)) WHERE firstname IS NULL AND lastname IS NULL;
CREATE UNIQUE INDEX participations_uniqueness_four ON participations (event_id, UPPER(email), UPPER(firstname), UPPER(lastname));
SQL

这在直接在数据库上执行 SQL 时有效。

我现在想通过 ActiveRecord 数据库迁移使用相同的代码。我将其作为普通 SQL 放入迁移中：

execute <<-SQL
 <above statements>
SQL

然而，Rails 似乎UPPER(<attribute>)完全忽略了。以下计算为schema.rb

add_index "participations", ["event_id"], name: "participations_uniqueness_four", unique: true, using: :btree
add_index "participations", ["event_id"], name: "participations_uniqueness_one", unique: true, where: "(firstname IS NULL)", using: :btree
add_index "participations", ["event_id"], name: "participations_uniqueness_three", unique: true, where: "((firstname IS NULL) AND (lastname IS NULL))", using: :btree
add_index "participations", ["event_id"], name: "participations_uniqueness_two", unique: true, where: "(lastname IS NULL)", using: :btree

这不是预期的。

如果我删除 UPPER 语句，索引实际上不区分大小写，因为我的测试在 RSPEC 中失败：

create(:participation, event_id: event.id, email: '[e-mail]', firstname: "john", lastname: nil)
expect { create(:participation, event_id: event2.id, email: '[e-mail]', firstname: "John", lastname: nil) }.not_to raise_error(ActiveRecord::RecordNotUnique)

=> 应该通过但失败了

对我来说，似乎没有办法将上面的 INDEX 创建放入 ActiveRecord 迁移中，因为 UPPER 正在被删除。这是一个 ActiveRecord 错误还是打算这样？如何解决？我没有找到关于这个话题的任何东西。

标签： ruby-on-railsdatabase-migration