X-Git-Url: https://granicus.if.org/sourcecode?a=blobdiff_plain;f=doc%2Fsrc%2Fsgml%2Fref%2Fcreate_database.sgml;h=9711b1f98e3ebb524b918843099e2e698a112f54;hb=fbb1d7d73f8e23a3a61e702629c53cef48cb0918;hp=c8de74eee51f1a74d50554c0bca398647b3b199d;hpb=4579e68db24e56141e0ed179f23a6021c6d304d3;p=postgresql
diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml
index c8de74eee5..9711b1f98e 100644
--- a/doc/src/sgml/ref/create_database.sgml
+++ b/doc/src/sgml/ref/create_database.sgml
@@ -1,298 +1,319 @@
+
+ CREATE DATABASE
+
+
-
- CREATE DATABASE
-
+ CREATE DATABASE
+ 7
SQL - Language Statements
+
-
- CREATE DATABASE
-
-
- Creates a new database
-
+ CREATE DATABASE
+ create a new database
+
-
- 1999-12-11
-
-
-CREATE DATABASE name [ WITH LOCATION = 'dbpath' ]
-
-
-
-
- 1999-12-11
-
-
- Inputs
-
-
+
+CREATE DATABASE name
+ [ [ WITH ] [ OWNER [=] user_name ]
+ [ TEMPLATE [=] template ]
+ [ ENCODING [=] encoding ]
+ [ LC_COLLATE [=] lc_collate ]
+ [ LC_CTYPE [=] lc_ctype ]
+ [ TABLESPACE [=] tablespace_name ]
+ [ IS_TEMPLATE [=] istemplate ]
+ [ ALLOW_CONNECTIONS [=] allowconn ]
+ [ CONNECTION LIMIT [=] connlimit ] ]
+
+
+
+
+ Description
+
+
+ CREATE DATABASE creates a new
+ PostgreSQL database.
+
+
+
+ To create a database, you must be a superuser or have the special
+ CREATEDB privilege.
+ See .
+
+
+
+ By default, the new database will be created by cloning the standard
+ system database template1. A different template can be
+ specified by writing TEMPLATE
+ name. In particular,
+ by writing TEMPLATE template0, you can create a virgin
+ database containing only the standard objects predefined by your
+ version of PostgreSQL. This is useful
+ if you wish to avoid copying
+ any installation-local objects that might have been added to
+ template1.
+
+
+
+
+ Parameters
name
- The name of a database to create.
+ The name of a database to create.
- dbpath
+ user_name
- An alternate location where to store the new database in the filesystem.
- See below for caveats.
+ The role name of the user who will own the new database,
+ or DEFAULT to use the default (namely, the
+ user executing the command). To create a database owned by another
+ role, you must be a direct or indirect member of that role,
+ or be a superuser.
-
-
-
-
-
-
- 1999-12-11
-
-
- Outputs
-
-
-
-
- CREATE DATABASE
+ template
- Message returned if the command completes successfully.
+ The name of the template from which to create the new database,
+ or DEFAULT to use the default template
+ (template1).
-
- ERROR: user 'username' is not allowed to create/drop databases
+ encoding
- You must have the special CREATEDB privilege to create databases.
- See .
+ Character set encoding to use in the new database. Specify
+ a string constant (e.g., 'SQL_ASCII'),
+ or an integer encoding number, or DEFAULT
+ to use the default encoding (namely, the encoding of the
+ template database). The character sets supported by the
+ PostgreSQL server are described in
+ . See below for
+ additional restrictions.
-
-
- ERROR: createdb: database "name" already exists
+ lc_collate
- This occurs if a database with the name
- specified already exists.
+ Collation order (LC_COLLATE) to use in the new database.
+ This affects the sort order applied to strings, e.g. in queries with
+ ORDER BY, as well as the order used in indexes on text columns.
+ The default is to use the collation order of the template database.
+ See below for additional restrictions.
-
- ERROR: Single quotes are not allowed in database names.
- ERROR: Single quotes are not allowed in database paths.
+ lc_ctype
- The database name and
- dbpath cannot contain
- single quotes. This is required so that the shell commands that
- create the database directory can execute safely.
+ Character classification (LC_CTYPE) to use in the new
+ database. This affects the categorization of characters, e.g. lower,
+ upper and digit. The default is to use the character classification of
+ the template database. See below for additional restrictions.
-
- ERROR: The path 'xxx' is invalid.
+ tablespace_name
- The expansion of the specified dbpath
- (see below how) failed. Check the path you entered or make sure that the
- environment variable you are referencing does exist.
+ The name of the tablespace that will be associated with the
+ new database, or DEFAULT to use the
+ template database's tablespace. This
+ tablespace will be the default tablespace used for objects
+ created in this database. See
+
+ for more information.
- ERROR: createdb: May not be called in a transaction block.
-
-
- If you have an explicit transaction block in progress you cannot call
- CREATE DATABASE. You must finish the transaction first.
-
-
-
-
-
- ERROR: Unable to create database directory 'xxx'.
- ERROR: Could not initialize database directory.
+ istemplate
+
+
+ If true, then this database can be cloned by any user with CREATEDB
+ privileges; if false (the default), then only superusers or the owner
+ of the database can clone it.
+
+
+
+
+
+ allowconn
+
+
+ If false then no one can connect to this database. The default is
+ true, allowing connections (except as restricted by other mechanisms,
+ such as GRANT/REVOKE CONNECT).
+
+
+
+
+
+ connlimit
- These are most likely related to insufficient permissions on the data
- directory, a full disk, or other file system problems. The user under
- which the database server is running, must have access to the location.
+ How many concurrent connections can be made
+ to this database. -1 (the default) means no limit.
-
-
-
-
-
-
- 1999-12-11
-
-
- Description
-
- CREATE DATABASE creates a new
- PostgreSQL database.
- The creator becomes the owner of the new database.
+ Optional parameters can be written in any order, not only the order
+ illustrated above.
+
-
- An alternate location can be specified in order to,
- for example, store the database on a different disk.
- The path must have been prepared with the
- command.
-
-
- If the path contains a slash, the leading part is interpreted
- as an environment variable, which must be known to the
- server process. This way the database administrator can
- exercise control over at which locations databases can be created.
- (A customary choice is, e.g., 'PGDATA2'.)
- If the server is compiled with ALLOW_ABSOLUTE_DBPATHS
- (not so by default), absolute path names, as identified by
- a leading slash
- (e.g. '/usr/local/pgsql/data'),
- are allowed as well.
-
+
+ Notes
-
-
- 1999-12-11
-
-
- Notes
-
- CREATE DATABASE is a Postgres
- language extension.
+ CREATE DATABASE cannot be executed inside a transaction
+ block.
+
- Use to remove a database.
+ Errors along the line of could not initialize database directory
+ are most likely related to insufficient permissions on the data
+ directory, a full disk, or other file system problems.
+
- The program is a
- shell script wrapper around this command, provided for convenience.
+ Use to remove a database.
- There are security and data integrity issues
- involved with using alternate database locations
- specified with absolute path names, and by default
- only an environment variable known to the backend may be
- specified for an alternate location.
- See the Administrator's Guide for more information.
+ The program is a
+ wrapper program around this command, provided for convenience.
-
-
+
+ Database-level configuration parameters (set via ) are not copied from the template
+ database.
+
+
+
+ Although it is possible to copy a database other than template1
+ by specifying its name as the template, this is not (yet) intended as
+ a general-purpose COPY DATABASE
facility.
+ The principal limitation is that no other sessions can be connected to
+ the template database while it is being copied. CREATE
+ DATABASE will fail if any other connection exists when it starts;
+ otherwise, new connections to the template database are locked out
+ until CREATE DATABASE completes.
+ See for more information.
+
+
+
+ The character set encoding specified for the new database must be
+ compatible with the chosen locale settings (LC_COLLATE and
+ LC_CTYPE). If the locale is C (or equivalently
+ POSIX), then all encodings are allowed, but for other
+ locale settings there is only one encoding that will work properly.
+ (On Windows, however, UTF-8 encoding can be used with any locale.)
+ CREATE DATABASE will allow superusers to specify
+ SQL_ASCII encoding regardless of the locale settings,
+ but this choice is deprecated and may result in misbehavior of
+ character-string functions if data that is not encoding-compatible
+ with the locale is stored in the database.
+
+
+
+ The encoding and locale settings must match those of the template database,
+ except when template0 is used as template. This is because
+ other databases might contain data that does not match the specified
+ encoding, or might contain indexes whose sort ordering is affected by
+ LC_COLLATE and LC_CTYPE. Copying such data would
+ result in a database that is corrupt according to the new settings.
+ template0, however, is known to not contain any data or
+ indexes that would be affected.
+
+
+
+ The CONNECTION LIMIT option is only enforced approximately;
+ if two new sessions start at about the same time when just one
+ connection slot remains for the database, it is possible that
+ both will fail. Also, the limit is not enforced against superusers.
+
-
-
- Usage
-
+
+ Examples
+
To create a new database:
-
-olly=> create database lusiadas;
-
+
+CREATE DATABASE lusiadas;
+
- To create a new database in an alternate area ~/private_db:
-
-
-$ mkdir private_db
-$ initlocation ~/private_db
-Creating Postgres database system directory /home/olly/private_db/base
-
-$ psql olly
-
-Welcome to psql, the PostgreSQL interactive terminal.
-
-Type: \copyright for distribution terms
- \h for help with SQL commands
- \? for help on internal slash commands
- \g or terminate with semicolon to execute query
- \q to quit
-
-olly=> CREATE DATABASE elsewhere WITH LOCATION = '/home/olly/private_db';
-CREATE DATABASE
-
+ To create a database sales owned by user salesapp
+ with a default tablespace of salesspace:
+
+
+CREATE DATABASE sales OWNER salesapp TABLESPACE salesspace;
+
+
+
+
+ To create a database music which supports the ISO-8859-1
+ character set:
+
+
+CREATE DATABASE music ENCODING 'LATIN1' TEMPLATE template0;
+
+
+ In this example, the TEMPLATE template0 clause would only
+ be required if template1's encoding is not ISO-8859-1.
+ Note that changing encoding might require selecting new
+ LC_COLLATE and LC_CTYPE settings as well.
-
-
- Compatibility
-
-
-
-
- 1998-04-15
-
-
- SQL92
-
-
- There is no CREATE DATABASE statement in SQL92.
- Databases are equivalent to catalogs whose creation is implementation-defined.
-
-
+
+ Compatibility
+
+
+ There is no CREATE DATABASE statement in the SQL
+ standard. Databases are equivalent to catalogs, whose creation is
+ implementation-defined.
+
-
-
+
+ See Also
+
+
+
+
+
+
+
+