Matching array values in PostgreSQL with ActiveRecord

Question

In my Rails 4 app I have a goal to see all contacts, where field visible_to in contacts table equal to 1. My visible_to is :integer, array: true.

However, I get the following exception:

PG::UndefinedFunction: ERROR: operator does not exist: integer[] = integer LINE 1: ....* FROM "contacts" WHERE "contacts"."visible_to" IN (1) OR... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.: SELECT "contacts".* FROM "contacts" WHERE "contacts"."visible_to" IN (1) ORDER BY created_at DESC

I searched for answers and as far as I see there is an issue with a type of visible_to. However, I couldn't find the solution. I also tried to get benefit from casts hint, but in vain.

My migration:

class AddVisibleToToContacts < ActiveRecord::Migration
    def change
      add_column :contacts, :visible_to, :integer, array: true, default: [], using: 'gin'     
    end 
end

Relevant variable from Controller:

@contacts_all_user_sorted = Contact.all.where(visible_to: [1]).order("created_at DESC")

Hi. Is it normal that you wrote "defalult" instead of "default" in your migration file ? — d34n5
– d34n5, Commented Aug 8, 2014 at 13:14
Thx, it was just a typo here. In a migration everything is fine. — arthur
– arthur, Commented Aug 11, 2014 at 12:15
Short preliminary debrief from me on the reasons of the problem. (1) Again and again and again RTFM and not only ActiveRecord but PostgreSQL as well (2) I was too heavy dependent on similar question and similar solution, that was not relevant for me (because of PostgreSQL specific?). — arthur
– arthur, Commented Aug 12, 2014 at 7:04

d34n5 · Accepted Answer · 2016-12-07 23:06:37Z

6

From these two websites:

It seems that this syntax should work:

@contacts_all_user_sorted = Contact.all.where("visible_to @> ARRAY[?]", [1])

Does it work?

P.S: As @Quertie pointed out in the comments, you may want to cast the value in the case of a String array, by replacing ARRAY[?] by ARRAY[?]::varchar[]

edited Dec 7, 2016 at 23:06

answered Aug 11, 2014 at 14:23

d34n5

1,31810 silver badges18 bronze badges

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

2 Comments

Qwertie Over a year ago

This doesn't work on string arrays and gives the error ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator does not exist: character varying[] @> text[] . Fixed it by changing ARRAY[?] to ARRAY[?]::varchar[]

d34n5 Over a year ago

Thanks for your comment @Qwertie. I will add a note to my answer.

Ajit Singh · Accepted Answer · 2014-08-08 17:26:18Z

0

your migration seems pretty straight forward and correct.

can you please try this:

Contact.where('visible_to IN ?', ['1', '2'])

answered Aug 8, 2014 at 17:26

Ajit Singh

1465 bronze badges

8 Comments

arthur Over a year ago

Ajit, thx for help. Here is an outcome from a console:

Contact Load (0.3ms)  SELECT "contacts".* FROM "contacts"  WHERE (visible_to IN '1','2')   Contact Load (0.3ms)  SELECT "contacts".* FROM "contacts"  WHERE (visible_to IN '1','2') PG::SyntaxError: ERROR:  syntax error at or near "'1'" LINE 1: ... "contacts".* FROM "contacts"  WHERE (visible_to IN '1','2') ......

arthur Over a year ago

P.S. And the output error at the end is output error: #<ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "'1'"

Ajit Singh Over a year ago

seems like your visible_in column is integer. is that correct? \n please try Contact.where('visible_to in (?)', [1,2])

arthur Over a year ago

I was thinking about that as well, but as far I understand from manuals I need to define a type of elements in array, right? I tried your suggestion and in this case I got:

output error: #<ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: integer[] = integer

Ajit Singh Over a year ago

Contact.where(:visible_to => [1,2,3]) try this

|

Collectives™ on Stack Overflow

Matching array values in PostgreSQL with ActiveRecord

2 Answers 2

2 Comments

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Linked

Related