Contents

pg_tablespace

A system table contanining information about tablespaces

pg_tablespace is a system catalogue table containing information about tablespaces.

pg_tablespace was added in PostgreSQL 8.2.

This table 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 16)

            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" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 15)

            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" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_tablespace

pg_tablespace (PostgreSQL 14)

            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" PRIMARY KEY, btree (oid), tablespace "pg_global"
    "pg_tablespace_spcname_index" UNIQUE CONSTRAINT, btree (spcname), tablespace "pg_global"
Tablespace: "pg_global"

Documentation: pg_tablespace

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