Storing binary objects in postgres using Ruby on Rails

Question

I need to store JSON-like objects in our postgres DB. Originally I was just using serialized fields but they were consuming too much space. So I wrote a simple custom compression scheme and am now using Marshal.dump/load to access the data. But I hit a snag with postgres' bytea field type - it's insisting that every non-visible byte be encoded as a 3-digit octal number, e.g. '\377'.

http://www.postgresql.org/docs/8.1/static/datatype-binary.html

I can't see an easy way to achieve this. s.pack( "m#{s.size}" ) seems to generate strings with single '\' whereas postgres wants '\'. Adding a gsub( /\/, '\\\\' ) on the end doesn't seem to solve it.

Does anyone have a more elegant (and working) solution?

mu is too short · Accepted Answer · 2011-12-17 06:04:03Z

10

Presumably you're using raw SQL to manipulate these bytea values as ActiveRecord takes care of the encoding and decoding of binary columns by itself. Never try to quote data for use in SQL yourself, always use the driver's quoting and escaping methods. In this case, you should be using escape_bytea:

encoded = ActiveRecord::Base.connection.escape_bytea(raw_bytes)

Similarly, you'd use unescape_bytea to decode a bytea if you get an escaped bytea value output of the database.

answered Dec 17, 2011 at 6:04

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Collectives™ on Stack Overflow

Storing binary objects in postgres using Ruby on Rails

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related