Activerecord::PGCollation

This gem adds support for creating and deleting collations. The collation feature allows specifying the sort order and character classification behavior of data per-column, or even per-operation. This alleviates the restriction that the LC_COLLATE and LC_CTYPE settings of a database cannot be changed after its creation.

schema.rb Support

The motivation of this gem is to integrate postgres collations into your schema.rb file without switching to structure.sql.

ActiveRecord::Schema.define(version: 2021_12_01_213707) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"

  # These are collations that must be created before they can be used in the schema definition
  create_collation "case_insensitive", lc_collate: "und-u-ks-level2", lc_ctype: "und-u-ks-level2", provider: "icu", deterministic: false
end

Installation

Add this line to your application's Gemfile:

gem 'activerecord-pg_collation'

And then execute:

$ bundle install

Or install it yourself as:

$ gem install activerecord-pg_collation

Usage

Create a collation

This gem adds the create_collation migration helper for creating collations:

create_collation "collation_name", options

For more information about available options visit: https://www.postgresql.org/docs/current/sql-createcollation.html

For example, this migration creates a case insensitive collation:

class CreateCaseInsensitiveCollation < ActiveRecord::Migration[6.1]
  def change
    create_collation "case_insensitive", provider: "icu", locale: "und-u-ks-level2", deterministic: false
  end
end

Delete a collation

This gem adds the drop_collation migration helper for deleting collations:

drop_collation "collation_name", option

By default option is equals to "RESTRICT". For more information visit https://www.postgresql.org/docs/current/sql-dropcollation.html

For example, this migration deletes the case insensitive collation previously created:

class DropCaseInsensitiveCollation < ActiveRecord::Migration[6.1]
  def change
    drop_collation "case_insensitive"
  end
end

Use a collation with an index

class AddIndexToSomeTable < ActiveRecord::Migration[6.1]
  def change
    add_index :some_table, "some_string_field COLLATE case_insensitive, another_string_field COLLATE case_insensitive", unique: true
  end
end

Development

After checking out the repo, run bin/setup to install dependencies. Then, run bundle exec rake spec to run the tests. You can also run bin/console for an interactive prompt that will allow you to experiment.

To install this gem onto your local machine, run bundle exec rake install. To release a new version, update the version number in version.rb, and then run bundle exec rake release, which will create a git tag for the version, push git commits and the created tag, and push the .gem file to rubygems.org.

License

The gem is available as open source under the terms of the MIT License.