Thread: Data type for storing images?
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@sidereal.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@sidereal.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@sidereal.kz> To: <pgsql-general@postgresql.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 > >
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@sidereal.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@postgresql.org
> 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@freeuk.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
Would a binary cursor solve the problem or the SELECT BINARY ? I support the fact that PG as a whole should send the data to the client as it is stored. It means the system must store the size of the data with its data and do a memcopy rather than a sprintf... Do not use \0 as a delimiter of fields... I haven't yet tried to see if a custom data type can send and store binary data and be read by pgsql libraries. Now that PG is TOAST it must support binary data without support functions or encoding. Cheers. Franck Martin Network and Database Development Officer SOPAC South Pacific Applied Geoscience Commission Fiji E-mail: franck@sopac.org <mailto:franck@sopac.org> Web site: http://www.sopac.org/ <http://www.sopac.org/> Support FMaps: http://fmaps.sourceforge.net/ <http://fmaps.sourceforge.net/> This e-mail is intended for its addresses only. Do not forward this e-mail without approval. The views expressed in this e-mail may not be necessarily the views of SOPAC. -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, 14 March 2001 6:26 To: Gordan Bobic Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Re: Data type for storing images? "Gordan Bobic" <gordan@freeuk.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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html