CODEWITHSUNDEEP
sqlruby-on-railspostgresqlhstore
KavitaC
KavitaC

Reputation: 635

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

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

Upvotes: 0

Views: 276

Answers (2)

Belayer
Belayer

Reputation: 14934

I'll provide samples of "access them like any other column" as far as the SQL goes, any conversion to Rails is on you - I don't know it. There is a slight difference in accessing the values in an hstore than any stand alone column. Since hstore consists of key:value pairings you need to specify the key for individual values. That comes in the format of: hstore_column -> 'key_name'. Examples (using above posted data)

-- select value specific key
select col1, col2, col3 -> 'property' 
  from my_table;

-- select entire hstore when value for specific key missing   
select col3 
  from my_table
 where col3 -> 'property' is null;

-- select entire row there value ends in a digit for specific key
select * 
  from my_table
 where col3 -> 'property' ~ '.*\d$'

Hope these help. '

Upvotes: 0

mu is too short
mu is too short

Reputation: 434835

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

Upvotes: 1

Related Questions