Geek Answers Handbook

How to create blob column in reils postgresql database

I am trying to store binary data in a database. (postgresql on heroku)

I understand that there are two different ways to store binary data in postgresql. Drop and byte ..

When I create a table in my migration,

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

it creates a column in a bytea type database.

My question is: .. How to create a blob record?

Why am I asking? It seems that when I send a file with ten bytes to the hero, it saves it as a string of hexadecimal values ​​added with "e" .. so my 10 bytes become 21. My 10 megabytes file will become 20 megabytes (and one byte), ext , ext, ext ...
Now it bothers me, but I don't really care about performance. (I had a care beaten out of me by the Prime Minister), this is not what bothers me the most. What really bothers me; when I read the contents of the database, I get 21 bytes, not 10. This is not applicable.

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

+4
source share
2 answers

bytea is a version of BLOB in PostgreSQL. From the exact guide :

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

So bytea is what you want. Regarding the format:

The bytea type supports two external input and output formats: the PostgreSQL historical "escape" format and the "hex" format. Both of them are always accepted at the entrance. The output format depends on the bytea_output configuration parameter; hex is the default. (Note that the hexadecimal format was introduced in PostgreSQL 9.0, earlier versions and some tools do not understand it.)

So, you only see text versions that are used to get data in and out of the database.

This may also be of interest:

+9
source

The Blob repository is built into Postgres and is supported by the ActiveRecord adapter, but only directly through the original connection and 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 that you can reference blob in your models.

You can add this using migration

 rails g migration AddFileToModel file:oid

Or just like that

 add_column :users, :avatar, :oid

For a working example of this, you should look at the Carrierwave PostgreSQL gem . You can either create your own solution based on this code, or simply use Carrierwave directly. I am currently using it and it works well.

+2
source

More articles:


All Articles