my model has a pg array which I use to store integers
Querying using the methods I found from other questions yields errors or gives empty results
MyModel.where("? = ANY (myarray)", 42)
gives
PG::UndefinedFunction: ERROR: operator does not exist: integer = text
and
MyModel.where("myarray @> '{?}'", 42)
gives an empty results, yet I do have a model with 42 as one of the ints in the array
#<MyModel:0x007f9a77dd5608> {
:id => 170,
:myarray => [
[0] 42,
[1] 43,
[2] 58,
[3] 61,
[4] 63
]
Is there a special way to query integers(or floats) within a postgres array in Rails?
the migration
class AddMyarrayToMyModel < ActiveRecord::Migration
def change
add_column :my_models, :myarray, :integer, array: true, default: []
add_index :my_models, :myarray, using: 'gin'
end
end
and schema
t.integer "myarray", default: [], array: true
Try these:
MyModel.where("? = ANY(myarray)", '{42}')
or
MyModel.where("myarray @> ?", '{42}')
or (on PG 9.6 according to York Yang's comment)
MyModel.where("? = ANY(myarray)", '42')
PG::UndefinedFunction: ERROR: operator does not exist: integer = text
From this message it appears that the myarray
is actually an array of type text
. Check that column to make sure it is of the correct type.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With