Creating a Database in PostgreSQL
November 25, 2003
The creation of a database in PostgreSQL is almost too simple. This article will give you a initial view of how to create a database and verify its existence.
This article assumes that you have read what is beginning to become a series in getting a PostgreSQL environment setup and an actual instance up and running. If you need assistance in those categories please visit these past articles: PostgreSQL Software Installation and Between the Software and theDatabase.
Since we have gotten an instance running in PostgreSQL, it is time to create a real database that we can use. Every PostgreSQL instance can manage more than one database and as you go through this article just consider how you might segregate your application or use across the multiple databases that a single instance can handle. Most database vendors that choose this hierarchy of one instance to many databases expect you to segregate databases by usage and thus group application objects into a single database.
How to Create a Database
In order to create a database you must have a running PostgreSQL instance. From previous articles you learned to start an instance by executing the postmaster as show in Figure 1. The output of starting an instance should be similar to the Listing 1.
After the instance is up and running, or if it already is, you need to connect to a database under the control of the instance you wish to create your new database under. If you recall, when creating the instance in a previous article, you also create a template database by default. You can connect to the template database as shown in Figure 2.
The SQL statement to create a database has a very simple form as show in Figure 3. After you enter the statement, which comes back very fast, you should get the "CREATE DATABASE" acknowledgement. Believe it or not, I did have a problem with this simple statement. I had forgotten to put the ";" after the CREATE statement and this cause a bit of havoc until I entered a ";" a few times. All this happened without an error message. If you find yourself in this situation, just look for the "CREATE DATABASE" acknowledgement.
Verifying a Database was Created
You can now verify that you have created a database successfully by connecting to it. Figure 4 shows that I did in fact create the database and am able to connect to it.
If you wish to query a system catalog table or two, to verify you created a database, use the SQL in Listing 2 or Listing 3. Listing 2 shows the database id along with the database name. Listing 3 shows the database name with the database owner id in the datdba column for each of the databases. To determine who this owner is you can query the pg_shadow system table as shown in Listing 4. Each of these three tables is global and viewable across the entire database in instance. Therefore, if I connected to the db1 database and issued the last three SQL statements, I would get the same result.