Menu
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?
402
PostgreSQL provides two distinct ways to store binary data. Binary data can be stored in a table using the data type bytea or by using the Large Object feature which stores the binary data in a separate table in a special format and refers to that table by storing a value of type oid in your table.
PostgreSQL does not have the BLOB data type. However, you can use the bytea data type for storing the binary string. We will create a new table named company_files to store the binary string. We will store the content of a file in the file_data column.
The bytea data type allows the storage of binary strings or what is typically thought of as “raw bytes”. Materialize supports both the typical formats for input and output: the hex format and the historical PostgreSQL escape format. The hex format is preferred.
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.
88
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
