Read more

Josh McArthur: Fancy Postgres indexes with ActiveRecord

Florian Leinsinger
May 18, 2022Software engineer at makandra GmbH

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
Illustration online protection

Rails professionals since 2007

Our laser focus on a single technology has made us a leader in this space. Need help?

  • We build a solid first version of your product
  • We train your development team
  • We rescue your project in trouble
Read more Show archive.org snapshot

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

See also:

Posted by Florian Leinsinger to makandra dev (2022-05-18 10:45)