postgres large objects blobs - ghdrako/doc_snipets GitHub Wiki

• https://www.postgresql.org/docs/current/lo-funcs.html

The procedure is to use the lo_import function to load to the database. With lo_ulink you can delete the large object reference in the database. The catalog view pg_largeobject displays the large object data.

CREATE TABLE Auto (name CHAR(30), image OID);
INSERT INTO Auto values('Benz',lo_import('/tmp/Marcedes.jpg'));
postgres=# select * From auto;
-[ RECORD 1 ]-------------------------
name  | Benz
image | 19838
select * from pg_largeobject  where loid=19838 limit 1;
loid   | 19838
pageno | 0
data   | \xffd8ffe000104a46494600010101004800480000ffe20c5849434 
35f50524f46494c4500010100000c484c696e6f021000006d6e747252474220 
58595a2007ce00020009000600310000616373704d53465400000000494543 
20735247420000000000000000000000000000f6d6000100000000d32d48502 
020000000000000000000000000000000000000000000000000000000000000
postgres=# SELECT lo_unlink(19838);
-[ RECORD 1 ]
lo_unlink | 1
postgres=# select * From auto;
-[ RECORD 1 ]-------------------------
name  | Benz
image | 19838
postgres=# select * from pg_largeobject  where loid=19838;
(0 rows)