How do you create a BLOB column in rails postgresql database

Question

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?

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
– baash05, Commented Jul 12, 2012 at 4:57

Community · Accepted Answer · 2017-05-23 12:01:43Z

12

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:

ActiveRecord loads binary field incorrectly on Heroku, fine on OSX

edited May 23, 2017 at 12:01

CommunityBot

11 silver badge

answered Jul 12, 2012 at 5:30

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

baash05 Over a year ago

THANKS! Really really! How do I get the data out in binary? It's coming out "97" when I put in "a"

baash05 Over a year ago

Found it.. and edited that answer... just to make it easier for the next noob.. stackoverflow.com/questions/8539207/…

Chris Nicola Over a year ago

This seems incorrect. Postgres has a separate BLOB type wiki.postgresql.org/wiki/BinaryFilesInDB. It is not the same as bytea which does not support streaming.

mu is too short Over a year ago

@ChrisNicola But Rails (AFAIK) doesn't know anything about PostgreSQL's Large Object system and LOs aren't really columns.

Chris Nicola Over a year ago

No, it doesn't have an higher level API but it is supported on the raw connection via lo_open, lo_read, lo_write. It would be pretty easy to write your own API on top of that though, it's just an OID reference. Also there is Carrierwave support github.com/diogob/carrierwave-postgresql

|

mu is too short · Accepted Answer · 2014-08-07 23:16:43Z

3

Blob storage is built into Postgres and is supported using the ActiveRecord adapter, but only directly through the raw connection and the lo_* methods. You can use lo_write, lo_open, lo_close and lo_read to create and manipulate blobs. Creating a blob returns an OID which you can reference the blob in your models.

You can add this using a migration

rails g migration AddFileToModel file:oid

Or directly like this

add_column :users, :avatar, :oid

For a working example of this you should look at the Carrierwave PostgreSQL gem. You can either build a custom solution based on that code, or just use Carrierwave directly. I'm currently using it and it works well.

edited Aug 7, 2014 at 23:16

mu is too short

436k71 gold badges863 silver badges822 bronze badges

answered Aug 7, 2014 at 21:16

Chris Nicola

14.6k6 gold badges49 silver badges61 bronze badges

Collectives™ on Stack Overflow

How do you create a BLOB column in rails postgresql database

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related