pg_read_binary_file()
pg_read_binary_file()
is a system function for reading the contents of a binary file on the local filesystem.
pg_read_binary_file()
was added in PostgreSQL 9.1.
Usage
pg_read_binary_file()
can be used to return the contents of any binary file on the local filesystem to which the postgres
system user has access. The contents are returned as bytea
.
Note that if reading binary files for storage in the database, the large object API may be more convenient and efficient.
Permissions
By default pg_read_binary_file()
is restricted to superusers, but other users can be granted the EXECUTE
permission to run this function.
Source code
pg_read_binary_file()
is implemented in src/backend/utils/adt/genfile.c.
Change history
- PostgreSQL 9.1
- added (commit 03db44ea)
Examples
Basic execution of pg_read_binary_file()
, here reading the PG_VERSION
file from the instance's data directory as bytea
:
postgres=# SELECT pg_read_binary_file(current_setting('data_directory') || '/PG_VERSION'); pg_read_binary_file --------------------- \x31340a (1 row)
Convert a text file into the database's encoding:
postgres=# SELECT pg_read_file('/tmp/convert.txt'); ERROR: invalid byte sequence for encoding "UTF8": 0xfc $ cat /tmp/convert.txt �berbewertete �thiopische �dnis
$ file -i /tmp/convert.txt /tmp/convert.txt: text/plain; charset=iso-8859-1 postgres=# SELECT convert_from(pg_read_binary_file('/tmp/convert.txt'), 'ISO_8859_1'); convert_from --------------------------------- überbewertete äthiopische Ödnis+ (1 row)
Attempt to read a non-existent file:
postgres=# SELECT pg_read_binary_file('/foo/bar.bin'); ERROR: could not open file "/foo/bar.bin" for reading: No such file or directory
Attempt to read a file for which permissions are not available:
postgres=# SELECT pg_read_binary_file('/root/bar.bin'); ERROR: could not open file "/root/bar.bin" for reading: Permission denied
References
- PostgreSQL documentation: Generic File Access Functions