Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

I'm attempting to store binary data in a database. (postgresql on heroku)

I understand there are two different ways to store binary data in postgresql. A blob and a bytea..

When I create a table in my migration, 

create_table :binaries do |t|
  t.binary :data
end

it creates a column in the database of type bytea.

My question is.. How do I create a record of type blob?

Why do I ask? It seems when I send a ten byte file up to heroku, it stores it as a string of hex values, prepended with an "e".. so my 10 bytes becomes 21. My 10 meg file would become 20 megs (and one byte), ext, ext, ext...
Now that bothers me, but as I don't really care about performance. (I've had the care beaten out of me by the PM), its not what bothers me the most.
What really bothers me is; when I read out the contents of the database I get the 21 bytes, not the 10. That is un-useable.

So my question again.. How do I create a BLOB column in rails/postgresql/heroku environment?

share|improve this question
 
Please don't point out that I should be storing binaries as files. Mine is not to reason why; mine is but to do or ??? –  baash05 Jul 12 '12 at 4:57

1 Answer

up vote 3 down vote accepted

bytea is PostgreSQL's version of a BLOB. From the fine manual:

The SQL standard defines a different binary string type, called BLOB or BINARY LARGE OBJECT. The input format is different from bytea, but the provided functions and operators are mostly the same.

So bytea is what you want. As far as the format goes:

The bytea type supports two external formats for input and output: PostgreSQL's historical "escape" format, and "hex" format. Both of these are always accepted on input. The output format depends on the configuration parameter bytea_output; the default is hex. (Note that the hex format was introduced in PostgreSQL 9.0; earlier versions and some tools don't understand it.)

So what you're seeing is just the text versions that are used for getting data into the database and out of the database.

This might also be of interest:

share|improve this answer
 
THANKS! Really really! How do I get the data out in binary? It's coming out "97" when I put in "a" –  baash05 Jul 12 '12 at 6:21
 
Seems you already answered that. But I don't see a way to get the URL.. –  baash05 Jul 12 '12 at 6:38
 
Found it.. and edited that answer... just to make it easier for the next noob.. stackoverflow.com/questions/8539207/… –  baash05 Jul 12 '12 at 6:57
 
@daveatflow: Thanks for that edit, approved and all that. –  mu is too short Jul 12 '12 at 8:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.