pg_read_binary_file()

A system function for reading the contents of a 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

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

Categories

System function

See also

pg_read_file(), pg_stat_file()