To create a database requires you to have the CREATE DATABASE privilege. If you are a normal user, you don't need to have these permissions. You can use an existing database for most of the examples in this book.
At the Unix command line, use the following to create a database for a bookstore called Book Town:
If you do not have the permission to create a database, then the following message appears:
createdb NOTICE: user (your username) is not allowed to create/destroy databases. createdb: database creation failed on booktown.
This fatal error message means that the site administrator started the postmaster process using the wrong user. It should be restarted as the PostgreSQL database superuser. Usually, this user is named postgres.
Then, create the user that you want to employ as the manager of the other users. Make sure this user is not the system root user because this user will have access and rights to everything within the database. Once you have switched users to postgres, you will be asked for user postgres's password.
If you want to log onto your postgres superuser account in the database and create a database in the psql interactive monitor, use the command:
psql -u template1
The -u option for the psql command means that you want to switch to another user when logging into the specified database. It will prompt you for the user name and password. Then it searches to see if that user has permission to access the database specified.
Template1 is a default database that is created upon installation.
It then asks for a username and password when you use the switch user tag. This is where you can specify the new user name that you want to log in as. Once psql starts, you can create the booktown database:
Example 7-1. Creating a Database
CREATE DATABASE booktown;
If you do not have the permission to create a database, the following message appears:
ERROR: CREATE DATABASE: permission denied
All database names must begin with an alphabetic character and are limited to 32 characters in length. PostgreSQL allows any number of databases to be created at a given site. Once a database is created, the creator of the database automatically becomes it's database administrator.