Skip to content
Search

PostgreSQL:CreateDatabase

lastmod 2021-03-04created 2021-02-253 edits

CREATE DATABASE — create a new database

Synopsis 

CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

Template Database

CREATE DATABASE actually works by copying an existing database. By default, it copies the standard system database named template1. That database is therefore the "template" from which new databases are made. If you add objects to template1, these objects will be copied into subsequently created user databases. This makes site-local modifications to the standard set of objects in databases possible. For example, if you install the procedural language PL/Perl in template1, it will automatically be available in user databases without any extra work when those databases are created.

There is a second standard system database named template0. This database contains the same data as the initial contents of template1, that is, only the standard objects predefined by your version of PostgreSQL. template0 should never be changed after the database cluster has been initialized. By running CREATE DATABASE to copy template0 instead of template1, you can create a "pristine" user database that contains none of the site-local additions in template1. This is particularly handy when restoring a pg_dump dump: the dump script should be restored into a pristine database so that it correctly recreates the dumped database without conflicting with objects that might have been added to template1 later.

Another common reason to copy template0 instead of template1 is that a copy of template1 must use the same settings, whereas a copy of template0 can specify new encoding and locale settings. template1 contains data related to encoding or locale, but template0 does not.

To create a database by copying template0: 

CREATE DATABASE dbname TEMPLATE template0;

See also

Favorite site

author Zer0 sitemap