Creating and Removing a Database
PostgreSQL installs two default template databases. Upon creation, a
new database is cloned from one of these templates. They are template0
, and 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.
Creating a Database
PostgreSQL provides two methods for creating a new database:
the 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
the usecreatedb
right set in the pg_shadow
table.
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 pg_shadow
directly). 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 guest
user.
Example 9-9. Checking usecreatedb rights
template1=> SELECT usecreatedb FROM pg_user WHERE usename='guest'; ...
Get Practical PostgreSQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.