2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_database.sgml,v 1.30 2002/11/15 03:11:17 momjian Exp $
3 PostgreSQL documentation
6 <refentry id="SQL-CREATEDATABASE">
8 <refentrytitle id="sql-createdatabase-title">CREATE DATABASE</refentrytitle>
9 <refmiscinfo>SQL - Language Statements</refmiscinfo>
21 <date>1999-12-11</date>
24 CREATE DATABASE <replaceable class="PARAMETER">name</replaceable>
25 [ [ WITH ] [ OWNER [=] <replaceable class="parameter">dbowner</replaceable> ]
26 [ LOCATION [=] '<replaceable class="parameter">dbpath</replaceable>' ]
27 [ TEMPLATE [=] <replaceable class="parameter">template</replaceable> ]
28 [ ENCODING [=] <replaceable class="parameter">encoding</replaceable> ] ]
31 <refsect2 id="R2-SQL-CREATEDATABASE-1">
33 <date>1999-12-11</date>
42 <term><replaceable class="parameter">name</replaceable></term>
45 The name of a database to create.
50 <term><replaceable class="parameter">dbowner</replaceable></term>
53 Name of the database user who will own the new database,
54 or <literal>DEFAULT</literal> to use the default (namely, the
55 user executing the command).
60 <term><replaceable class="parameter">dbpath</replaceable></term>
63 An alternate file-system location in which to store the new database,
64 specified as a string literal;
65 or <literal>DEFAULT</literal> to use the default location.
70 <term><replaceable class="parameter">template</replaceable></term>
73 Name of template from which to create the new database,
74 or <literal>DEFAULT</literal> to use the default template
75 (<literal>template1</literal>).
80 <term><replaceable class="parameter">encoding</replaceable></term>
83 Multibyte encoding method to use in the new database. Specify
84 a string literal name (e.g., <literal>'SQL_ASCII'</literal>),
85 or an integer encoding number, or <literal>DEFAULT</literal>
86 to use the default encoding.
94 <refsect2 id="R2-SQL-CREATEDATABASE-2">
96 <date>1999-12-11</date>
105 <term><computeroutput>CREATE DATABASE</computeroutput></term>
108 Message returned if the command completes successfully.
114 <term><computeroutput>ERROR: user '<replaceable class="parameter">username</replaceable>' is not allowed to create/drop databases</computeroutput></term>
117 You must have the special <literal>CREATEDB</> privilege to create databases.
118 See <xref linkend="SQL-CREATEUSER" endterm="SQL-CREATEUSER-title">.
125 <term><computeroutput>ERROR: createdb: database "<replaceable class="parameter">name</replaceable>" already exists</computeroutput></term>
128 This occurs if a database with the <replaceable class="parameter">name</replaceable>
129 specified already exists.
135 <term><computeroutput>ERROR: database path may not contain single quotes</computeroutput></term>
138 The database location
139 <replaceable class="parameter">dbpath</replaceable> cannot contain
140 single quotes. This is required so that the shell commands that
141 create the database directory can execute safely.
147 <term><computeroutput>ERROR: CREATE DATABASE: may not be called in a transaction block</computeroutput></term>
150 If you have an explicit transaction block in progress you cannot call
151 <command>CREATE DATABASE</command>. You must finish the transaction first.
157 <term><computeroutput>ERROR: Unable to create database directory '<replaceable>path</replaceable>'.</computeroutput></term>
158 <term><computeroutput>ERROR: Could not initialize database directory.</computeroutput></term>
161 These are most likely related to insufficient permissions on the data
162 directory, a full disk, or other file system problems. The user under
163 which the database server is running must have access to the location.
173 <refsect1 id="R1-SQL-CREATEDATABASE-1">
175 <date>1999-12-11</date>
181 <command>CREATE DATABASE</command> creates a new
182 <productname>PostgreSQL</productname> database.
186 Normally, the creator becomes the owner of the new database.
187 Superusers can create databases owned by other users using the
188 <option>OWNER</> clause. They can even create databases owned by
189 users with no special privileges. Non-superusers with <literal>CREATEDB</>
190 privilege can only create databases owned by themselves.
194 An alternate location can be specified in order to,
195 for example, store the database on a different disk.
196 The path must have been prepared with the
197 <xref linkend="APP-INITLOCATION" endterm="APP-INITLOCATION-title">
201 If the path name does not contain a slash, it is interpreted
202 as an environment variable name, which must be known to the
203 server process. This way the database administrator can
204 exercise control over locations in which databases can be created.
205 (A customary choice is, e.g., <envar>PGDATA2</envar>.)
206 If the server is compiled with <literal>ALLOW_ABSOLUTE_DBPATHS</literal>
207 (not so by default), absolute path names, as identified by
209 (e.g., <filename>/usr/local/pgsql/data</filename>),
214 By default, the new database will be created by cloning the standard
215 system database <literal>template1</>. A different template can be
216 specified by writing <literal>TEMPLATE =</>
217 <replaceable class="parameter">name</replaceable>. In particular,
218 by writing <literal>TEMPLATE = template0</>, you can create a virgin
219 database containing only the standard objects predefined by your
220 version of <productname>PostgreSQL</productname>. This is useful
221 if you wish to avoid copying
222 any installation-local objects that may have been added to
223 <literal>template1</>.
227 The optional encoding parameter allows selection of the database encoding,
228 if your server was compiled with multibyte encoding support. When not
229 specified, it defaults to the encoding used by the selected template
234 Optional parameters can be written in any order, not only the order
238 <refsect2 id="R2-SQL-CREATEDATABASE-3">
240 <date>1999-12-11</date>
246 <command>CREATE DATABASE</command> is a <productname>PostgreSQL</productname>
250 Use <xref linkend="SQL-DROPDATABASE" endterm="SQL-DROPDATABASE-title"> to remove a database.
253 The program <xref linkend="APP-CREATEDB" endterm="APP-CREATEDB-title"> is a
254 shell script wrapper around this command, provided for convenience.
258 There are security and data integrity issues
259 involved with using alternate database locations
260 specified with absolute path names, and by default
261 only an environment variable known to the backend may be
262 specified for an alternate location.
263 See the Administrator's Guide for more information.
267 comment from Olly; response from Thomas...
269 initlocation does not create a PG_VERSION file in the specified location.
270 How will PostgreSQL handle the situation if it is upgraded to an
271 incompatible database version?
273 Hmm. This isn't an issue since the upgrade would do
274 a dump/reload from the main database area also.
275 Not sure if the dump/reload would guarantee that
276 the alternate data area gets refreshed though...
280 Although it is possible to copy a database other than <literal>template1</>
281 by specifying its name as the template, this is not (yet) intended as
282 a general-purpose COPY DATABASE facility.
283 We recommend that databases used as templates be treated as read-only.
284 See the <citetitle>Administrator's Guide</> for more information.
289 <refsect1 id="R1-SQL-CREATEDATABASE-2">
294 To create a new database:
297 <prompt>olly=></prompt> <userinput>create database lusiadas;</userinput>
302 To create a new database in an alternate area <filename>~/private_db</filename>:
305 <prompt>$</prompt> <userinput>mkdir private_db</userinput>
306 <prompt>$</prompt> <userinput>initlocation ~/private_db</userinput>
308 The location will be initialized with username "olly".
309 This user will own all the files and must also own the server process.
310 Creating directory /home/olly/private_db
311 Creating directory /home/olly/private_db/base
313 initlocation is complete.
316 <prompt>$</prompt> <userinput>psql olly</userinput>
318 Welcome to psql, the PostgreSQL interactive terminal.
320 Type: \copyright for distribution terms
321 \h for help with SQL commands
322 \? for help on internal slash commands
323 \g or terminate with semicolon to execute query
326 <prompt>olly=></prompt></computeroutput> <userinput>CREATE DATABASE elsewhere WITH LOCATION = '/home/olly/private_db';</userinput>
327 <computeroutput>CREATE DATABASE</computeroutput>
332 <refsect1 id="R1-SQL-CREATEDATABASE-4">
337 <refsect2 id="R2-SQL-CREATEDATABASE-4">
339 <date>1998-04-15</date>
345 There is no <command>CREATE DATABASE</command> statement in SQL92.
346 Databases are equivalent to catalogs, whose creation is
347 implementation-defined.
353 <!-- Keep this comment at the end of the file
358 sgml-minimize-attributes:nil
359 sgml-always-quote-attributes:t
362 sgml-parent-document:nil
363 sgml-default-dtd-file:"../reference.ced"
364 sgml-exposed-tags:nil
365 sgml-local-catalogs:"/usr/lib/sgml/catalog"
366 sgml-local-ecat-files:nil