CREATE DATABASE

Name

CREATE DATABASE  --  Creates a new database on the system.

Synopsis

CREATE DATABASE name
  [ WITH [ LOCATION = 'dbpath' ]
  [TEMPLATE = template ]
  [ENCODING = encoding ]]
  

Parameters

name

Use this parameter to provide the name of the database you are creating.

dbpath

With this parameter you may specify a directory to save the database, or you may specify 'DEFAULT' to save it in the default directory. This field must be entered as a string literal.

template

Specify the name of the template you wish to base the new database off of, or use DEFAULT to use the default template.

encoding

With this parameter you can specify the multibyte encoding method to use for the database. This should be entered as a string literal, or integer encoding number. Alternately, you may use DEFAULT to set this as the default encoding method.

Results

CREATE DATABASE

This message is displayed if the database is created successfully.

ERROR: user 'username' is not allowed to create/drop databases

This error is displayed if your user account does not have the permissions neccessary to create a database. Add permissions this by using the ALTER USER command.

ERROR: createdb: database "name" already exists

This error is displayed if a database of the same name currently exists.

ERROR: database path may not contain single quotes.

This error is displayed if you use single quotes (') within the dbpath parameter (the location to save the database). It is required that you not use single quotes for compatibility with the system's directory creation program.

ERROR: Unable to create database directory 'path', ERROR: Could not initialize database directory.

This error is displayed if it was impossible to save the database files in the path you specified. This can be due to a full disk, insufficient permissions on the specified directory, or other file system problems.

NotePermissions
 

The username the database is running under must have access to the path specified as the save location.

ERROR: CREATE DATABASE: May not be called in a transaction block.

This error is displayed if you attempt to use the CREATE DATABASE during an explicit transaction block. First finish the current transaction, then use CREATE DATABASE .

Description

Use the CREATE DATABASE to create a new database on the system. When you create a new database, the account you are logged in as will automatically become the owner of the new database, so be sure you are logged in correctly before using this command.

To specify a different, non-default directory you wish the new database to be saved in (such as another disk or a network partition), use the dbpath parameter.

NotePrepare your directory
 

The directory you choose to use must be prepared with the initlocation command beforehand.

The directory path will either be interpreted as a literal path in the filesystem, or (if it does not contain any slashes), an environment variable. If the path is intpepreted as a variable, the database server must be able to recognize it for it to be used correctly. It is in this manner that administrators have more control over where on the filesystem databases can be created.

NoteAbsolute Paths
 

If PostgreSQL has been compiled with ALLOW_ABSOLUTE_DBPATHS (by passing cppflags=-d ALLOW_ABSOLUTE_DBPATHS to make after configuration), absolute path names are allowed as well. This is not allowed by default, due to security and data integrity issues that can arise from using database locations specified as absolute paths.

To create the new database, the system will clone the standard system database template (template1). If you wish to use a different database template, specify it with the TEMPLATE = name clause. To create a completely new database (not derived from templates), pass template0 as name of the template to clone from. This new database will contain only standard objects.

Examples

The following example creates a database with the name of booktown .

template1=# CREATE DATABASE booktown;

Alternatively, you can specify a different data directory location to be used by the new database.

   
template1#= CREATE DATABASE booktown WITH LOCATION = '/usr/local/pgsql/booktowndata';
CREATE DATABASE