Dump all binary columns from a table as files

I have a table in a postgresql database with a column of type bytea. I want to drop each binary entry as a file using the id as filename.

What would a SQL query look like for this?

To save bytea to the disk server-side, it is necessary to be a database superuser. Normal users are not allowed to write to the filesystem.

Assuming superuser rights, the simplest way is to implement it as a function in one of the "untrusted" languages.

Example in pl/perlu:

CREATE FUNCTION bytea_to_file(bytea,text) RETURNS void AS $
  open(my $fd, ">".$_[1]) or die $!;
  binmode($fd);
  print $fd decode_bytea($_[0]);
  close($fd);
$ language plperlu;

Usage:

select bytea_to_file(bytea_column, concat('/path/to/destination/', id_column)) 
from tablename where...

If such languages are not available in your postgres environment, a non-efficient version can be made in pl/pgsql. It's non efficient because it has to create a temporary large object and copy the entire data into it before exporting it as a file, and then purge the large object.

pl/pgsql version (inefficient)

CREATE FUNCTION bytea_to_file_with_lo(bytea,text) RETURNS void AS $
declare
 o oid;
 fd integer;
 INV_WRITE int := 131072;
begin
 o:=lo_create(-1);
 fd:=lo_open(o, INV_WRITE);
 if (fd0) then
   raise exception 'Failed to close large object %', o;
 end if;

 perform lo_export(o, $2);
 perform lo_unlink(o);
end;
$ language plpgsql;

Usage: the same as pl/perlu version. You still need to be superuser, unless the superuser privileges for this function only are granted to other users (which in the present case would be only OK if 100% trusting the users, otherwise it's disastrous):

alter FUNCTION bytea_to_file_with_lo(bytea,text) SECURITY DEFINER;

psql client-side version (mildly inefficient)

When all you have at your disposal is a client-side psql interpreter, binary contents cannot be extracted directly (it boils down to psql being adverse to the '\0' byte), but an intermediate representation in hex or base64 can easily be obtained and post-processed outside of psql.

Example in shell, assuming existence of the base64 command from GNU coreutils:

$ psql -Atc "select encode(bytea_column,'base64') from tablename" | \
   base64 -d >/path/to/destination/filename

This outputs only one line and column at a time. Extracting another column at the same time (like an ID) would be significantly more difficult for the post-processing, so this simple example assumes some kind of outer loop going through the IDs that would have been obtained previously.

StackExchange Database Administrators Q#83663, answer score: 7