Contents

pg_tablespace_location()

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.

Usage

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

Examples

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
ORDER BY 1;
**************************

                  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)

Categories

System function, Tablespaces

See also

pg_tablespace_databases(), tablespace, tablespace directory, pg_tablespace