pg_tablespace
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 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
- PostgreSQL 9.2
- column
spclocation
removed (commit 16d8e594)
- column
- PostgreSQL 9.0
- column
spcoptions
added (commit d86d51a9)
- column
- PostgreSQL 8.0
- added (commit 2467394e)
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)
References
- PostgreSQL documentation: pg_tablespace