In this post, we are going to look at what an Oracle Blob is and how we can convert it to PostgreSQL or use it. Blob (Binary large object) is an Oracle data type that is used to store binary data like contents of a file or information like audio, video, and images. PostgreSQL does not have the Blob data type directly, but we can work with it using the methods below.
Large objects
A large object is a value stored outside of a table.
Example
Let's assume you have an image (in jpeg format) downloaded under /tmp/ folder:
[centos@tushar-ldap-docker bin]$ ls /tmp/test.jpg
/tmp/test.jpg
We want to store this image in the PostgreSQL database.
Go to the bin folder of your PG installation and connect to the psql terminal:
[centos@tushar-ldap-docker bin]$ /usr/pgsql-11/bin/psql -U postgres postgres
psql (11.6)
Type "help" for help.
postgres=#
create table test1(n int,n1 oid);
CREATE TABLE
postgres=# insert into test1 values (1,lo_import('/tmp/test.jpg'));
INSERT 0 1
The lo_import() function loads the named file into pg_largeobject and returns an OID (object identifier) value that refers to the large object.
Selecting table test1 will show the OID and not the bits that have made up this photo.
The catalog pg_largeobject (system table) holds the data making up “large objects.” A large object is identified by an OID that is assigned when it is created. Each large object is broken into segments small enough to be stored as rows in pg_largeobject.
The amount of data per page is its LOBLKSIZE (which is currently BLCKSZ/4, or typically 2 kB).
postgres=# select * from test1;
n | n1
---+-------
1 | 16408
(1 row)
postgres=#
postgres=# select distinct loid from pg_largeobject;
loid
-------
16408
(1 row)
To see all large-objects in the current database, We can use “ \lo_list” command inside psql terminal:
postgres=# \lo_list
Large objects
ID | Owner | Description
-------+----------+-------------
16408 | postgres |
(1 row)
Using the function lo_export(), we can write the photo back into a file:
postgres=# SELECT lo_export( 16408, '/tmp/newtest.jpg' );
lo_export
-----------
1
(1 row)
postgres=# \q
Compare both the files using diff utility:
-bash-4.2$ diff /tmp/newtest.jpg /tmp/test.jpg
No difference found.
Using the function lo_unlink(), you can remove large objects from the database:
postgres=# select lo_unlink(16408);
lo_unlink
-----------
1
(1 row)
postgres=# select * from pg_largeobject;
loid | pageno | data
------+--------+------
(0 rows)
postgres=#
Bytea data type
The bytea data type can be used to store the binary string. The storage size for bytea is 1 or 4 bytes plus the actual binary string.
The bytea type supports two external formats for I/O(input) and O/P(output): i.e., hex and escape format. Both of these are always accepted on input. The output format depends on the GUC parameter known as bytea_output. The default value of this parameter is hex.
Please refer to the PostgreSQL documentation for more information on bytea data type and formats: https://www.postgresql.org/docs/12/datatype-binary.html.
Example
Let's assume you have an image (in jpeg format) downloaded under /tmp/ folder:
[centos@tushar-ldap-docker bin]$ ls /tmp/test.jpg
/tmp/test.jpg
We can want to store this image in the PostgreSQL database.
Go to the bin folder of your PG installation and connect to the psql terminal:
[centos@tushar-ldap-docker bin]$ ./psql postgres -U postgres
psql (11.6)
Type "help" for help.
postgres=# create table test(n int, n1 bytea);
CREATE TABLE
postgres=# insert into test values (1,'/tmp/test.jpg');
INSERT 0 1
postgres=# show bytea_output ;
bytea_output
--------------
hex
(1 row)
postgres=# select * from test;
n | n1
---+------------------------------
1 | \x2f746d702f746573742e6a7067
(1 row)
postgres=# set bytea_output ='escape';
SET
postgres=# select * from test;
n | n1
---+---------------
1 | /tmp/test.jpg
(1 row)
Hope it helps!