Rails 4 querying against postgresql column with array data type error

Question

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

Here is the table schema:

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 works:

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

However, this query does not:

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

That throws this error:

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

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

What I have right now is:

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

I want to be able to query records that have more than one body_part associated with them, which was the whole point of using an array data type, so if someone could enlighten me on how to do this that would be awesome. I don't see it anywhere in the docs.

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 typecasted ARRAY['Arms', 'Chest'] to varchar:

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

and that worked.

Answer 1

I don't think that it has related to rails.

What if you do the follow?

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

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

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

or maybe give a look to this gem: https://github.com/dockyard/postgres_ext/blob/master/docs/querying.md

And then do a query like:

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

Answer 2

@Aguardientico is absolutely right that what you want is the array overlaps operator &&. I'm following up with some more explanation, but would prefer you to accept that answer, not this one.

Anonymous rows (records)

The construct ('item1', 'item2', ...) is a row-constructor unless it appears in an IN (...) list. It creates an anonymous row, which PostgreSQL calls a "record". The error:

ERROR:  operator does not exist: record = character varying

is because ('Arms', 'Chest') is being 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 has no idea how it's supposed to compare that to a string.

I don't really like this behaviour; I'd prefer it if PostgreSQL required you to explicitly use a ROW() constructor when you want an anonymous row. I expect that the behaviour shown here exists to support SET (col1,col2,col3) = (val1,val2,val3) and other similar operations where a ROW(...) constructor wouldn't make as much sense.

But the same thing with a single item works?

The reason the single ('Arms') case works is because unless there's a comma it's just a single parenthesised value where the parentheses are redundant and may 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)

Don't be alarmed by the type unknown. It just means that it's a string literal that hasn't yet had a type applied:

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

Compare array to scalar

This:

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

fails with:

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

because of implicit casting. The type of the body_part column is text[] (or varchar[]; same thing in PostgreSQL). You're comparing it for equality with the values in the IN clause, which are unknown-typed literals. The only valid equality operator for an array is = another array of the same type, so PostgreSQL figures that the values in the IN clause must also be arrays of text[] and tries to parse them as arrays.

Since they aren't written as array literals, like {"FirstValue","SecondValue"}, this parsing fails. Observe:

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

See?

It's easier to understand this once you see that IN is actually just a shorthand for = ANY. It's an equality comparison to each element in the IN list. That isn't what you want if you really want to find out if two arrays overlap.

So that's why you want to use the array overlaps operator &&.