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.
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
Note that if reading binary files for storage in the database, the large object API may be more convenient and efficient.
pg_read_binary_file() is restricted to superusers, but other users can be granted the
EXECUTE permission to run this function.
pg_read_binary_file() is implemented in src/backend/utils/adt/genfile.c.
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