current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

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.

share|improve this question
1  
Thanks for making the effort to write a detailed question, including your code, exact error messages, etc. It's good to always include your PostgreSQL version too (SELECT version()) though in this case it doesn't look like it matters much. –  Craig Ringer Jun 29 '13 at 9:11
add comment

2 Answers

up vote 5 down vote accepted

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])

share|improve this answer
    
Yes, that works, but I should have been more specific. Really I am looking for how to query using ActiveRecord, something like this: DbOfExercise.where(body_part: params[:body_parts]). I don't know ahead of time what values the user will select so using the OR statement does not seem feasible. I edited my question above. –  kwyoung11 Jun 28 '13 at 22:59
    
I've edited the answer. Give a look –  Aguardientico Jun 28 '13 at 23:07
    
Array overlaps is certainly what you want. = ANY makes no sense for a multiple value left hand side. –  Craig Ringer Jun 29 '13 at 9:10
    
+1 for postgres_ext. Solves the problem of making the queries from Rails. –  ConstableJoe Mar 6 at 16:15
add comment
up vote 4 down vote

@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 &&.

share|improve this answer
    
Excelent explain!!! –  Aguardientico Jun 29 '13 at 11:59
    
Thanks, great explanation. Are text[] and varchar[] really the same thing though? I was getting an error and had to typecast the ARRAY['Arms', 'Chest'] to varchar when using the && operator against the body_part column (expanded on this in my question). –  kwyoung11 Jun 29 '13 at 16:53
    
@kwyoung11 They're not precisely the same thing; varchar(n) carries a length constraint with it. They're implicitly castable to each other and in most places are equivalent, but there are a few places Pg isn't allowed to treat them as the same. They're the same in-memory and on-disk data type, it's just the length constraint that forces it to treat them slightly differently sometimes. I avoid varchar and just use text everywhere. If I want a length constraint I add an explicit check constraint. I find this clearer and more consistent, but then I don't have to write portable schemas. –  Craig Ringer Jun 30 '13 at 23:55
    
Gotcha, that makes sense. I thought there might have been a performance difference, but according to the docs that isn't the case. –  kwyoung11 Jul 1 '13 at 3:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.