How to convert Oracle Blobs to PostgreSQL

 

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!