pg_tablespace

A system catalogue table contanining information about tablespaces

pg_tablespace is a system catalogue table containing information about tablespaces.

pg_tablespace was added in PostgreSQL 8.2 and is shared across all databases in a cluster.

psql commands

  • \db lists basic information about tablespaces available to a cluster
  • \db+ displays additional information such as size.

Definition by PostgreSQL version

pg_tablespace (PostgreSQL 13)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null |
 spcname    | name      |           | not null |
 spcowner   | oid       |           | not null |
 spcacl     | aclitem[] |           |          |
 spcoptions | text[]    | C         |          |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 12)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default
------------+-----------+-----------+----------+---------
 oid        | oid       |           | not null |
 spcname    | name      |           | not null |
 spcowner   | oid       |           | not null |
 spcacl     | aclitem[] |           |          |
 spcoptions | text[]    | C         |          |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 11)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default
------------+-----------+-----------+----------+---------
 spcname    | name      |           | not null |
 spcowner   | oid       |           | not null |
 spcacl     | aclitem[] |           |          |
 spcoptions | text[]    |           |          |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 10)

            Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Collation | Nullable | Default
------------+-----------+-----------+----------+---------
 spcname    | name      |           | not null |
 spcowner   | oid       |           | not null |
 spcacl     | aclitem[] |           |          |
 spcoptions | text[]    |           |          |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 9.6)

  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] |
 spcoptions | text[]    |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 9.5)

  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] |
 spcoptions | text[]    |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 9.4)

  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] |
 spcoptions | text[]    |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 9.3)

  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] |
 spcoptions | text[]    |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 9.2)

  Table "pg_catalog.pg_tablespace"
   Column   |   Type    | Modifiers
------------+-----------+-----------
 spcname    | name      | not null
 spcowner   | oid       | not null
 spcacl     | aclitem[] |
 spcoptions | text[]    |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 9.1)

  Table "pg_catalog.pg_tablespace"
   Column    |   Type    | Modifiers
-------------+-----------+-----------
 spcname     | name      | not null
 spcowner    | oid       | not null
 spclocation | text      |
 spcacl      | aclitem[] |
 spcoptions  | text[]    |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 9.0)

  Table "pg_catalog.pg_tablespace"
   Column    |   Type    | Modifiers
-------------+-----------+-----------
 spcname     | name      | not null
 spcowner    | oid       | not null
 spclocation | text      |
 spcacl      | aclitem[] |
 spcoptions  | text[]    |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 8.4)

  Table "pg_catalog.pg_tablespace"
   Column    |   Type    | Modifiers
-------------+-----------+-----------
 spcname     | name      | not null
 spcowner    | oid       | not null
 spclocation | text      |
 spcacl      | aclitem[] |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 8.3)

  Table "pg_catalog.pg_tablespace"
   Column    |   Type    | Modifiers
-------------+-----------+-----------
 spcname     | name      | not null
 spcowner    | oid       | not null
 spclocation | text      |
 spcacl      | aclitem[] |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 8.2)

  Table "pg_catalog.pg_tablespace"
   Column    |   Type    | Modifiers
-------------+-----------+-----------
 spcname     | name      | not null
 spcowner    | oid       | not null
 spclocation | text      |
 spcacl      | aclitem[] |
Indexes:
    "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"
    

Documentation: pg_tablespace

Change history

Examples

postgres=# SELECT * FROM pg_tablespace;
  spcname   | spcowner | spcacl | spcoptions
------------+----------+--------+------------
 pg_default |       10 | ¤      | ¤
 pg_global  |       10 | ¤      | ¤
 space_2    |       10 | ¤      | ¤
 space_1    |       10 | ¤      | ¤
(4 rows)

postgres=# \db
                        List of tablespaces
    Name    |  Owner   |                  Location
------------+----------+--------------------------------------------
 pg_default | postgres |
 pg_global  | postgres |
 space_1    | postgres | /var/lib/pgsql/tblspc/tblspc_dir1
 space_2    | postgres | /var/lib/pgsql/tblspc/tblspc_dir2
(4 rows)
postgres=# \db+ List of tablespaces Name | Owner | Location | Access privileges | Options | Size | Description ------------+----------+-----------------------------------+-------------------+---------+---------+------------- pg_default | postgres | | | | 29 MB | pg_global | postgres | | | | 590 kB | space_1 | postgres | /var/lib/pgsql/tblspc/tblspc_dir1 | | | 0 bytes | space_2 | postgres | /var/lib/pgsql/tblspc/tblspc_dir2 | | | 0 bytes | (4 rows)

Categories

Cluster-wide table, Storage, System catalogue, Tablespaces

See also

tablespace, pg_tablespace_location(), pg_tablespace_size(), pg_tablespace_databases(), CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE