Contents
CREATE TABLESPACE
An SQL command for defining a tablespace
CREATE TABLESPACE is a DDL command for defining a tablespace.
CREATE TABLESPACE was added in PostgreSQL 8.0.
Change history
- PostgreSQL 14
CREATE TABLESPACE ... OWNER CURRENT_ROLEoption added (commit 45b98057)
- PostgreSQL 9.5
CREATE TABLESPACE ... OWNER { CURRENT_USER | SESSION_USER }option added (commit 31eae602)
- PostgreSQL 9.4
CREATE TABLESPACE ... WITH ( options )syntax added (commit 5254958e)
- PostgreSQL 8.0
- added (commit 2467394e)
Examples
The tablespace directory must exist:
postgres=# CREATE TABLESPACE foo LOCATION '/tmp/tblspc_test'; ERROR: directory "/tmp/tblspc_test" does not exist
and be owned by the system user under which PostgreSQL runs:
postgres=# CREATE TABLESPACE foo LOCATION '/tmp/tblspc_test'; ERROR: could not set permissions on directory "/tmp/tblspc_test": Operation not permitted
After successful creation:
postgres=# CREATE TABLESPACE foo LOCATION '/tmp/tblspc_test'; CREATE TABLESPACE
a symlink is created from the pg_tblspc directory pointing to the specified tablespace directory:
$ ls -l /var/lib/pgsql/data/pg_tblspc/ total 0 lrwxrwxrwx 1 postgres postgres 16 Nov 6 13:40 16495 -> /tmp/tblspc_test
Attempting to create a tablespace in an existing tablespace directory:
postgres=# CREATE TABLESPACE foo1 LOCATION '/tmp/tblspc_test'; ERROR: directory "/tmp/tblspc_test/PG_14_202010291" already in use as a tablespace
References
- PostgreSQL documentation: CREATE TABLESPACE