PostgreSQL Large Object with Examples

Create table with large object support

Table is optional. Even there is no table created, large object can still be created via these lo_ functions.

create table t (id int primary key, loc oid);

Populate table with different methods of large object construction

-- obtain bytea data, and create lo using lo_from_bytea 

mytest=> select 'test string'::bytea;
          bytea
--------------------------
 \x7465737420737472696e67
(1 row)

mytest=> insert into t values (1,lo_from_bytea(0,'\x7465737420737472696e67'));
INSERT 0 1

-- combine above example into single step

mytest=> insert into t values (2,lo_from_bytea(0,'test string1'::bytea));
INSERT 0 1

-- create lo first, populate data and insert into table with lo oid

mytest=> select lo_create(0);
 lo_create
-----------
     16418
(1 row)

mytest=> begin;
BEGIN
mytest=*> SELECT pg_catalog.lo_open('16418',131072);
 lo_open
---------
       0
(1 row)

mytest=*> SELECT pg_catalog.lowrite(0,'test string2'::bytea);
 lowrite
---------
      12
(1 row)

mytest=*> SELECT pg_catalog.lo_close(0);
 lo_close
----------
        0
(1 row)

mytest=*> commit;
COMMIT

mytest=> insert into t values (3,'16418');
INSERT 0 1

-- create lo oid directly and populate data

mytest=> select max(loid) from pg_catalog.pg_largeobject;
  max
-------
 16418
(1 row)

mytest=> insert into t values (4, lo_create(16419));
INSERT 0 1

mytest=> select pg_catalog.lo_put('16419',0,'test string3'::bytea);
 lo_put
--------

(1 row)

Examples to query data

mytest=> select * from pg_largeobject;
 loid  | pageno |            data
-------+--------+----------------------------
 16416 |      0 | \x7465737420737472696e67
 16417 |      0 | \x7465737420737472696e6731
 16418 |      0 | \x7465737420737472696e6732
 16419 |      0 | \x7465737420737472696e6733
(4 rows)

mytest=> select * from pg_largeobject_metadata;
  oid  | lomowner | lomacl
-------+----------+--------
 16416 |    16397 |
 16417 |    16397 |
 16418 |    16397 |
 16419 |    16397 |
(4 rows)

mytest=> select id, encode(lo_get(loc),'escape') lo_to_text  from t;
 id |  lo_to_text
----+--------------
  1 | test string
  2 | test string1
  3 | test string2
  4 | test string3
(4 rows)

mytest=> select id, loc, lo_get(loc), encode(lo_get(loc),'escape') lo_to_text  from t;
 id |  loc  |           lo_get           |  lo_to_text
----+-------+----------------------------+--------------
  1 | 16416 | \x7465737420737472696e67   | test string
  2 | 16417 | \x7465737420737472696e6731 | test string1
  3 | 16418 | \x7465737420737472696e6732 | test string2
  4 | 16419 | \x7465737420737472696e6733 | test string3
(4 rows)

Delete rows and large objects

mytest=> delete from t where id=1;
DELETE 1
mytest=> select lo_unlink('16417');
 lo_unlink
-----------
         1
(1 row)

mytest=> select * from pg_largeobject;
 loid  | pageno |            data
-------+--------+----------------------------
 16416 |      0 | \x7465737420737472696e67
 16418 |      0 | \x7465737420737472696e6732
 16419 |      0 | \x7465737420737472696e6733
(3 rows)

mytest=> select id, loc, lo_get(loc)  from t;
ERROR:  large object 16417 does not exist

$ vacuumlo -h rdspg.ciscfgtmcrn3.us-east-1.rds.amazonaws.com mytest -v
Connected to database "mytest"
Checking loc in public.t
Successfully removed 1 large objects from database "mytest".

mytest=> delete from t where id=3;
DELETE 1
select lo_unlink('16418');
 lo_unlink
-----------
         1
(1 row)

Export data using pg_dump

$ pg_dump -h rdspg.ciscfgtmcrn3.us-east-1.rds.amazonaws.com

--
-- PostgreSQL database dump
--

-- Dumped from database version 15.4
-- Dumped by pg_dump version 15.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: t; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE public.t (
    id integer NOT NULL,
    loc oid
);


ALTER TABLE public.t OWNER TO postgres;

--
-- Name: 16419; Type: BLOB; Schema: -; Owner: postgres
--

SELECT pg_catalog.lo_create('16419');


ALTER LARGE OBJECT 16419 OWNER TO postgres;

--
-- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY public.t (id, loc) FROM stdin;
4	16419
\.


--
-- Data for Name: BLOBS; Type: BLOBS; Schema: -; Owner: -
--

BEGIN;

SELECT pg_catalog.lo_open('16419', 131072);
SELECT pg_catalog.lowrite(0, '\x7465737420737472696e6733');
SELECT pg_catalog.lo_close(0);

COMMIT;

--
-- Name: t t_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY public.t
    ADD CONSTRAINT t_pkey PRIMARY KEY (id);


--
-- PostgreSQL database dump complete
--

Use LO Extension

-- clean up

mytest=> drop table t;
DROP TABLE
mytest=> select lo_unlink('16419');
 lo_unlink
-----------
         1
(1 row)

mytest=> select * from pg_largeobject;
 loid | pageno | data
------+--------+------
(0 rows)

-- create extension and table using the lo extension
mytest=> create extension lo;
CREATE EXTENSION

mytest=> create table t (id int primary key, loc lo);
CREATE TABLE

mytest=> create or replace trigger t_trigger_cleanup_lo before update or delete on t
mytest-> for each row execute function lo_manage(loc);
CREATE TRIGGER

-- populate data

mytest=> insert into t values (1,lo_from_bytea(0,'test string1'::bytea));
INSERT 0 1
mytest=> insert into t values (2,lo_from_bytea(0,'test string2'::bytea));
INSERT 0 1
mytest=> insert into t values (3,lo_from_bytea(0,'test string3'::bytea));
INSERT 0 1
mytest=> insert into t values (4,lo_from_bytea(0,'test string4'::bytea));
INSERT 0 1

mytest=> select * from pg_largeobject;
 loid  | pageno |            data
-------+--------+----------------------------
 16438 |      0 | \x7465737420737472696e6731
 16439 |      0 | \x7465737420737472696e6732
 16440 |      0 | \x7465737420737472696e6733
 16441 |      0 | \x7465737420737472696e6734
(4 rows)

-- delete data
mytest=> delete from t where id in (1,3);
DELETE 2

-- verify data, both data and lo delete without orphan records
mytest=> select * from t;
 id |  loc
----+-------
  2 | 16439
  4 | 16441
(2 rows)

mytest=> select * from pg_largeobject;
 loid  | pageno |            data
-------+--------+----------------------------
 16439 |      0 | \x7465737420737472696e6732
 16441 |      0 | \x7465737420737472696e6734
(2 rows)