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
endNotice:
* 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
Written by Stuart Liston
Related protips
11 Responses
Excellent, thanks for this!
You can also define the default value with:
ruby
add_column :users, :emails, :string, array: true, default: []
no need to pass a String.
Thanks just the tidbit I was looking for!
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!
In the UI, would the user now enter multiple emails just by separating them with a comma?
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 =)
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.
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.
@sai43
Migraition is wrong, should be:
add column :courses, :userid, :integer, array: true, null: false, default: []
Thanks! xD
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]
EUSIZES = %w[e36 e37 e38 e39 e40 e41 e42 e43 e44]
USSIZES = %w[XS S M L XL XXL]
UKSIZES = %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: GENDERTYPES
enum type: TYPETYPES
enum size: SIZETYPES
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 :eurosize, 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 :americansize, 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
