Postgresql – How to select bytea data as binary data, inserted as hex-encoded from binary

postgresql

The post How to insert (file) data into a PostgreSQL bytea column was helpful.

However, when I select these columns, I get the same hex encoded data.

For instance:

> select substring(smallblob,0,64) from longdata where blobi d=1;
\xd0cf11e0a1b11ae1000000000000000000000000000000003b000300feff09000600000000000 00000000000010000006a0000000000000000100000670000 
(1 row)

How can I SELECT this data in binary format? CAST' andCONVERT` do not appear to support a 'binary' option.

Best Answer

You're not going to get binary data out of psql and SELECT. psql will format it for readability. You could try something with lo_export similar to the answer in How to insert (file) data into a PostgreSQL bytea column to get the data written to a file.

However, most PostgreSQL client adapters (perl DBI, Ruby 'pg', etc) can give you the data in binary when you SELECT bytea columns. Just write a little program using an appropriate adapter in the language of your choice.

Related Solutions

Postgresql – How to insert (file) data into a PostgreSQL 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');

Mysql – How to migrate large blob table from thesql to postgresql

I think that simplest way is to use that --hex-blob switch on mysqldump and restore by psql, with decode(string text, type text). However it's not that simple, because you need to change a little produced dump (sed, awk), adding that decode function. For example:

mysqldump -u root -p --skip-quote-names --hex-blob --skip-triggers \
    --compact --no-create-info mysql samples > prepg.dump

sed "s/0x\([0-9,A-F]*\))/decode('\1','hex'))/g" prepg.dump > pg.dump

psql session:

CREATE TABLE samples
(
    file_id integer PRIMARY KEY,
    file bytea
);

\i 'path/to/pg.dump'

Best Answer

Related Solutions

Postgresql – How to insert (file) data into a PostgreSQL bytea column

Mysql – How to migrate large blob table from thesql to postgresql

Related Question