CODEWITHSUNDEEP
ruby-on-railsarrayspostgresqlruby-on-rails-4activerecord
Nick Ginanto
Nick Ginanto

Reputation: 32130

Querying a Postgres array of integers in Rails

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

Upvotes: 13

Views: 6839

Answers (3)

Sharvy Ahmed
Sharvy Ahmed

Reputation: 7405

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')

Upvotes: 20

Nick Ginanto
Nick Ginanto

Reputation: 32130

seems to had a problem with the name of the array since there was an association with a similar name and rails automagic collided with it

Upvotes: 0

Wizard of Ogz
Wizard of Ogz

Reputation: 12643

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.

Upvotes: 1

Related Questions