sud9ja
Last Updated: June 29, 2017
·
48.6K
· stuliston

Rails 4 & the PostgreSQL Array data type

Active Record 4 now has support for PG's Array data-type. Here's how you might use it to give users multiple email addresses:

Migration:

class AddEmailsToUser < ActiveRecord::Migration
  def change
    add_column :users, :emails, :string, array: true, default: '{}'
  end
end

Notice:
* it's specified as data type :string with array: true
* to default the column to an empty array ( [] ), you use default: '{}'

To query your records you want to use PSQL's ANY and/or ALL methods:

SELECT * FROM users WHERE 'foo@bar.com' = ANY (emails);
SELECT * FROM users WHERE 'foo@bar.com' = ALL (emails);

...which behave as you'd imagine.

Using the Active Record query API:

User.where("'foo@bar.com' = ANY (emails)")

That's it! I hope that helps someone.

BONUS EDIT 2013-07-15:

To create an index for your array column, you must choose between GiST and GIN as strategies. The documentation covers the options nicely, but the distilled version is that GIN lookups are much (3x) faster, but take longer (10x) to build. If your data is read far more often than it is written, go for GIN.

You'd do that with the following (AR4) migration:

class AddEmailIndexToUsers < ActiveRecord::Migration
  def change
    add_index  :users, :emails, using: 'gin'
  end
end

~ Stu

11 Responses
Add your response

8168

Excellent, thanks for this!

over 1 year ago ·
9862

You can also define the default value with:
ruby add_column :users, :emails, :string, array: true, default: []
no need to pass a String.

over 1 year ago ·
10277

Thanks just the tidbit I was looking for!

over 1 year ago ·
12600

Thanks, I've been using default: [] in Rails 4.1.0.beta1 which seems to be working. Also, can you tell me how to edit my protips? I can't find an edit button anywhere!

over 1 year ago ·
13695

In the UI, would the user now enter multiple emails just by separating them with a comma?

over 1 year ago ·
15137

default: [] (or '{}') will also result in the whole table been traversed to insert '{}' value for each row. This locks the table on a large production table during the migration. Got bitten so just a friendly note =)

over 1 year ago ·
15182

In trying to use array fields, it seems that GIN indices are not used when we query the array field with ANY. We have to use @> to use the index as we typically think it should.

I wrote a short one about it. https://coderwall.com/p/ey7mbq.

over 1 year ago ·
18466

Hi,
I am try PG array with Rails 4, this is very interesting to do in rails.but i got struck in below error
<br/>
addcolumn :courses, :userid, :integer, array: true, null: false, default: '{}'
</br>
for the above data type I'm getting DatatypeMismatch

PG::DatatypeMismatch: ERROR: column "user_id" is of type integer[] but default expression is of type integer

HINT: You will need to rewrite or cast the expression.

if I made changes like integer to text then error message is
PG::InvalidTextRepresentation at /courses

ERROR: malformed array literal: "1" DETAIL: Array value must start with "{" or dimension information.

over 1 year ago ·
20086

@sai43
Migraition is wrong, should be:
add column :courses, :userid, :integer, array: true, null: false, default: []

over 1 year ago ·
21171

Thanks! xD

over 1 year ago ·
26549

this is my migration
addcolumn :products, :russiansize, :integer, array: true, array: true, null: false, default: []
addcolumn :products, :eurosize, :integer, array: true, array: true, null: false, default: []
addcolumn :products, :britishsize, :integer, array: true, array: true, null: false, default: []
addcolumn :products, :americansize, :integer, array: true, array: true, null: false, default: []
addcolumn :products, :asiansize, :integer, array: true, array: true, null: false, default: []
this is my model

RUSIZES = %w[35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56]
EU
SIZES = %w[e36 e37 e38 e39 e40 e41 e42 e43 e44]
USSIZES = %w[XS S M L XL XXL]
UK
SIZES = %w[b3 b4 b5 d5.5 b6 b6.5 b7 b7.5 b8]
AISA_SIZES = %w[a35 a36 a37 a38 a39 a40 a41 a42 a43 a44 a45 a46 a47 a48 a49 a50 a51 a52 a53 a54 a55 a56]

enum industry: INDUSTRYTYPES
enum gender: GENDER
TYPES
enum type: TYPETYPES
enum size: SIZE
TYPES
enum russiansize: RUSIZES
enum eurosize: EUSIZES
enum americansize: USSIZES
enum britishsize: UKSIZES
enum asiansize: AISASIZES

and this is my params
params.require(:product).permit(:title, :image, :description, :price, :brand, :categoryid, :industry, :gender, :size, russiansize: [], eurosize: [], britishsize: [], americansize: [], asiansize: [] )

here is my form
#fashion-param.hidden
= f.input :gender, as: :radiobuttons, collection: Product::GENDERTYPES, :itemwrapperclass => 'radio-inline'
hr
= f.input :type, as: :radiobuttons, collection: Product::TYPETYPES, :itemwrapperclass => 'radio-inline'
hr
= f.input :size, as: :radiobuttons, collection: Product::SIZETYPES, :itemwrapperclass => 'radio-inline'
hr
#size-ru-param.hidden.country-sizes
= f.input :russiansize, as: :radiobuttons, collection: Product.russiansizes, :itemwrapperclass => 'radio-inline'
hr
#size-eu-param.hidden.country-sizes
= f.input :euro
size, as: :radiobuttons, collection: Product::EUSIZES, :itemwrapperclass => 'radio-inline'
hr
#size-uk-param.hidden.country-sizes
= f.input :britishsize, as: :checkboxes, collection: Product::UKSIZES, :itemwrapperclass => 'radio-inline'
hr
#size-us-param.hidden.country-sizes
= f.input :american
size, as: :checkboxes, collection: Product::USSIZES, :itemwrapperclass => 'radio-inline'
hr
#size-az-param.hidden.country-sizes
= f.input :asiansize, as: :checkboxes, collection: Product.AISASIZES, :itemwrapper_class => 'radio-inline'

but i cant save it its give me errors please would like to check it why i am getting error

ArgumentError at /products
'["4", "5", "6", ""]' is not a valid asian_size

over 1 year ago ·