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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityI 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.
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.
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 (fd<0) then
raise exception 'Failed to open large object %', o;
end if;
perform lowrite(fd, $1);
if (lo_close(fd)<>0) 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;
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.