Saving ruby hash to postgres with raw sql

Question

How can I save a ruby hash directly to postgres using sql?

I've tried like this :

INSERT INTO "table" ("created_at", "key", "params") VALUES (now(), 'create', '{:sub => tralal}')

But it inserts the string into the database, how can I insert a hash like I do from ruby when I save the object.

The sql query who performs insert from rails looks like :

INSERT INTO "table" ("created_at", "key", "params") VALUES ($1, $2, $3) RETURNING "id"  [["created_at", Mon, 24 Feb 2014 18:53:58 UTC +00:00], ["key", "create"], ["parameters", "---\n:sub: tralal\n"]

I'm trying to do some inserts with sql for performance gains, but I can't figure out how to put a hash in db instead of string

If I use the converstion to yaml I get:

{:my => 'hash'}.to_yaml
 => "---\n:my: hash\n"

And when I do :

INSERT INTO "table" ("created_at", "key", "params") VALUES (now(), 'create', '---\n:my: hash\n')

Then when I try to get it back in the rails console :

MyClass.last[:parameters].keys.first
 => "---\\n:my"

I get ---\\n:my instead of :my

Postgresql uses hstore to store a hash. postgresql.org/docs/9.0/static/hstore.html — wurde
– wurde, Commented Feb 25, 2014 at 16:12

DiegoSalazar · Accepted Answer · 2014-02-25 15:50:36Z

1

It looks like your code is serializing the hash into YAML, which you can then deserialize when you read it back in. You can use YAML.load for that and it'll turn the serialized hash back into a ruby hash.

To manually read back yaml in from the db you have to parse it with YAML:

Update:

when I do :

INSERT INTO "table" ("created_at", "key", "params") VALUES (now(), 'create', '---\n:my: hash\n')

I can read read and parse it back with:

model = MyClass.last
params = YAML.load model.params
=> {:my=>"hash"}

edited Feb 25, 2014 at 15:50

answered Feb 24, 2014 at 19:26

DiegoSalazar

13.5k2 gold badges42 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HereToLearn Over a year ago

thanks for the reply. I already have the part where I get the values from db. How can I serialize the hash to the format which row sql can understand and save it like that in the db, without changing anything for reading back the values because I already have that bit serialize :params, Hash

DiegoSalazar Over a year ago

Oh well, if you have that declaration in your model then you should have to do anything, when you read it back ActiveRecord will deserialize it for you.

MGrantley · Accepted Answer · 2014-02-25 20:14:34Z

0

I'm not sure why you want to do it this way but:

INSERT INTO "table" ("created_at", "key", "params") VALUES (now(), 'create', '{:sub => tralal}')

should be

INSERT INTO "table" ("created_at", "key", "params") VALUES (now(), 'sub',  'tralal')

That is if you want you key to be sub and the param to be tralal.

The only way to get an actual hash into the db is to use a string though, and you wouldn't separate your values the way you did above. It would be like this:

INSERT INTO "table" ("created_at", "hash") VALUES (now(), '{:sub => tralal}')

And in the console:

MyClass.last.hash

should give you your hash

answered Feb 25, 2014 at 20:14

MGrantley

414 bronze badges

Collectives™ on Stack Overflow

Saving ruby hash to postgres with raw sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related