Large Objects (BLOBs)

POSTGRESQL cannot store values of more than several thousand bytes using the data types discussed so far, nor can binary data be easily entered within single quotes. Instead, large objects--also called Binary Large Objects or BLOBS--are used to store very large values and binary data.

  Large objects permit storage of any operating system file, including images or large text files, directly into the database. You load the file into the database using lo_import(), and retrieve it from the database using lo_export().

Figure  shows an example that stores a fruit name and image.  

        test=> CREATE TABLE fruit (name CHAR(30), image OID);
        CREATE
        test=> INSERT INTO fruit
        test-> VALUES ('peach', lo_import('/usr/images/peach.jpg'));
        INSERT 27111 1
        test=> SELECT lo_export(fruit.image, '/tmp/outimage.jpg')
        test-> FROM   fruit 
        test-> WHERE  name = 'peach';
         lo_export 
        -----------
                 1
        (1 row) 
         
        test=> SELECT lo_unlink(fruit.image) FROM fruit;
         lo_unlink 
        -----------
                 1
        (1 row)  

The lo_import() function stores /usr/images/peach.jpg into the database. The function call returns an OID  that is used to refer to the imported large object. This value is stored in fruit.image. The lo_export() function uses the OID value to find the large object stored in the database, then places the image into the new file /tmp/outimage.jpg. The 1 returned by lo_export() indicates a successful export. The lo_unlink()  function removes large objects.

Full path names must be used with large objects because the database server runs in a different directory than the psql client. Files are imported and exported by the postgres user, so postgres must have permission to read the file for lo_import() and directory write permission for lo_export(). Because large objects use the local filesystem, users connecting over a network cannot use lo_import or lo_export(). They can, however, use psql's \lo_import and \lo_export commands.