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.
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
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.
