2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_database.sgml,v 1.21 2001/11/18 00:38:00 tgl Exp $
6 <refentry id="SQL-CREATEDATABASE">
8 <refentrytitle id="sql-createdatabase-title">
11 <refmiscinfo>SQL - Language Statements</refmiscinfo>
23 <date>1999-12-11</date>
26 CREATE DATABASE <replaceable class="PARAMETER">name</replaceable>
27 [ WITH [ LOCATION = '<replaceable class="parameter">dbpath</replaceable>' ]
28 [ TEMPLATE = <replaceable class="parameter">template</replaceable> ]
29 [ ENCODING = <replaceable class="parameter">encoding</replaceable> ] ]
32 <refsect2 id="R2-SQL-CREATEDATABASE-1">
34 <date>1999-12-11</date>
43 <term><replaceable class="parameter">name</replaceable></term>
46 The name of a database to create.
51 <term><replaceable class="parameter">dbpath</replaceable></term>
54 An alternate filesystem location in which to store the new database,
55 specified as a string literal;
56 or <literal>DEFAULT</literal> to use the default location.
61 <term><replaceable class="parameter">template</replaceable></term>
64 Name of template from which to create the new database,
65 or <literal>DEFAULT</literal> to use the default template
66 (<literal>template1</literal>).
71 <term><replaceable class="parameter">encoding</replaceable></term>
74 Multibyte encoding method to use in the new database. Specify
75 a string literal name (e.g., <literal>'SQL_ASCII'</literal>),
76 or an integer encoding number, or <literal>DEFAULT</literal>
77 to use the default encoding.
85 <refsect2 id="R2-SQL-CREATEDATABASE-2">
87 <date>1999-12-11</date>
96 <term><computeroutput>CREATE DATABASE</computeroutput></term>
99 Message returned if the command completes successfully.
105 <term><computeroutput>ERROR: user '<replaceable class="parameter">username</replaceable>' is not allowed to create/drop databases</computeroutput></term>
108 You must have the special CREATEDB privilege to create databases.
109 See <xref linkend="SQL-CREATEUSER" endterm="SQL-CREATEUSER-title">.
116 <term><computeroutput>ERROR: createdb: database "<replaceable class="parameter">name</replaceable>" already exists</computeroutput></term>
119 This occurs if a database with the <replaceable class="parameter">name</replaceable>
120 specified already exists.
126 <term><computeroutput>ERROR: database path may not contain single quotes</computeroutput></term>
129 The database location
130 <replaceable class="parameter">dbpath</replaceable> cannot contain
131 single quotes. This is required so that the shell commands that
132 create the database directory can execute safely.
138 <term><computeroutput>ERROR: CREATE DATABASE: may not be called in a transaction block</computeroutput></term>
141 If you have an explicit transaction block in progress you cannot call
142 <command>CREATE DATABASE</command>. You must finish the transaction first.
148 <term><computeroutput>ERROR: Unable to create database directory '<replaceable>path</replaceable>'.</computeroutput></term>
149 <term><computeroutput>ERROR: Could not initialize database directory.</computeroutput></term>
152 These are most likely related to insufficient permissions on the data
153 directory, a full disk, or other file system problems. The user under
154 which the database server is running must have access to the location.
164 <refsect1 id="R1-SQL-CREATEDATABASE-1">
166 <date>1999-12-11</date>
172 <command>CREATE DATABASE</command> creates a new
173 <productname>Postgres</productname> database.
174 The creator becomes the owner of the new database.
178 An alternate location can be specified in order to,
179 for example, store the database on a different disk.
180 The path must have been prepared with the
181 <xref linkend="APP-INITLOCATION" endterm="APP-INITLOCATION-title">
185 If the path name does not contain a slash, it is interpreted
186 as an environment variable name, which must be known to the
187 server process. This way the database administrator can
188 exercise control over locations in which databases can be created.
189 (A customary choice is, e.g., <envar>PGDATA2</envar>.)
190 If the server is compiled with <literal>ALLOW_ABSOLUTE_DBPATHS</literal>
191 (not so by default), absolute path names, as identified by
193 (e.g., <filename>/usr/local/pgsql/data</filename>),
198 By default, the new database will be created by cloning the standard
199 system database <literal>template1</>. A different template can be
200 specified by writing <literal>TEMPLATE =</>
201 <replaceable class="parameter">name</replaceable>. In particular,
202 by writing <literal>TEMPLATE = template0</>, you can create a virgin
203 database containing only the standard objects predefined by your
204 version of Postgres. This is useful if you wish to avoid copying
205 any installation-local objects that may have been added to
206 <literal>template1</>.
210 The optional encoding parameter allows selection of the database encoding,
211 if your server was compiled with multibyte encoding support. When not
212 specified, it defaults to the encoding used by the selected template
217 Optional parameters can be written in any order, not only the order
221 <refsect2 id="R2-SQL-CREATEDATABASE-3">
223 <date>1999-12-11</date>
229 <command>CREATE DATABASE</command> is a <productname>Postgres</productname>
233 Use <xref linkend="SQL-DROPDATABASE" endterm="SQL-DROPDATABASE-title"> to remove a database.
236 The program <xref linkend="APP-CREATEDB" endterm="APP-CREATEDB-title"> is a
237 shell script wrapper around this command, provided for convenience.
241 There are security and data integrity issues
242 involved with using alternate database locations
243 specified with absolute path names, and by default
244 only an environment variable known to the backend may be
245 specified for an alternate location.
246 See the Administrator's Guide for more information.
250 comment from Olly; response from Thomas...
252 initlocation does not create a PG_VERSION file in the specified location.
253 How will Postgres handle the situation if it is upgraded to an
254 incompatible database version?
256 Hmm. This isn't an issue since the upgrade would do
257 a dump/reload from the main database area also.
258 Not sure if the dump/reload would guarantee that
259 the alternate data area gets refreshed though...
263 Although it is possible to copy a database other than <literal>template1</>
264 by specifying its name as the template, this is not (yet) intended as
265 a general-purpose COPY DATABASE facility.
266 We recommend that databases used as templates be treated as read-only.
267 See the <citetitle>Administrator's Guide</> for more information.
272 <refsect1 id="R1-SQL-CREATEDATABASE-2">
277 To create a new database:
280 <prompt>olly=></prompt> <userinput>create database lusiadas;</userinput>
285 To create a new database in an alternate area <filename>~/private_db</filename>:
288 <prompt>$</prompt> <userinput>mkdir private_db</userinput>
289 <prompt>$</prompt> <userinput>initlocation ~/private_db</userinput>
290 <computeroutput>Creating Postgres database system directory /home/olly/private_db/base</computeroutput>
292 <prompt>$</prompt> <userinput>psql olly</userinput>
294 Welcome to psql, the PostgreSQL interactive terminal.
296 Type: \copyright for distribution terms
297 \h for help with SQL commands
298 \? for help on internal slash commands
299 \g or terminate with semicolon to execute query
302 <prompt>olly=></prompt></computeroutput> <userinput>CREATE DATABASE elsewhere WITH LOCATION = '/home/olly/private_db';</userinput>
303 <computeroutput>CREATE DATABASE</computeroutput>
308 <refsect1 id="R1-SQL-CREATEDATABASE-4">
313 <refsect2 id="R2-SQL-CREATEDATABASE-4">
315 <date>1998-04-15</date>
321 There is no <command>CREATE DATABASE</command> statement in SQL92.
322 Databases are equivalent to catalogs whose creation is implementation-defined.
328 <!-- Keep this comment at the end of the file
333 sgml-minimize-attributes:nil
334 sgml-always-quote-attributes:t
337 sgml-parent-document:nil
338 sgml-default-dtd-file:"../reference.ced"
339 sgml-exposed-tags:nil
340 sgml-local-catalogs:"/usr/lib/sgml/catalog"
341 sgml-local-ecat-files:nil