PostgreSQL installs two default template databases. Upon creation, a
new database is cloned from one of these templates. They are
template1. Of these, you may only connect to
template1. This is because the
template0 database exists
as an empty template, while
template1 may be modified to include commonly
used languages, functions, and even database objects, such as tables, views, or sequences.
Neither of the template databases may be removed from the system.
The following sections cover creating and removing databases from PostgreSQL.
PostgreSQL provides two methods for creating a new database:
CREATE DATABASE SQL command, and the createdb
command-line executable. To use either of these methods requires that you have the necessary
rights. You do not have to be a PostgreSQL superuser to create a database, but you must have
usecreatedb right set in the
If you are unsure of whether or not this right has been granted to your user, check
through a query to the
pg_user view (which in turn queries the
pg_shadow table; only superusers may query the
usecreatedb column in the pg_shadow
table contains a boolean value, which reflects if this right has been granted. Example 9-9 illustrates an example query to the
pg_user view to check for
usecreatedb rights for the
Example 9-9. Checking usecreatedb rights
template1=> SELECT usecreatedb FROM pg_user WHERE usename='guest'; ...