Josh McArthur: Fancy Postgres indexes with ActiveRecord

I recently wanted to add a model for address information but also wanted to add a unique index to those fields that is case-insensitive.
The model looked like this:

create_table :shop_locations do |t|
  t.string :street
  t.string :house_number
  t.string :zip_code
  t.string :city
  t.belongs_to :shop
end

But how to solve the uniqueness problem?

Another day, another undocumented Rails feature!

This time, it’s that ActiveRecord::Base.connection.add_index supports an undocumented option to pass a string argument as the value for ‘column’.

This string is passed directly to the SQL statement, making it possible to use all sorts of fun things to lock down the constraint. It also means that you can make an index more declaratively if you’re not comfortable using the built-in options for key lengths or ordering.

(excerpt from: Fancy Postgres indexes with ActiveRecord Show archive.org snapshot )

So regarding to the cited site Rails 5+ allows me to use an SQL statement in my index:

t.index 'shop_id, lower(street), lower(house_number), lower(zip_code), lower(city)',
  name: 'index_unique_locations_on_shop_id',
  unique: true

You will also need a validation in your model. Since this is not possible with the existing Rails helpers, you have to write a custom validation like this:

def validate_uniqueness_of_address
  same_address = shop.locations
    .where.not(id: id)
    .where('lower(street) = lower(?)', street)
    .where('lower(house_number) = lower(?)', house_number)
    .where('lower(zip_code) = lower(?)', zip_code)
    .where('lower(city) = lower(?)', city)

  if same_address.exists?
    errors.add(:street, :taken)
    errors.add(:house_number, :taken)
    errors.add(:zip_code, :taken)
    errors.add(:city, :taken)
  end
end