A function returning the file system path of a tablespace

pg_tablespace_location() is a system function returning the file system path of the specified tablespace.

pg_tablespace_location() was added in PostgreSQL 9.2.


pg_tablespace_databases ( tablespace oid ) → setof oid

pg_tablespace_location() was introduced to avoid hard-coding system paths in the pg_tablespace system catalogue table, and is used mainly by metadata queries which need to translate a tablespace's OID to its filepath, for example psql's \db slash command.

pg_tablespace_location() must be provided with the OID of the tablespace to query (note there is currently no regtablespace OID type) and returns the system file path, or NULL if the tablespace is a default one.

Change history


The query used by psql's \db command:

postgres=# \db
********* QUERY **********
SELECT spcname AS "Name",
  pg_catalog.pg_get_userbyid(spcowner) AS "Owner",
  pg_catalog.pg_tablespace_location(oid) AS "Location"
FROM pg_catalog.pg_tablespace

                  List of tablespaces
    Name    |  Owner   |         Location
 pg_default | postgres |
 pg_global  | postgres |
 tblspace_1 | postgres | /var/lib/pgsql/tblspc_dir1
 tblspace_2 | postgres | /var/lib/pgsql/tblspc_dir2

(4 rows)


System function, Tablespaces

See also

pg_tablespace_databases(), tablespace, tablespace directory, pg_tablespace