0

I have a table called photo, that contains 3 columns

  • chunk_id oid
  • chunk_sq int
  • chunk_data bytea

How can I see the stored image?

The (bytea) type column contains data like

'034\350sec\302\340s\221\\030seq\301\037\022\034seq`uence!V!\364s\030ett\241\376\001\310sho\214rt\224\204\002Tize\001\240\370sloDR#\024\002\020\023\273\003\240\360ourc\324\.................'

select * from pg_toast.photo

1 Answer 1

5

Certain graphical DB clients will show you the image when going to the table data view. In plain query output (for example in psql), though, you cannot expect images to appear. Otherwise, you have to 'decode' the bytea.

Here is an example solution (written in PHP) on gis.stackexchange.com:

$conn = pg_connect("...");
$res = pg_query($conn, "SELECT chunk_data AS photo FROM photo WHERE ...");
$raw = pg_fetch_result($res, 'photo');
header('Content-type: image/jpeg');
echo pg_unescape_bytea($raw);

Note:

pg_toast is not usually a schema you query directly from. What is stored there has a 'parent' table, do your queries against it instead. Then you can possibly use sane column names in your queries, and so on.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.