Rails 4 query for postgresql column with array data type error

Question

I am trying to query a table with a column with the data type of a postgresql array in Rails 4.

Here is the table layout:

create_table "db_of_exercises", force: true do |t| t.text "preparation" t.text "execution" t.string "category" t.datetime "created_at" t.datetime "updated_at" t.string "name" t.string "body_part", default: [], array: true t.hstore "muscle_groups" t.string "equipment_type", default: [], array: true end 

The following query is executed:

 SELECT * FROM db_of_exercises WHERE ('Arms') = ANY (body_part); 

However, this request does not execute:

 SELECT * FROM db_of_exercises WHERE ('Arms', 'Chest') = ANY (body_part); 

It throws this error:

 ERROR: operator does not exist: record = character varying 

This does not work for me either:

 SELECT * FROM "db_of_exercises" WHERE "body_part" IN ('Arms', 'Chest'); 

This causes this error:

 ERROR: array value must start with "{" or dimension information 

So, how can I query a column with an array data type in ActiveRecord ?

What I have now:

 @exercises = DbOfExercise.where(body_part: params[:body_parts]) 

I want to be able to query for records containing more than one body_part associated with them, which was generally the goal of using an array data type, so if someone could enlighten me on how to do this, that would be awesome. I do not see it anywhere in the documents.

The final solution for posterity:

Using the overlap operator (& &):

 SELECT * FROM db_of_exercises WHERE ARRAY['Arms', 'Chest'] && body_part; 

I was getting this error:

 ERROR: operator does not exist: text[] && character varying[] 

so I drew ARRAY ['Arms', 'Chest'] for varchar:

  SELECT * FROM db_of_exercises WHERE ARRAY['Arms', 'Chest']::varchar[] && body_part; 

and it worked.

Answer 1

I do not think that this is related to rails.

What if you do the following?

 SELECT * FROM db_of_exercises WHERE 'Arms' = ANY (body_part) OR 'Chest' = ANY (body_part) 

I know that rails 4 supports the Postgresql ARRAY data type, but I'm not sure that ActiveRecord creates new methods for querying the data type. Maybe you can use Array Overlap I mean the && operator, and then do something like:

 WHERE ARRAY['Arms', 'Chest'] && body_part 

or maybe look at this stone: https://github.com/dockyard/postgres_ext/blob/master/docs/querying.md

And then run a query like:

DBOfExercise.where.overlap (: body_part => params [: body_parts])

Answer 2

@Aguardientico is absolutely correct that you want the array to overlap with the && operator. I follow some explanations, but would prefer you to accept this answer rather than this one.

Anonymous strings (entries)

The construct ('item1', 'item2', ...) is the constructor of the string if it does not appear in the IN (...) list. It creates an anonymous string, which PostgreSQL calls a "record". Error:

 ERROR: operator does not exist: record = character varying 

is that ('Arms', 'Chest') interpreted as if it were ROW('Arms', 'Chest') , which produces a single record value:

 craig=> SELECT ('Arms', 'Chest'), ROW('Arms', 'Chest'), pg_typeof(('Arms', 'Chest')); row | row | pg_typeof --------------+--------------+----------- (Arms,Chest) | (Arms,Chest) | record (1 row) 

and PostgreSQL doesn't know how this should compare to a string.

I do not like this behavior; I would prefer PostgreSQL to require explicit use of the ROW() constructor when you want an anonymous string. I expect that the behavior given here exists to support SET (col1,col2,col3) = (val1,val2,val3) and other similar operations in which the ROW(...) constructor will not make any sense.

But what works with one element?

The reason why the only one ('Arms') works is because if there is no comma, this is just one brace value, where the brackets are redundant and can be ignored:

 craig=> SELECT ('Arms'), ROW('Arms'), pg_typeof(('Arms')), pg_typeof(ROW('Arms')); ?column? | row | pg_typeof | pg_typeof ----------+--------+-----------+----------- Arms | (Arms) | unknown | record (1 row) 

Do not worry about the unknown type. It just means that it is a string literal that has not yet used the type:

 craig=> SELECT pg_typeof('blah'); pg_typeof ----------- unknown (1 row) 

Comparing an array with a scalar

It:

 SELECT * FROM "db_of_exercises" WHERE "body_part" IN ('Arms', 'Chest'); 

failure:

 ERROR: array value must start with "{" or dimension information 

due to implicit casting. The body_part column body_part is text[] (or varchar[] , the same in PostgreSQL). You compare it for equality with the values ​​in the IN clause, which are letters with a typed type. The only valid equality operator for an array is = another array of the same type, so PostgreSQL shows that the values ​​in the IN clause must also be text[] arrays and try to parse them as arrays.

Since they are not written as array literals such as {"FirstValue","SecondValue"} , this parsing is not performed. Note:

 craig=> SELECT 'Arms'::text[]; ERROR: array value must start with "{" or dimension information LINE 1: SELECT 'Arms'::text[]; ^ 

Cm?

It’s easier to understand this as soon as you see that IN is actually just a shorthand for = ANY . This is a comparison of equality with each item in the IN list. This is not what you need if you really want to find out if two arrays overlap.

So why do you want to use array overlap with the && operator.