0

Problem: I want to add unique indexes to my POSTGRESQL database which are case sensitive. I can do that manually and directly on the database like this:

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

This works when executing the SQL directly on the database.

I now want to use the same code via ActiveRecord database migration. I put this in the migration as plain SQL:

execute <<-SQL
 <above statements>
SQL

However, Rails seem to ignore the UPPER(<attribute>) completely. The following is computed to the 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

That's not expected.

If I remove the UPPER statements, the indexes are NOT case sensitive in fact as my tests are failing in RSPEC:

create(:participation, event_id: event.id, email: 'hans@gmail.com', firstname: "john", lastname: nil)
expect { create(:participation, event_id: event2.id, email: 'hans@gmail.com', firstname: "John", lastname: nil) }.not_to raise_error(ActiveRecord::RecordNotUnique)

=> should pass but fails

For me it seems like there is no way to put the above INDEX creation into an ActiveRecord migration as UPPER is getting removed. Is this an ActiveRecord bug or intended like this? How can it be solved? I found nothing regarding this topic.

DonMB
  • 2,550
  • 3
  • 28
  • 59
  • I think you're slightly misdiagnosing your problem here. Compare your database schema with schema.rb and I think you'll find that no matter how you run the SQL (directly or via migration), the schema.rb will be the same. You need to run `rake db:schema:dump` after you manually add the indexes in the database for schema.rb to be updated. – user229044 Jul 10 '18 at 13:15
  • @meagar that's right but the problem is that what will be added in the database as index is in fact different from what I want via the plain SQL execution. I have now made manual adjustments to my CircleCI config to make it running but that's a monkey patch. The schema.rb should reflect what really is in the DB. – DonMB Jul 10 '18 at 17:13

0 Answers0