Querying a Postgres array of integers in Rails

Question

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

Sharvy Ahmed · Accepted Answer · 2018-10-18 10:50:19Z

20

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

edited Oct 18, 2018 at 10:50

answered Apr 25, 2015 at 13:02

Sharvy Ahmed

7,4151 gold badge36 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sharvy Ahmed Over a year ago

Can you add the migration file or the schema?

York Yang Over a year ago

MyModel.where("? = ANY(myarray)", '{42}') should be MyModel.where("? = ANY(myarray)", '42') ? tested on pg 9.6

Wizard of Ogz · Accepted Answer · 2015-04-25 14:31:34Z

1

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.

answered Apr 25, 2015 at 14:31

Wizard of Ogz

12.6k2 gold badges44 silver badges44 bronze badges

1 Comment

Evmorov Over a year ago

And if it's true try MyModel.where("'?' = ANY(myarray)", 42)

Nick Ginanto · Accepted Answer · 2015-04-25 16:57:31Z

0

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

answered Apr 25, 2015 at 16:57

Nick Ginanto

32.4k49 gold badges143 silver badges253 bronze badges

Collectives™ on Stack Overflow

Querying a Postgres array of integers in Rails

3 Answers 3

2 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Related