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!

I don't know what => is...do you mean >= (greater than or equal to)? — Jim
– Jim, Commented Aug 14, 2012 at 23:58
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
– Craig Ringer, Commented Aug 15, 2012 at 1:57

mu is too short · Accepted Answer · 2012-08-15 00:39:52Z

3

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.

answered Aug 15, 2012 at 0:39

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Brandon Over a year ago

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!

mu is too short Over a year ago

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

CDub Over a year ago

I'm having the same problem with Rails 3.2.13 and psql (PostgreSQL) 9.2.4. However, I have done the create extension hstore. Help?

CDub Over a year ago

Disregard - syntax problem. See: github.com/softa/activerecord-postgres-hstore

chribsen Over a year ago

This issue solved it for me: stackoverflow.com/questions/14707975/…

Collectives™ on Stack Overflow

Postgres HStore Errors - Unknown Operator

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related