Re: Data type for storing images?

I am not sure what your exact purpose is, but in doing a lot of web
work with pgsql, I usually just store the full path to the image in a
varchar field and fetch that right into the IMG SRC attribute when I
need to display the image.

I also have modified a simple web based image uploading script so upon
uploading an image it detects the images width and height and inserts
that into the DB as well. The script is written in PHP, let me know
if it would be helpful to send it to you.

Good Luck

-Ryan Mahoney

On Sun, 11 Mar 2001 22:52:53 +0000 (UTC), <drevil(at)sidereal(dot)kz> wrote:

>
>I would like to store some images in my Postgres database. These will
>all be thumbnails, limited to 4k or 5k. I know that PG has an 8k row
>limit, but this should be fine, because there's not much else in the
>row. Which data type could I use to store them, and is there a
>performance cost for having rows that are this big? The system will
>be using PG7.1, btw.
>
>Thanks
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

I would like to store some images in my Postgres database. These will
all be thumbnails, limited to 4k or 5k. I know that PG has an 8k row
limit, but this should be fine, because there's not much else in the
row. Which data type could I use to store them, and is there a
performance cost for having rows that are this big? The system will
be using PG7.1, btw.

Thanks

<drevil(at)sidereal(dot)kz> writes:
> I would like to store some images in my Postgres database. These will
> all be thumbnails, limited to 4k or 5k. I know that PG has an 8k row
> limit, but this should be fine, because there's not much else in the
> row. Which data type could I use to store them, and is there a
> performance cost for having rows that are this big? The system will
> be using PG7.1, btw.

bytea is probably your best bet. The 8k limit is toast in 7.1, btw.

regards, tom lane

> bytea is probably your best bet. The 8k limit is toast in 7.1, btw.

If you're using JDBC, you may not be able to get bytea types to work. I was
able to get the SQL type OID to work with JDBC's get/setBytes() methods.
You may also want to use 7.1 since I believe it's large object support is
improved.

David

On Sun, Mar 11, 2001 at 08:48:21PM -0500, Tom Lane wrote:
[ . . . ]
> bytea is probably your best bet. The 8k limit is toast in 7.1, btw.

Is that whay you named it TOAST, in order to be able to say that? :)))

Regards, Frank

At 08:48 PM 11-03-2001 -0500, Tom Lane wrote:
><drevil(at)sidereal(dot)kz> writes:
>> I would like to store some images in my Postgres database. These will
>> all be thumbnails, limited to 4k or 5k. I know that PG has an 8k row
>
>bytea is probably your best bet. The 8k limit is toast in 7.1, btw.

What's bytea ?

How do I store and retrieve 0x00 and other binary stuff like this?

Right now I store stuff like this in files on the filesystem. Storing as
base64 in text doesn't sound attractive :). What are the advantages of
using BLOBs vs files on the filesystem?

Cheerio,
Link.

If bytea can be unlimited size in 7.1, does that mean I can finally
put all my 1gb of MP3s into Postgres, thereby combining all my
favorite stuff?

More to the point, what would be a reason for not using bytea to
replace BLOBs completely?

----- Original Message -----
From: <drevil(at)sidereal(dot)kz>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Tuesday, March 13, 2001 9:00 AM
Subject: [GENERAL] Re: Data type for storing images?

>
> If bytea can be unlimited size in 7.1, does that mean I can finally
> put all my 1gb of MP3s into Postgres, thereby combining all my
> favorite stuff?
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>

> More to the point, what would be a reason for not using bytea to
> replace BLOBs completely?

Does anybody know if bytea can be accessed by JDBC yet? I couldn't figure
out how to make it work (setBytes/getBytes) didn't work; only the OID sql
type was working for me with the byte routines.

David

> I am not sure what your exact purpose is, but in doing a lot of web
> work with pgsql, I usually just store the full path to the image in a
> varchar field and fetch that right into the IMG SRC attribute when I
> need to display the image.

Why would a full path in a IMG tag work? Doesn't your web server use
relative paths based on its document root? If not, you have a serious
security hole allowing people to access any file on your system. <wink>

> I also have modified a simple web based image uploading script so upon
> uploading an image it detects the images width and height and inserts
> that into the DB as well. The script is written in PHP, let me know
> if it would be helpful to send it to you.

I'd be interested in this script. You'll probably need to send it either
"in place" thru the list, or as an attachment directly to me.

Thanks,
David

"Gordan Bobic" <gordan(at)freeuk(dot)com> writes:
> More to the point, what would be a reason for not using bytea to
> replace BLOBs completely?

The fact that encoding/decoding is a pain in the neck :-(. The
lo_read/lo_write interfaces are a relatively convenient way to send
and receive binary data, but the ASCII input-output representation that
bytea uses is not.

We really need some binary-friendly data transmission interface like
lo_read/lo_write for bytea fields before they will be ready to take over
the world. I think Jan has some ideas about how to do that, but it's
not done yet.

regards, tom lane