Postgresql – How to retrieve the image which is stored in bytea column of a pg_toast schema

postgresqlpostgresql-9.6

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

Best Answer

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.

Related Solutions

PostgreSQL – How to Insert File Data into a Bytea Column

as superuser:

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function bytea_import(p_path text, p_result out bytea) 
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data 
             from pg_largeobject 
             where loid = l_oid 
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

insert into my_table(bytea_data) select bytea_import('/my/file.name');

PostgreSQL Random Bytea – How to Generate a Random Bytea

Enhancing Jack Douglas's answer to avoid the need for PL/PgSQL looping and bytea concatenation, you can use:

CREATE OR REPLACE FUNCTION random_bytea(bytea_length integer)
RETURNS bytea AS $body$
    SELECT decode(string_agg(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0') ,''), 'hex')
    FROM generate_series(1, $1);
$body$
LANGUAGE 'sql'
VOLATILE
SET search_path = 'pg_catalog';

It's a simple SQL function that's cheaper to call than PL/PgSQL.

The difference in performance due to the changed aggregation method is immense for larger bytea values. Though the original function is actually up to 3x faster for sizes < 50 bytes, this one scales much better for larger values.

Or use a C extension function:

I've implemented a random bytea generator as a simple C extension function. It's in my scrapcode repository on GitHub. See the README there.

It nukes the performance of the above SQL version:

regress=# \a
regress=# \o /dev/null
regress=# \timing on
regress=# select random_bytea(2000000);
Time: 895.972 ms
regress=# drop function random_bytea(integer);
regress=# create extension random_bytea;
regress=# select random_bytea(2000000);
Time: 24.126 ms

Best Answer

Related Solutions

PostgreSQL – How to Insert File Data into a Bytea Column

PostgreSQL Random Bytea – How to Generate a Random Bytea

Related Question