Can I store arrays in hstore with Rails

Question

I want to save data like this:

User.create(name:"Guy", properties:{url:["url1","url2","url3"], street_address:"asdf"})

Can I do so in Rails 4? So far, I have tried migration: add_column :users, :properties, :hstore, array: true

But when I save the array in hstore, it returns error:

PG::InvalidTextRepresentation: ERROR: array value must start with "{" or dimension information

Answer 1

hstore is intended for simple key/value storage, where both the keys and values are simple unstructured strings. From the fine manual:

F.16. hstore

This module implements the store data type for storing sets of key/value pairs within a single PostgreSQL value. [...] Keys and values are simply text strings.

Note the last sentence: keys and values in hstore are strings. That means that you can't put an array in an hstore value without some handholding to convert the array to and from a string and you really don't want to be messing around with that sort of thing.

However, there is a JSON data type available:

8.14. JSON Type

The json data type can be used to store JSON (JavaScript Object Notation) data, as specified in RFC 4627.

and JSON can easily handle embedded arrays and objects. Try using JSON instead:

add_column :users, :properties, :json

You'll have to remove the old hstore column first though.

Also, you didn't want array: true on your hstore column as you weren't storing an array of hstores, you just wanted one of them.

Answer 2

Add on Mu's answer. Hstore is also giving very promising update in a few month (Postgresql 9.4 will launch in 3rd quarter of 2014).

Some highlights of the coming changes which should address these limitations:

• Support for scalars and types (numeric, boolean, strings, NULL) support, along with new corresponding operators
• Support for nesting and arrays (the authors propose that output format, i.e. brackets v. curly braces, be configured with GUC variables)
• Essentially, full compatibility between hstore and JSON, so JSON documents can now take full advantage of hstore’s indexes (with GIN in particular, the authors ballparked a 120x speed improvement for JSON search performance)

It is very hard to pick one between hstore and json right now. Because they are just getting way too similar and updating too quickly.

Answer 3

My 2 cents to Mu's answer. I'm posting this as an answer cause I don't have enough reputation to add a comment.

JSON is becoming the go-to solution for storing "complex" data.

Oleg Bartunov--one of the authors of hstore--himself stated that there is no advantage using hstore over JSON and he encourages people use jsonb.

On Mar 23, 2014 jsonb, a structured format for storing json, was formally introduced in the pgsql development mailing list.

On May 15, 2014 JSONB was listed in the PostgreSQL 9.4 Beta 1 release announcement.

JSONB: 9.4 includes the new JSONB "binary JSON" type. This new storage format for document data is higher-performance, and comes with indexing, functions and operators for manipulating JSON data.