Rails 3.1, Postgres 9.1.4, hstore unknown operator error

hstore, postgresql, ruby-on-rails

Solution

Maybe it's a simple data type problem in your case, try:

data @> ('complete'::text => 'true'::text)

But the `=>` operator is deprecated (and has been removed in 9.2), it's better to use the `hstore()` format:

(data @> hstore('complete','true'))

Problem

I'm trying to add an hstore data column to an existing STI model. Similar to Postgres HStore Errors - Unknown Operator but as far as I can tell, I HAVE installed the hstore extension. I've dropped the database and rebuilt it from migrations without error but the specs still fail. ``` Mac OS X 10.8.2 Rails 3.1.10 psql (PostgreSQL) 9.1.4 ``` user.rb ``` has_many :targets def complete_targets targets.where("data @> (:key => :value)", key: 'complete', value: 'true') end ``` targets.rb ``` belongs_to :user serialize :data, ActiveRecord::Coders::Hstore ``` setup_hstore migration: ``` class SetupHstore < ActiveRecord::Migration def self.up execute "CREATE EXTENSION IF NOT EXISTS hstore" end def self.down execute "DROP EXTENSION IF EXISTS hstore" end end ``` Add data column migration: ``` class AddDataToRecords < ActiveRecord::Migration def change add_column :records, :data, :hstore end end ``` Add indexes migration: ``` class AddHstoreIndexes < ActiveRecord::Migration def up execute "CREATE INDEX records_gin_data ON records USING GIN(data)" end def down execute "DROP INDEX records_gin_data" end end ``` The Error: ``` ActiveRecord::StatementInvalid: PG::Error: ERROR: operator does not exist: unknown => unknown LINE 1: ...records"."user_id" = 244 AND (data @> ('complete' => 'true')... ``` Navicat output when trying to create the extension via a direct query: ``` CREATE EXTENSION hstore Error : ERROR: extension "hstore" already exists ```

Original source