Creating databases

The next logical step is to create a new database. For this operation, PostgreSQL provides an instruction called CREATE DATABASE. Here is the syntax of this vital command:

postgres=# \h CREATE DATABASE
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ CONNECTION LIMIT [=] connlimit ] ]

The \h command is very convenient to use; it provides you with the syntax of basically every command in the system. In short, \h makes life really easy.

In our case, we can see which options CREATE DATABASE provides. First, we can define the name of the newly created database. The TEMPLATE parameter can be used to physically clone an existing database (if you don't use this one, template1 will be cloned by default). The ENCODING and LC_* parameters are needed in case you want to use encodings and locales different from the default one. Finally, we can make use of a tablespace (which will be dealt with later on in this book), and PostgreSQL provides a way to limit the number of concurrent connections to the database.

In our example, we create a simple database:

postgres=# CREATE DATABASE test;
CREATE DATABASE

If this succeeds, we are ready to connect to our newly created database. We can even do so without psql:

postgres=# \c test
psql (9.4.1, server 9.4.1)
You are now connected to database "test" as user "postgres".