> I'm a newbie to PostgreSQL and I'd like to hear some "insight" info about
the "bytea" data type (preferably from people that have actually used them).
> From what I've read so far (online docs), it doesn't seem
> to be that similar to the BLOB data type specified by SQL standard (for
instance, Oracle's BLOB).
I've used regular PG blobs and they don't use bytea, but use the LO
interface underneath and using datatypes of OID in the db. The only issue
for us is that we have to schedule a daily job that runs 'vacuumlo' and our
backup scripts need the options to also backup the LOs. It's worked fine.
> (2) Has somebody worked both with PostgreSQL and Oracle (independently)?
Are there many
> differences between BLOBs through JDBC on either DBMS?
We use both in our code. To use the JDBC blob interfaces, we found we
needed to create a special class that implements the java.sql.Blob interface
and handles the data going in and out of the db. I don't know why JDBC
didn't create some nice Blob wrappers for more traditional stuff, like one
that works with a file descriptor, another with a byte array, etc.
The only difference I recall between the two implementations was on
inserting a new blob. In Oracle, we had to actually insert an empty_blob()
and then follow that up with an SQL UPDATE to set the blob data. I'm not
sure why they need two SQL calls to insert a blob, but that's how it appears
to "work" in Oracle. In PG, you can do the INSERT as expected by specifying
a '?' param in the prepared statement and then using the setBlob() call
passing in your object that implements the Blob interface.
David