CREATE TABLESPACE

A DDL 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

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

Categories

DDL, Tablespaces

See also

ALTER TABLESPACE, DROP TABLESPACE, tablespace