up vote 1 down vote favorite

My ruby code:

Portfolio.where("data @> (:key => :value)",     :key => 'CSJ', :value => '0.1')

Generates the following SQL:

"SELECT \"portfolios\".* FROM \"portfolios\"  WHERE (data @> ('CSJ' => '0.1'))"

Comes up with this error:

Error: PG::Error: ERROR:  operator does not exist: unknown => unknown
LINE 1: ...olios".* FROM "portfolios"  WHERE (data @> ('CSJ' => '0.1'))
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT "portfolios".* FROM "portfolios"  WHERE (data @> ('CSJ' => '0.1'))

Postgresql 9.1.4, Rails 3.2.7/8, using activerecord-postgres-hstore gem with the following in my model code:

serialize :data, ActiveRecord::Coders::Hstore

Help would be appreciated!

share|improve this question
I don't know what => is...do you mean >= (greater than or equal to)? – Jim Aug 14 '12 at 23:58
1  
Ah, thank you. Hadn't seen that before. – Jim Aug 15 '12 at 0:06
1  
Thanks for the detailed question and for including the versions you're working with from the start. Saves everyone a bunch of time. – Craig Ringer Aug 15 '12 at 1:57
feedback

1 Answer

up vote 3 down vote accepted

You didn't install the hstore extension in the database that Rails is using.

For example, if I say select 'a' => 'b' in one of my databases that doesn't have hstore, I get this:

=> select 'a' => 'b';
ERROR:  operator does not exist: unknown => unknown
LINE 1: select 'a' => 'b';
                   ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

But in another database that does have hstore installed, I get this:

=> select 'a' => 'b';
 ?column? 
----------
 "a"=>"b"
(1 row)

You need to do a create extension hstore in your Rails database.

share|improve this answer
Thank you very much for confirming this. I had originally considered this, and then decided it couldn't be the case as I HAD installed the extension. But your note just made me remember that the last time I brought those databases in, I was lazy and regenerated them from a dump. That must be the problem.... I'll accept before checking as I'm fairly sure now this is the problem! Thx! – Brandon Aug 15 '12 at 3:17
@Brandon: Might make sense to add a check for this to your test suite, it 'should have had "create extension hstore" done in the database' do .... – mu is too short Aug 15 '12 at 3:27
feedback

Your Answer

 
or
required, but never shown
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.