CODEWITHSUNDEEP
postgresql
juztcode
juztcode

Reputation: 1345

Creating Large Objects in postgresql

SQL standard provides large object data types for character data (clob) and binary data(blob), and in an example, I found them being declared as: 

attribute_name clob(20KB)
attribute_name blob(10GB)

so, I tried the following:

create table mydata(
my_movie blob(2GB)
);

but, it gave me an error. How is this large object created?

Upvotes: 0

Views: 3632

Answers (1)

David Costanzo
David Costanzo

Reputation: 266

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.

Upvotes: 1

Related Questions