Saving binary objects in postgres using Ruby on Rails

Question

I need to store JSON-like objects in our postgres database. Initially, I just used serialized fields, but they were consuming too much space. So I wrote a simple custom compression and now I use Marshal.dump / load to access the data. But I fell into the trap of the postgres bytea field type - he insisted that every invisible byte is encoded as an octal number of 3 digits, for example. '\ 377'.

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

I do not see a simple way to achieve this. s.pack ("m # {s.size}") seems to generate lines with one "\", whereas postgres wants "\". Adding gsub (/ \ /, '\\\\') to the end doesn't seem to solve it.

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

Answer 1

Presumably, you are using raw SQL to manage these bytea values, since ActiveRecord takes care of encoding and decoding binary columns by itself. Never try to cite data for use in SQL yourself; always use the quoting and evacuation methods of drivers. In this case, you should use escape_bytea :

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

Similarly, you should use unescape_bytea to decode bytea if you get the bytea output bytea output.