create_tablespace(7)

—

NAME

CREATE_TABLESPACE - define a new tablespace

SYNOPSIS

bash

CREATE TABLESPACE \ntablespace_name\n
\n
    [ OWNER { \nnew_owner\n | CURRENT_ROLE | CURRENT_USER | SESSION_USER } ]
\n
    LOCATION '\ndirectory\n'
\n
    [ WITH ( \ntablespace_option\n = \nvalue\n [, ... ] ) ]

DESCRIPTION

CREATE TABLESPACE registers a new cluster-wide tablespace. The tablespace name must be distinct from the name of any existing tablespace in the database cluster.

A tablespace allows superusers to define an alternative location on the file system where the data files containing database objects (such as tables and indexes) can reside.

A user with appropriate privileges can pass tablespace_name to CREATE DATABASE, CREATE TABLE, CREATE INDEX or ADD CONSTRAINT to have the data files for these objects stored within the specified tablespace.

A tablespace cannot be used independently of the cluster in which it is defined; see Section 22.6.

PARAMETERS

tablespace_name

user_name

directory

tablespace_option

NOTES

CREATE TABLESPACE cannot be executed inside a transaction block.

EXAMPLES

To create a tablespace dbspace at file system location /data/dbs, first create the directory using operating system facilities and set the correct ownership:

bash

mkdir /data/dbs
chown postgres:postgres /data/dbs

Then issue the tablespace creation command inside PostgreSQL:

bash

CREATE TABLESPACE dbspace LOCATION '/data/dbs';

To create a tablespace owned by a different database user, use a command like this:

bash

CREATE TABLESPACE indexspace OWNER genevieve LOCATION '/data/indexes';

COMPATIBILITY

CREATE TABLESPACE is a PostgreSQL extension.