A system function returning metadata about a file

pg_stat_file() is a system function returning metadata about a file or other object on the local filesystem.

pg_stat_file() was added in PostgreSQL 8.1.


pg_stat_file ( filename text [, missing_ok boolean ] )
        → record ( size bigint, access timestamptz, modification timestamptz, change timestamptz, creation timestamptz, isdir boolean )

pg_stat_file() returns a row containing metadata about a file or other object on the local filesystem to which the postgres system user has access.

It indicates whether a filesystem object is a directory, but does not indicate if an object is a symbolic link (symlink).


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


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

Change history


Basic execution of pg_stat_file():

postgres=# SELECT * FROM pg_stat_file(current_setting('data_directory') || '/global/pg_control');
 size |         access         |      modification      |         change         | creation | isdir 
 8192 | 2020-12-24 11:13:59+01 | 2020-12-24 12:04:32+01 | 2020-12-24 12:04:32+01 |          | f
(1 row)

Attempt to query a non-existent file:

postgres=# SELECT * FROM pg_stat_file('/foo/bar.txt');
ERROR:  could not stat file "/foo/bar.txt": No such file or directory

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

postgres=# SELECT * FROM pg_stat_file('/root/bar.txt');
ERROR:  could not stat file "/root/bar.txt": Permission denied


System function

See also

pg_read_binary_file(), pg_read_file()