Rails3: Postgres Hstore: How to insert multiple records in a table that has an hstore column

Question

I want to insert multiple records in my table that has an Hstore column. How can I do that? col3: hstore datatype

INSERT INTO my_table (col1, col2, col3) VALUES ("abc",123,'"property1"=>"hello","property2"=>"hi"'), ("xyz",345,'"property1"=>"hello1","property"=>"hi1"'), ("weq",23,'"property1"=>"hello2","property"=>"hi2"')

This format adds the records in hstore in string format. I want it like a key:value hash that i can access

Answer 1

You have two problems with that SQL:

1. SQL string literals are wrapped in single quotes, double quotes are for identifiers (such as table and column names).
2. hstore literals don't use braces, they're generally represented as strings.

Fixing those issues give us:

INSERT INTO my_table (col1, col2, col3) VALUES
('abc', 123, '"property1"=>"hello","property2"=>"hi"'),
('xyz', 345, '"property1"=>"hello1","property"=>"hi1"'),
('wqe', 23,  '"property1"=>"hello2","property"=>"hi2"')