Postgres Large Object

Large objects (LOBs) are those data type collections that are created to contain a large amount of data up to a maximum size that ranges from 8 terabytes to 128 terabytes. It mainly depends on the configuration of a database.

Why large objects are used in Postgresql

We will explain different types of data that are suitable for large objects and their purpose of creation.

• Structured data: This is a simple type of structured data and can be created in a simple table.
• Complex structured data: These are complex structures and are created to manage the features of object-relational of the oracle database like collections and references.
• Unstructured data: These types of data are not interpreted by the database and these kinds of data are not broken into smaller logical structures.

Syntax

# CREATE TABLE TABLE_NAME (name_of_column1 data_type, name_of_column2 data_type, name_of_columnN bytea);

A table for a large object is created with the data type of a specific column having a bytea or blob to store the values for a large object like images or videos.

Implementation

We will create a table with the data type of blob in a column, while other columns have integer and varchar data types. But on the execution of a query, this causes an error by displaying that the ‘blob’ type does not exist.

>> CREATE TABLE tbl_blob (tbl_blob BLOB, id INTEGER, name VARCHAR(10),address VARCHAR(10));

We will create the same table attributes again by having the first column with the data type of ‘BYTEA’. This is also a blob-type data variable type. By using this, we can restore the data of binary type in PostgreSQL.

>> CREATE TABLE tbl_blob (tbl_blob bytea, id INTEGER, name VARCHAR(10),address VARCHAR(10));

This was done to show the difference in using blob and bytea. Most often, blob is not compatible with some versions of PostgreSQL; that’s why we use bytea in the command.

A new sample table will be created again to test the usage of the large object. A table named tbl_blob1 is created.

>> CREATE TABLE tbl_blob1(tbl_blob bytea, id INTEGER, name VARCHAR(10),address VARCHAR(10), class varchar(10));[/cc]

After creating the table, now we will see the description of the table to note the storage type and column types.

>> \d+ tbl_blol1;

You can see in the above image that the column names and the data type are displayed. The method used to access this description is heap sort. To explain the usage of large objects in more than one column, we will again create a table. This table will contain more than one column having the large object data type.

>> CREATE TABLE tbl_blob2(tbl_blob bytea, tbl1_blob bytea, tbl2_blob bytea, id INTEGER, name VARCHAR(10),address VARCHAR(10), class VARCHAR(10));

After the creation, again use the command written below to see the table description.

>> \d+ tbl_blob2;

Till now, we have seen the usage of blob while creating the table. But to add a new column having a data type of bytea and blob, we need an update command. In table tbl_blob1, we have added a column named tbl1_blob that has the data type of bytea.

>> ALTER TABLE tbl_blob1 ADD COLUMN tbl1_blob bytea;

Large object creation in Postgresql

There exists a function to create a large object.

# Oid lo_creat(PGconn *con, INT mode);

In this function, ‘mode’ is a bitmask that is used to describe different attributes of the object that is newly created. Whereas the constants used in the function are present in the library of header file libpq-fs. Here we apply a sample example for the large object creation, in which the value is returned and accepted by a variable.

# inv_oid = lo_creat(INV_read|INV_write);

The read and write are the access types used in the function. A “-1” argument is used as a parameter of the lo_creat() function to create a large object in the given example. This is used to create a new large, empty, showing the index as null.

>> SELECT lo-_creat(-1);

 

A random value is allotted by PostgreSQL. To provide a specific value to create a large object, we provide a value of our own choice in the parameter.

>> SELECT lo_create (9945621);

Import large object

If you want to import a file present in any directory of your operating system as a large object, then this can be used as a function call.

# Oid lo_import(PGconn *conn, const CHAR *name_of_file);

Name_of_file is the specific file you want to import from the system as a large object. The OID value that is assigned to a new large object is the returned value.

Export a large object

Contrary to importing, large objects can also be exported into the operating system. A function call to export the large object is made.

# INT lo_export(PGcon *conn, Oid lobjId, constant CHAR *name_of_file);

OID is shown by lobjId. OID is specified for the object that needs to be exported from PostgreSQL to the Operating system. The argument of the filename is that it specifies the name of the operating system. A large object id is created to support exporting of the file from PostgreSQL to the operating system.

Open a large object that already exists in Postgresql

If you want to open a large object detail that is already present and created before in Postgresql, then the following function is used.

# INT lo_open(PGcon *con, Oid lojId, INT mode);

The Large object is opened for any purpose like reading or writing or for both, any update can be done. ‘lo_open’ an integer type variable, returns the large object for later use in lo_write, lo_read, or lo_close.

Writing/reading data to/from the large object

Write data to large object

# INT lo_write(PGcon *conn, INT fx, const CHAR *buffr, size_t lenn);

The large object fx is used to write in the length of lenn from buffer buffr. The bytes that are written are returned. If the return value is negative, then it means that an error has occurred.

Read data 

# INT lo_read(PGconn *con, INT fx, CHAR *buffr, size_t lenn);

This function reads the lenn length bytes of the large object into a buffer buffr. The value is returned as the writing process does.

Large object removal

>> INT lo_unlink(PGconn *con, Oid lobjId);

We have used the below-cited example using a SELECT command with an OID number. The ‘lo_unlink’ is used to remove the OID.

>> SELECT lo_unlink(9945621);

Implement large object commands

We will use some of the above-mentioned features of a large object on psql shell. The first create command is used to create a table of the large object as we did it above.

>> CREATE TABLE pic ( id INTEGER, name text, pic oid);

 

 Here the table is created to store the information of a picture having the data type of large object id.

Insert values in the table

>> INSERT INTO pic (id, name, pic) VALUES (1, 'my image', lo_import('/etc1/mottd'));

 

 An image from the operating system is selected here.

Conclusion

The article ‘Postgres large object’ contains the description of the specialty of using the large object, its types, and some important functions used in creating, importing, and exporting large PostgreSQL objects. Some of the features are implemented on the PostgreSQL shell to elaborate the creation and working of large objects to store data.