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?

Answer 1

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.