CODEWITHSUNDEEP
ruby-on-railspostgresqlhstore
user2013350
user2013350

Reputation: 117

Rails 3.1, Postgres 9.1.4, hstore unknown operator error

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

Upvotes: 3

Views: 1682

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 7

Related Questions