Dealing with BLOBs in Postgres?

Greyfox asks: "I've got a question and it's been bugging me for a while. I'm trying to do some database programming with Java and PostgresSQL. I've got an array of bytes I'd like to store in the database and I can't quite figure out how to do it. I've tried the method suggested in their old documentation (Which involves doing some JDBC thing with a FileInputStream) and some esoteric BLOB thing involving ResultSet.setBytes() and a more esoteric BLOB thing involving their LargeObjectManager and when I'm at my most successful I get error messages back griping about unexpected EOFs from the connection manager or somesuch. The documentation, example code and web searches have all been profoundly unhelpful. Am I going about this the right way and if I am, what am I missing?"

BLOB data in Postgres

[mozkill]

I am not an expert by any means. I am just poking a question and trying to get you to think about it.

One question:

How sure are you that your database tables are configured to be able to store BLOB data? Don't you need to specify some sort of separate tablespace to store the blob data and then assign a table column to use that space?

Re:BLOB data in Postgres

[Greyfox]

A BLOB is type oid in Postgres. I'm using type oid for the field I want to store the byte array in. At least, that's what all the documentation I've been able to scare up has said to do...

Funny

[JMZero]

Large object documentation [postgresql.org]

Or you can use bytea or TEXT like I do.

-Dave

What's going on with Slashdot?

[m_ilya]

Probably I'll lose some karma but anyway.

When I have a problem of such type and I cannot solve it myself I usually find relevant maillist and ask where. If problem is not very hard chances are that it will be answered in minutes.

I just don't get why editors pass such submissions and people submit them. It just doesn't make any sense. If you post in PostgreSQL maillist chances that in the future another person with same problem will find solution in its archives. But who in good state of mind will search for solution of PostgreSQL very specific problem on Slashdot? On the other side I'm sure that submitter of this query will have to wait longer for reply which could solve his problems (probably forever).

Re:What's going on with Slashdot?

[platypus]

Ok, I'm a bit late, but anyway:

You are so right.

Esp. when you go to google and type in:

postgres 7.1 blob jdbc

you get the following link on the first page of search results:

http://www.postgresql.org/idocs/index.php?jdbc-l o. html

Possible Solution

[Slipped_Disk]

One possible solution I came up with was to UUEncode the data, then store it in a text field.

I never actually implemented this, but it should be fairly easy to do (either outside the DB or using a Postgres user-defined function.

The existing BLOB support is documented briefly Here [postgresql.org], but quite honestly it... well sucks.

Re:Possible Solution

[Greyfox]

Hmm. Perhaps I should look at some other database systems then. I seem to recall that us private users can get Oracle or DB/2 for free or relatively free. No point in trying to use a hammer to drive a screw...

Shameless plug

[fm6]

Interbase [borland.com] has excellent blob support. Borland markets it as an "embedded" database, but in the real world it's used at all scales, including servers that do stock market transactions. The open source version has an active community [ibphoenix.com], including a lot of people who helped create the original closed-source product.

PostgreSQL JDBC docs

[shrike]

You should have a look at the PostgreSQL JDBC documentation, which can be found at http://jdbc.postgresql.org. While you're at it, also search for BYTEA in the PGSQL JDBC mailingslist.

I'm going to be modded down for that but...

[Betcour]

This is one of the reasons I decided not to switch from MySQL to PostgreSQL : the BLOB support in PostgreSQL is very crappy (and the "large object interface" is a horrible hack and pain in the ass to use). (yes I know the latest version doesn't have any limit on row size anymore and could store BLOB without going thru the large object interface).