Contents

Large object

A special data type which provides stream-style access to data which is too large to manipulate as a whole

A large object is a special data type which provides stream-style access to data which is too large to manipulate as a whole.

A large object can contain up to 4TB of data, compared to the maximum 1GB available with conventional data types via the TOAST mechanism.

Usage

Large objects differ from normal database objects in that they are not manipulated via SQL commands (with the exception of ownership and permissions metadata); instead PostgreSQL provides (via libpq) an API with which they can be uploaded, downloaded and removed. Actual implementation of a user-accessible interface is left to individual applications or database drivers.

psql provides basic commands for manipulating large objects.

SQL commands

psql commands

  • \lo_export LOBOID FILE
  • \lo_import FILE [COMMENT]
  • \lo_list (also implemented as \dl)
  • \lo_unlink LOBOID

Contrib modules

System catalog relations

Change history

Large object support has always been present in PostgreSQL.

Examples

Basic creation and manipulation of a large object using psql's large object slash commands:

postgres=# \lo_import /tmp/random-meme.jpg "Some meme"
lo_import 16458

postgres=# \dl
         Large objects
  ID   |  Owner   | Description
-------+----------+-------------
 16458 | postgres | "Some meme"
(1 row)

postgres=# ALTER LARGE OBJECT 16458 OWNER TO lo_usr;
ALTER LARGE OBJECT

postgres=# \dl
        Large objects
  ID   | Owner  | Description
-------+--------+-------------
 16458 | lo_usr | "Some file"
(1 row)

postgres=# \lo_unlink 16458
lo_unlink 16458

postgres=# \dl
      Large objects
 ID | Owner | Description
----+-------+-------------
(0 rows)

Categories

Data type, Large object, Storage

See also

pg_largeobject, pg_largeobject_metadata