pg_read_file()

A function for reading the contents of a text file

pg_read_file() is a system function for reading the contents of a text file on the local filesystem.

pg_read_file() was added in PostgreSQL 8.1.

Usage

pg_read_file ( filename text [, offset bigint, length bigint [, missing_ok boolean ]] ) 
        → text

In PostgreSQL 16 and later:

pg_read_file ( filename text [, offset bigint, length bigint ][, missing_ok boolean ] ) 
        → text

pg_read_file() can be used to return the contents of any text file on the local filesystem to which the postgres system user has access.

Note that for reading structured data files into tables, the COPY command and the file_fdw foreign data wrapper will provide more convenient and efficient interfaces.

Permissions

By default pg_read_file() is restricted to superusers, but other users can be granted the EXECUTE permission to run this function.

Source

pg_read_file() is implemented in src/backend/utils/adt/genfile.c.

Change history

PostgreSQL 16
- can be called as pg_read_file ( filename text, missing_ok boolean ) (commit 283129e3)
PostgreSQL 9.1
- able to read an entire file, rather than specified segments (commit 03db44ea)
PostgreSQL 8.1
- added (commit b609695b)

Examples

Read the PostgreSQL instance's postmaster.pid file:

postgres=# SELECT pg_read_file(current_setting('data_directory') || '/postmaster.pid');
    pg_read_file     
---------------------
 3568221            +
 /var/lib/pgsql/data+
 1608776039         +
 5432               +
 /tmp               +
 *                  +
 113475654  37224658+
 ready              +
 
(1 row)

Attempt to read a non-existent file:

postgres=# SELECT pg_read_file('/foo/bar.txt');
ERROR:  could not open file "/foo/bar.txt" for reading: No such file or directory

Attempt to read a file for which permissions are not available:

postgres=# SELECT pg_read_file('/root/password.txt');
ERROR:  could not open file "/root/password.txt" for reading: Permission denied

Attempting to read a binary file will probably result in an encoding error of some kind:

postgres=# SELECT pg_read_file(current_setting('data_directory') || '/global/pg_control');
ERROR:  invalid byte sequence for encoding "UTF8": 0xb9

See pg_read_binary_file().

References

PostgreSQL documentation: Generic File Access Functions

Version charts

Useful overviews

Useful links

Contents