Re: Storing images in PG? - Mailing list pgsql-general

> I am creating a DB which is going to have to store a lot of small
> images.  Nothing will be over 10k.  This is with PG7.2.  I have a few
> questions: Should I use blob or bytea (neither seem to be well
> documented), and how do I access that binary information?  For
> instance, if I have this table in PG:
>
> CREATE TABLE imagetable (
>        ....
>        image BYTEA,
>        ....
> );
>
> and then I do this in PHP:
>
> $result = pg_exec($db, "SELECT image FROM imagetable WHERE ...");
> $ary = pg_fetch_array($result, 0);
> $imagedata = $ary["image"];
>
> Will all of the above work?
>
> If I can handle images in the DB, this is going to be the coolest
> wireless app.

I'll stay out of the images should be stored in the database vs filesystem
discussion -- I've seen it many times and it always gets religious ;-) But
regardless of why you want to store binary data in PostgreSQL, here's some
info based on recent experience.

I have found (and confirmed by studying the PostgreSQL source) that to
reliably insert arbitrary binary data into a bytea column there are only 3
characters which need to be escaped: \000, \047 ( ' ), \134 ( \ ). Here's
the PHP function that I've been using:

function sqlesc($ct)
{
 $buf = "";
 for ($i = 0; $i < strlen($ct); $i++)
 {
  if (ord($ct[$i]) == 0)
   $buf .= "\\\\000";
  else if (ord($ct[$i]) == 39)
   $buf .= "\\\\047";
  else if (ord($ct[$i]) == 92)
   $buf .= "\\\\134";
  else
   $buf .= $ct[$i];
 }
 return $buf;
}

As far as storage is concerned, all escaped characters get converted back
into their single byte equivilent for storage, so using bytea is the most
efficient way to store binary in the database. Encoding to base64 or hex and
storing as text wastes cpu cycles and storage space, at least on INSERT.
However on retrieval, PostgreSQL will escape all "nonprintable" characters
(based on the C "isprint()" function), which is quite a few. Your PHP app
will have to unescape all of the nonprintable characters. I haven't written
a function for that yet, but it shouldn't be too hard.

I guess another option would be to encode the retrieved data as base64, like
this (works in 7.2devel *only*):

    SELECT encode(image, 'hex') FROM imagetable WHERE ...

And then convert from hex back to bin:

function hex2bin($data)
{
 $data = trim($data);
 $len = strlen($data);
 return pack("H" . $len, $data);
}


If PHP could open a binary cursor (which I don't think it can, but could be
wrong), the retrieval conversion step could be avoided also (I think). I
might just try to hack PHP and add that capability, but no promises ;)

Hope this helps,

-- Joe