Rails 4 supports arrays fields for PostgreSQL in a nice way, although it is not a very known feature. In order to demonstrate its usage it’s useful to explain the context where this was used.

PostgreSQL Arrays and Rails Migrations

Suppose we have a Product model with the following fields: name, category_id and tags. The name field will be a simple string, category_id will be the foreign key of a record in the Category model and tags will be created by inputting a string of comma-separated words, so: “one, two, forty two” will become the tags: “one”, “two” and “forty two” respectively.

Creating these tables via migrations is nothing new, except for the column tags which will have the Array type in this case. To create this kind of column we use the following syntax in our migration:

create_table :categories do |t| t.string :name, null: false end create_table :products do |t| t.string :name, null: false t.references :category, null: false t.text :tags, array: true, default: [] end

Let’s explore what we can do with this kind of field using the postgres console:

$ rails db > insert into products(name, category_id, tags) values('T-Shirt', 3, '{clothing, summer}'); > insert into products(name, category_id, tags) values('Sweater', 3, ARRAY['clothing', 'winter']); > select * from products; 1 | T-Shirt | 3 | {clothing, summer} 2 | Sweater | 3 | {clothing, winter}

As we can see we need to specify each tag following this syntax:

‘{ val1, val2, … }’ or ARRAY[‘val1’, ‘val2’, …]

Let’s play a little more to understand how this column behaves when queried:

> select * from products where tags = '{clothing, summer}'; 1 | T-Shirt | 3 | {clothing, summer} > select * from products where tags = '{summer, clothing}'; (0 rows) > select * from products where 'winter' = ANY(tags); 2 | Sweater | 3 | {clothing, winter}

As this example demonstrates, searching for records by an array with its values in the order they were inserted works, but with the same values in a different order does not. We were also able to find a record searching for a specific tag using the ANY function.

There’s a lot more to talk about arrays in PostgreSQL, but for our example this is enough. You can find more information at the PostgreSQL official documentation about arrays and its functions.

How Rails treats PostgreSQL arrays

It’s also valuable to see how to use the array field within Rails, let’s try:

$ rails c Product.create(name: 'Shoes', category: Category.first, tags: ['a', 'b', 'c']) #> Product.find(26).tags #> ["a", "b", "c"]

So Rails treats an array column in PostgreSQL as an Array in Ruby, pretty reasonable!

Validations

We want each product to be unique, let’s see some examples to clarify this concept.

Given we have the following product:

Product.create(name: 'Shoes', category: Category.first, tags: ['a', 'b', 'c'])

We can easily create another one if we change the name attribute:

Product.create(name: 'Slippers', category: Category.first, tags: ['a', 'b', 'c'])

We can also create another product with different tags:

Product.create(name: 'Shoes', category: Category.first, tags: ['a', 'b'])

But we don’t want to create a product with the same attributes, even if the tags are in a different order:

Product.create(name: 'Shoes', category: Category.first, tags: ['a', 'c', 'b']) #> false

As PostgreSQL only finds records by tags given the exact order in which they were inserted, then how can we ensure the uniqueness of a product with tags in an order-independent way?

After much thought we decided that a good approach would involve creating an unique index with all the columns in the products table but with tags sorted when a row is inserted in the database. Something like:

CREATE UNIQUE INDEX index_products_on_category_id_and_name_and_tags ON products USING btree (category_id, name, sort_array(tags));

And sort_array is our custom function responsible for sorting the array, since PostgreSQL does not have a built in function like this.

Creating a custom function in PostgreSQL using PL/pgSQL

To create a custom function we used the PL/pgSQL language, and since we are adding database specific code like this we can’t use the default schema.rb anymore. Let’s change this in config/application.rb:

# Use SQL instead of AR schema dumper when creating the database config.active_record.schema_format = :sql

With this configuration set, our schema.rb file will be replaced by a structure.sql file without side effects, our current migrations don’t need to be changed at all. Now we can create a migration with our sort_array code: