#345 Hstore pro

Nice to see how to implement Hstore in rails views. Thanks Ryan.

Another thing to keep in mind: Postgres supports functional indexes, i.e index over the output of any arbitrary expression. This means that you can even use indexes when sorting over HSTORE values if you build the right index.

HSTOREs are really powerful tools.

I do hope though, that one day we will get the same versatility in a native JSON column and I think we are on the right track as 9.2 will already have a native JSON type albeit with no functionality aside of validation, but that's up to extension writers to fix.

This is great...I was just looking into a db schema for the other side of your nested model example (where results would be saved and the user would have a chance to edit the results.) HSTORE seems like it would be a good way to store all the survey responses.

I was going to serialize all the question responses and store them in a column of a table named survey_responses but it looks like HSTORE is a more robust solution!

Thanks Ryan, very useful.

I have a requirement for user-definable attributes and found the pointer to Richard's Schneems example was just the ticket for me.

Really enjoying all the PostgreSQL screencasts.

Hey Ryan,
Fantastic coverage of Postgres. I switched to Postgres a year ago and that is my preferred database now. For the RailsCasts community, there are two more excellent references that you may want to look into:

1. Peepcode screencast on Postgres: a bit dated but very very good.

https://peepcode.com/products/postgresql

1. Tekpub's more recent screencasts, equally good

http://tekpub.com/productions/pg

As far as using Postgres with Rails, Railscasts videos are the best.

Bharat

Thanks for the coverage on all the great Postgres features, Ryan. I switched from MySQL a couple of years ago and haven't looked back. This video on hstore is especially timely for the project I'm working on.

The best 9 dollars a month I have spent in my whole life...

Nice post Ryan as always.

I noticed that if I want to set properties with multi-byte string key or key string with spaces, the following format did not work.

p.properties = {rating: "PG-13", runtime: 107}

I needed to write this like follows in my environment.

p.properties = {"Highest Rating" => "PG-13", "時間" => "107"}

did something happen to the source code repository on GitHub? Getting a 404 from the link..... :^(

This screencast kind of convinced me that hstore is not worth the hassle :(. It doesn't look elegant to integrate it and Mongo or something else looks way better.

In any case, thank you Ryan for focus on pg and all your work.

thx ryan, I very much enjoy all the PostgreSQL episodes!

Dude. My nomination to have you sainted or knighted or something!!! Sir Ryan Bates has a certain ring.

You've pretty much converted me from MySQL last week!

Is it possible to search an hstore by value instead of by key? For example, find all records for which value is 'red', regardless of key? The documentation doesn't say and this is likely a deliberate omission.

As an alternative, has anyone tried to build a key-value store using an association? That is, "product hasmany key_values", where key_values is a table with two indexed attributes, key and value.

Hi! I tried to generify the stuff a little bit -- it may be helpful for someone:
https://github.com/metaminded/meta_types

Very nice implementation!
Could be even better if you implemented it somehow with your nested_form and made it dynamic. Do you have a tip on how to make it work with nested_form?
thanks!

How about a bit of DRY. I have this in lib/hstore_accessor.rb ( see https://gist.github.com/2834785 ):

module HstoreAccessor
  def self.included(base)
    base.extend(ClassMethods)
  end
  
  module ClassMethods
    def hstore_accessor(hstore_attribute, *keys)
      Array(keys).flatten.each do |key|
        define_method("#{key}=") do |value|
          send("#{hstore_attribute}=", (send(hstore_attribute) || {}).merge(key.to_s => value))
          send("#{hstore_attribute}_will_change!")
        end
        define_method(key) do
          send(hstore_attribute) && send(hstore_attribute)[key.to_s]
        end
      end
    end
  end
end

ActiveRecord::Base.send(:include, HstoreAccessor)

and in config/initializers/active_record_extensions.rb

require "hstore_accessor"

Then we can do

Class Widget < ActiveRecord::Base
  hstore_accessor :properties, :size, :shape, :smell
end

FYI, I was getting an error when I ran the migration file that was asking to specify an extension version for hstore. Turns out I did not have the postgres contrib module installed. You need to that to add the hstore extension to the postgres db. On Ubuntu I did 'sudo apt-get install postgresql-contrib-9.1'.

Another usage tip. To make creates work (e.g. when using accepts_nested_attributes_for), you'll need serialize :properties, ActiveRecord::Coders::Hstore in your model (assuming your hstore field is called "properties").

I've seen suggestions that this need will go away with Rails 4.0.

Has anyone gotten this to work with pg 8.4? 8.4 supports hstore but does not support the "CREATE EXTENSION" command. I am not sure what to use instead.

Also an interesting talk on the same at RailsConf 2012: Schemaless SQL The Best of Both Worlds

@ryan the gem has moved: https://github.com/engageis/activerecord-postgres-hstore

Thanks for the tutorial very interesting but I am not sure if its the correct usage. If you are going to define the fields in your code by defining accessors then you know what fields you want at code time. If you know what fields you want then why not just use db columns, they are easier to use. As far as I can see this is most useful when you dont know what fields you will have or they can change. What if you deploy this and later decide to change a field? I would rather query the keys in the properties column and use that to define my accessors, field forms and #show page etc. This means u can't rely on an accessor existing, but that feeds back into the same point. It does not seem wise to let the app define a schema in code but let the data remain schema-less in the DB. I think the app should not make any assumptions about what the data is. Whats the advantage of moving your schema into ruby?

Would have been nice to mention that HSTORE columns only take simple text strings but no slightly more complex JSON data (e.g. Array of Hashes). Might have been obvious to some, but I stumbled right passed it.

Does anybody know what implications there are as the keys change within an hstore column over time? Are migrations needed to make the keys consistent in the hstore column?

For example:

initial model class created with hstore column with keys:

:foo :bar

data added to database

one row/record has
"foo"=>"blah", "bar"=>"blah blah"
...
later changes made to model class new key added to hstore column
:baz

more data added to database
...
second row/record has
"foo"=>"blah", "bar"=>"blah blah", "baz"=>"blah blah blah"

Do I have to go back and make sure that the first record has an empty :baz property?

What about the removal of keys? What happens if I remove :foo from my model?

The migration to setup hstore only runs if the account your Rails app is using to access Postgres has superuser access. If you want to grant this temporarily, you can do this at postgres prompt (assuming your own postgres login is a superuser account):

alter user [rails account name] with superuser;
then revoke when done...
alter user [rails account name] with nosuperuser;

How would you type cast as a float rather than an integer?

Need help, i get this error in console when i create new record withe hstore data?

ActiveRecord::StatementInvalid: PG::InternalError: ERROR: Syntax error near ':'

Is Activerecord query supports json data type of PG ?