Creating Large Objects in postgresql

PostgreSQL supports large objects as related chunks in a pg_largeobject table. You create a large object (separately) then insert a reference to it into your table. Large objects are more difficult to deal with than bytea and require an API beyond pure SQL.

The SQL syntax for creating a table is simple. Assuming you want a primary key named "id", you can create one like:

CREATE TABLE mydata (id INTEGER, my_movie OID);

The OID is a reference to a large object.

Because a large object is potentially too large to hold in memory, you'll probably want to read and write them using a streaming interface. Below is an untested example that uses Java's JDBC interface to create a large object and associate it with an existing record in mydata. Be aware that deleting the record in mydata won't delete the large object.

Path moviePath  = ...
int  primaryKey = ...

connection.setAutoCommit(false); // start a transaction (required for large objects)

try (var inputStream = Files.newInputStream(moviePath)) { // Open the file for reading
    // Insert the movie into a mydata record as a Blob
    try (PreparedStatement ps = connection.prepareStatement(
            "UPDATE mydata SET my_movie = ? WHERE id = ?")) {
        ps.setBlob(1, inputStream);
        ps.setInt(2, primaryKey);
        ps.executeUpdate();
    }
}

connection.commit();

Note that TEXT is not appropriate for movie clips, since PostgreSQL disallows the use of codepoint=0 in TEXT and there may be null bytes in binary data.