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 DB. You load the file into the DB using lo_import(), retrieve it from the DB using lo_export() and remove it with the lo_unlink() function.
Example "store a fruit name and image":

CREATE TABLE fruit (name CHAR(30), image OID); INSERT INTO fruit VALUES ('peach', lo_import('/usr/images/peach.jpg')); SELECT lo_export(fruit.image, '/tmp/outimage.jpg') FROM fruit WHERE name = 'peach'; SELECT lo_unlink(fruit.image) FROM fruit; The lo_import() function stores /usr/images/peach.jpg into the DB. 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 DB, 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 DB server runs in a different directory than the psql client.

The postgres user must have permission to read the files