Rails query comparing against Postgres Array Column

Question

So i have a this column in a table t.text "foo", default: [], array: true

And i have this array list in a helper file,

module HelperData

  Helper_array = [
    "data_1",
    "data_2",
    "data_3",
    "data_4",
    "data_5"
  ]
end

Inside the FooName.rb model there's a line include HelperData.

So the query i want to pass is the include operator && in comparing PG arrays, based on this Postgres Guide.

I have tried the following queries:

FooName.where("foo" && HelperData::Helper_array). This query returns the error ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:. The syntax error is with the elements in HelperData::Helper_array. I have a slight hunch that it is because Foo is :text but the data in Helper_array is a string, though i can't be certain.

FooName.where("foo && HelperData::Helper_array"). This query returns the error ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR:. Obviously this is just the wrong syntax then.

What is the proper query to find whether the foo array has any elements that overlap with the Helper_array, and return all FooName objects that has overlapping elements?

seaify - Freelancer · Accepted Answer · 2017-04-01 09:12:30Z

2

try:

FooName.where("foo && ARRAY[?]::text[]", HelperData::Helper_array)

refer this link https://www.postgresql.org/docs/current/static/functions-array.html

edited Apr 1, 2017 at 9:12

answered Apr 1, 2017 at 9:04

seaify - Freelancer

7487 silver badges11 bronze badges

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

3 Comments

angkiki Over a year ago

ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR: operator does not exist: text[] && character varying[] Throws this error.

seaify - Freelancer Over a year ago

em, sorry for that, find out that the column is text type, not string type. so try FooName.where("foo && ARRAY[?]::text[]", HelperData::Helper_array). i have test it in my rails console, it's working.

angkiki Over a year ago

Okay thanks! I'll test it out and check your answer as correct if it works! :)

Collectives™ on Stack Overflow

Rails query comparing against Postgres Array Column

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related