Postgres HStore Errors - Unknown Operator

Question

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!

Answer 1

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.