Querying if an int is inside of an array column, Postgres with Rails

Question

I have a table with a column of type integer array. Given an integer as input I want to query for the rows where this column named tipo_propiedad contains the input integer. Directly in Postgres that may work by using && operator and casting the input int to an array. The thing is that using this:

Zone.where("tipo_propiedad && ?", [].push(type)).count

Where type is the input integer, gives

SELECT COUNT(*) FROM "zonas" WHERE (tipo_propiedad && 1) PG::UndefinedFunction: ERROR: operator does not exist: integer[] && integer

I also tried with

Zone.where("tipo_propiedad && ?", [5].push(type))

because tipo_propiedad would only have 1, 2 or 3 inside and that gives

SELECT COUNT(*) FROM "zonas" WHERE (tipo_propiedad && 5,1) PG::UndefinedFunction: ERROR: operator does not exist: integer[] && integer

I'm also using squeel but that gem doesn't have any operator to do this.

Marth · Accepted Answer · 2015-01-09 14:47:04Z

5

You can do either

Zone.where("'?' = ANY (tipo_propiedad)", 1)

or

Zone.where("tipo_propiedad && ARRAY[?]", 1)
                     # or "&& '{?}'"

answered Jan 9, 2015 at 14:47

Marth

24.9k3 gold badges68 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

Querying if an int is inside of an array column, Postgres with Rails

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related