X-Git-Url: https://granicus.if.org/sourcecode?a=blobdiff_plain;f=doc%2Fsrc%2Fsgml%2Fref%2Fcreate_database.sgml;h=9711b1f98e3ebb524b918843099e2e698a112f54;hb=fbb1d7d73f8e23a3a61e702629c53cef48cb0918;hp=c6fff50184dbd45375ba1afe6cec980e14900d6c;hpb=d2a907c6ad28413bf85660ea590e0e21e0d7016c;p=postgresql diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml index c6fff50184..9711b1f98e 100644 --- a/doc/src/sgml/ref/create_database.sgml +++ b/doc/src/sgml/ref/create_database.sgml @@ -1,240 +1,319 @@ - - - - CREATE DATABASE - - SQL - Language Statements - - - - CREATE DATABASE - - - Creates a new database - - - - 1998-04-15 - - -CREATE DATABASE name [ WITH LOCATION = 'dbpath' ] - - - - - 1998-04-15 - - - Inputs - - - - - - name - - - - The name of a database to create. - - - - - - dbpath - - - - An alternate location can be specified as either an - environment variable known to the backend server - (e.g. 'PGDATA2') or as an absolute path name - (e.g. '/usr/local/pgsql/data'). - In either case, the location must be pre-configured - by initlocation. - - - - - - - - - 1998-04-15 - - - Outputs - - - - - - - status - - - - - - - CREATEDB - - - - Message returned if the command completes successfully. - - - - - - WARN: createdb: database "name" already exists. - - - - This occurs if database specified already exists. - - - - - - ERROR: Unable to create database directory directory - - - - -There was a problem with creating the required directory; this operation will - need permissions for the postgres user on the specified location. - - - - - - - - - + + + + + CREATE DATABASE + + + + CREATE DATABASE + 7 + SQL - Language Statements + + + + CREATE DATABASE + create a new database + + + + +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. + + + + + user_name + + + 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. + + + + + template + + + The name of the template from which to create the new database, + or DEFAULT to use the default template + (template1). + + + + + encoding + + + 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. + + + + + lc_collate + + + 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. + + + + + lc_ctype + + + 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. + + + + + tablespace_name + + + 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. + + + + + + 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. + + + - - - 1998-04-15 - - - Description - - - CREATE DATABASE creates a new Postgres database. - The creator becomes the administrator of the new database. - - - - - 1998-04-15 - - - Notes - - - CREATE DATABASE is a Postgres - language extension. - - - Use DROP DATABASE to remove a database. - - - - - - Usage - - - To create a new database: - - -olly=> 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 the POSTGRESQL interactive sql monitor: - Please read the file COPYRIGHT for copyright terms of POSTGRESQL - - type \? for help on slash commands - type \q to quit - type \g or terminate with semicolon to execute query - You are currently connected to the database: template1 - -olly=> create database elsewhere with location = '/home/olly/private_db'; - CREATEDB - - + + 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). + + + - - - Bugs - - - 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. - + + connlimit + + + How many concurrent connections can be made + to this database. -1 (the default) means no limit. + + + + + + + Optional parameters can be written in any order, not only the order + illustrated above. + - - - - - Compatibility - - - - - - 1998-04-15 - - - SQL92 - - - There is no CREATE DATABASE statement in SQL92. - + + Notes + + + CREATE DATABASE cannot be executed inside a transaction + block. + + + + 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 equivalent command in standard SQL is CREATE SCHEMA. + Use to remove a database. - + + + 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. + - - - - + + + Examples + + + To create a new database: + + +CREATE DATABASE lusiadas; + + + + + 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 + + + There is no CREATE DATABASE statement in the SQL + standard. Databases are equivalent to catalogs, whose creation is + implementation-defined. + + + + + See Also + + + + + + + +