Can I copy a Postgresql Large Object on the db server?

Question

I've got data in a large object, now I want to make a copy of it so I can append to it while keeping the copy of the original. Is there any JDBC call or SQL statement I can use to cause that to happen?

From every resource I have found, it seems I have to actually read all the data to my client and write it out again to get the copy. I'd much prefer to save the round trip.

Answer 1

A large object can be copied/cloned with two queries if you know its oid.

INSERT INTO pg_largeobject_metadata (lomowner, lomacl) 
  SELECT lomowner, lomacl
  FROM pg_largeobject_metadata
  WHERE oid = <my_old_oid>
RETURNING oid AS my_new_oid;

INSERT INTO pg_largeobject (loid, pageno, data)
  SELECT <my_new_oid>, pageno, data
  FROM pg_largeobject
  WHERE loid = <my_old_oid>;

my_old_oid is the large object's known oid
my_new_oid is the oid returned by the first insert statement

pg_large_object reference
Object Type Identifier (oid) reference

Answer 2

Have a look at the server-side lo_import and lo_export functions. You will have to move the data from the database to the filesystem and back again, but at least it's the server's filesystem, not the client's.