How to insert image in postgresql?

Question

I am wondering how to insert an image on one of the fields in my postgresql table. I cannot find an appropriate tutorial re this matter. The dataype of the field is oid. Has anyone tried this? Thanks!

Before checking how, think why you want to store an image in database. It's not a good idea to store image in a database http://stackoverflow.com/questions/3748/storing-images-in-db-yea-or-nay — 18bytes, Jul 02 '12 at 06:53
@devsundar: there are valid reasons to do so. And sometimes it's actually faster. — , Jul 02 '12 at 07:35
@a_horse_with_no_name Isn't accessing the image from file system is faster than from database? It will be great if you can mention one key reason so that its useful for everyone. — 18bytes, Jul 02 '12 at 08:27
@devsundar: no, the filesystem is not always faster. Especially when the images are small can be faster (but this depends on so many factors that it's impossible to give a general advise). This question has been beaten to death here on SO, just search for it. You will find loads of answers showing advantages and disadvantages — , Jul 02 '12 at 08:34

score 3 · Answer 1 · answered Jul 02 '12 at 06:50

// All LargeObject API calls must be within a transaction
conn.setAutoCommit(false);

// Get the Large Object Manager to perform operations with
LargeObjectManager lobj = ((org.postgresql.PGConnection)conn).getLargeObjectAPI();

//create a new large object
int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);

//open the large object for write
LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

// Now open the file
File file = new File("myimage.gif");
FileInputStream fis = new FileInputStream(file);

// copy the data from the file to the large object
byte buf[] = new byte[2048];
int s, tl = 0;
while ((s = fis.read(buf, 0, 2048)) > 0)
{
        obj.write(buf, 0, s);
        tl += s;
}

// Close the large object
obj.close();

//Now insert the row into imagesLO
PreparedStatement ps = conn.prepareStatement("INSERT INTO imagesLO VALUES (?, ?)");
ps.setString(1, file.getName());
ps.setInt(2, oid);
ps.executeUpdate();
ps.close();
fis.close();

Found that sample code from here. Really very good bunch of sql operations.

Please do not link to to totally outdated versions (7.3). Use the the current JDBC manual instead: http://jdbc.postgresql.org/documentation/91/binary-data.html — , Jul 02 '12 at 07:35

score 2 · Answer 2 · answered Jul 02 '12 at 06:53

To quote this site,

PostgreSQL database has a special data type to store binary data called bytea. This is a non-standard data type. The standard data type in databases is BLOB.

You need to write a client to read the image file, for example

File img = new File("woman.jpg");
fin = new FileInputStream(img);

con = DriverManager.getConnection(url, user, password);

pst = con.prepareStatement("INSERT INTO images(data) VALUES(?)");
pst.setBinaryStream(1, fin, (int) img.length());
pst.executeUpdate();

score 1 · Answer 3 · answered Jul 02 '12 at 06:57

You can either use the bytea type or the large objects facility. However note that depending on your use case it might not be a good idea to put your images in the DB because of additional load it may put on the DB server.

Rereading your question I notice you mentioned you have a field of type oid. If this is an application you are modifying it suggests to me it is using large objects. These objects get an oid which you then need to store in another table to keep track of them.

How to insert image in postgresql?

3 Answers3