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.