Practical PostgreSQL
Prev		Next

CREATE DATABASE

Name

CREATE DATABASE -- Creates a new database in PostgreSQL.

Synopsis

CREATE DATABASE name
  [ WITH [ LOCATION = { 'dbpath' | DEFAULT } ]
         [ TEMPLATE = template | DEFAULT ]
         [ ENCODING = encoding_name | encoding_number | DEFAULT ] ]

Parameters

name

The name of the database you are creating.

dbpath

The directory in which to save the database. You may use the DEFAULT keyword to save the database in the default data directory, as specified by the PGDATA environment variable (or -D flag, passed to the postmaster).

Note that the dbpath value must normally be the name of system-level environment variable, which is set within the PostgreSQL user's environment to a value describing an initialized database directory.However, if PostgreSQL was compiled by the gmake command with the CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS argument passed to it, you can also specify a complete, absolute directory path.

template

The name of the template you wish to base the new database off of. Use the DEFAULT keyword to specify the default template (usually template1).

encoding_name | encoding_num

The multibyte encoding method to use for the database. This can be entered as a string literal, or an encoding type's corresponding integer encoding number. See Appendix A, for a list of PostgreSQL's multibyte encoding types.

You may use the DEFAULT keyword to explicitly specify the default encoding method (this is already the default).

Results

CREATE DATABASE

The message returned when a new database is created successfully.

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

The error returned if your PostgreSQL user does not have the createdb privilege necessary to create a database. A database administrator can add permissions to a PostgreSQL user by using the ALTER USER command.

ERROR: Absolute paths are not allowed as database locations

The error returned if dbpath is specified as an absolute system path, and PostgreSQL was not compiled with the CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS argument to gmake.

ERROR: Relative paths are not allowed as database locations

The error returned if dbpath is specified as a relative system path, which is never allowed, as of PostgreSQL 7.1.x. You must set dbpath to the name of the postmaster's configured environment variable describing the location of your intended database location.

ERROR: createdb: database "name" already exists

The error returned if a database named name already exists within the system catalog.

ERROR: database path may not contain single quotes

The error returned if you use single quotes (') within the dbpath parameter (the location to save the database). Such single quotes are not compatible with the system's directory creation program.

ERROR: CREATE DATABASE: unable to create database directory '/path'

The error returned if it is not possible 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 the directory not having been initialized by either initdb or initlocation.

The username under which PostgreSQL's postmaster is running must have access to the path specified as the database location.

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

The error returned if you attempt to use CREATE DATABASE during an explicit transaction block. You cannot use CREATE DATABASE within an open transaction block.

Description

Use CREATE DATABASE to create a new database on the system. When you create a new database, the PostgreSQL user 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.

Note: If absolutely necessary, you can change the owner of a database by performing an UPDATE on the pg_database system table's datdba column to be a different user's PostgreSQL system ID).

The dbpath usually describes an environment variable, which contains the location of the path to create the database in. This environment variable must exist in the environment of the user running the postmaster. In this manner, administrators have more control over where on the filesystem databases can be created. See Chapter 9, for more information on this.

The directory you choose to store the database in must be prepared with the initlocation (or initdb) command beforehand. See Chapter 9 for more on these commands.

Note: If PostgreSQL has been compiled with ALLOW_ABSOLUTE_DBPATHS (by passing CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS to gmake after configuration), absolute pathnames are allowed. 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 a new database, PostgreSQL clones a database template (template1, by default). If you wish to use a different database template, specify it with the TEMPLATE clause. To create a completely new database (with no cloned template objects), pass template0 as the name of the template to clone from.

Examples

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

template1=# CREATE DATABASE booktown;
CREATE DATABASE

This next example specifies the creation of a database with a different data directory location for the new database:

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

Prev	Home	Next
CREATE AGGREGATE	Up	CREATE FUNCTION