O'Reilly logo

Practical PostgreSQL by John C. Worsley, Joshua D. Drake

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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'; ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required