sud9ja
Last Updated: June 29, 2017
·
39.13K
· stuliston
Dae3715f6eae05308ff8bd57b1618b2b

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

Say Thanks
Respond

11 Responses
Add your response

8168
Photo on 6 5 13 at 8.07 pm

Excellent, thanks for this!

over 1 year ago ·
9862
Borisilli

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
8498e0303c78c6441d73314629a44fb0

Thanks just the tidbit I was looking for!

over 1 year ago ·
12600
934794545f4c74ad8a6bcf9939e764a4

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
Riri1 normal

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

over 1 year ago ·
15137
493e873b1593892192b2cede3c69ffc5

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
493e873b1593892192b2cede3c69ffc5

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
0 yrlwhn5tiog2yvpoyb6nhznxif5dpmpoycaqhzlxpolpijier9zbqvlldtl1xyj6g1fvftcsoa9l

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
None

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

over 1 year ago ·
21171
None

Thanks! xD

over 1 year ago ·
26549
None

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 ·