Re: BLOBs - Mailing list pgsql-sql
På mandag 11. januar 2016 kl. 01:37:52, skrev Eugene Yin <eugeneymail@ymail.com>:
I use the BLOB in an Oracle table to store IMG and document files. Now for Postgres(9.4.5), I have two options, i.e., BYTEA or OID.With consideration of passing the params (SAVING) from the Java side as follows:DiskFileItemDeepy file = myFile; InputStream is = null; long fileSize = 0; if (file != null && file.getFileSize() > 0){ is = file.getInputStream(); fileSize = file.getFileSize(); call.setBinaryStream(1, (InputStream)is, (long)fileSize);}...call.execute();//When retrieve the data use:java.sql.Blob blob = (Blob) resultSet.getBlob(tableColumnName);For the purpose mentioned above, which Postgres data type is a better candidate for replacement the BLOB, BYTEA or OID?
From my experience, always use OID for BLOBs, and use the pgjdbc-ng JDBC-driver here: https://github.com/impossibl/pgjdbc-ng
Maven-config:
<properties> <version.pgjdbc-ng>0.6</version.pgjdbc-ng>
</properties>
<dependency> <groupId>com.impossibl.pgjdbc-ng</groupId> <artifactId>pgjdbc-ng</artifactId> <version>${version.pgjdbc-ng}</version> <classifier>complete</classifier>
</dependency>
In the connection-URL use blobtype=oid:
datasource.url=jdbc:pgsql://localhost:5432/andreak?blob.type=oid
This is the only (as I know of) combination which lets you work with true streams all the way down to PG. This way you can work with very large images/movies/documents without sacrificing memory.
The official JDBC-driver for PG doesn't support BLOBs proparly, no getBlob/createBlob (among other things, like custom type mappings).
-- 
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963