Storing binary objects in postgres using Ruby on Rails

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?

Answers


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.


Need Your Help

servletcontextlistener is not invoking

java web-services servlets servlet-listeners

why follow contextlistener is not invoking on first request or server startup.

jquery UI connected sortable (from ul to div)

jquery-ui jquery-ui-sortable

I have 2 sortables. One applied to a and one to a

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.