Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 25, 2003

Creating a Database in PostgreSQL

By James Koopmann

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.

Figure 1
Starting an instance through postmaster script

Success. You can now start the database server using:
    /usr/local/pgsql/bin/postmaster -D /u04/pgsql/data

Listing 1
Output of starting an instance

 [postgres@koopgate postgres]$     /usr/local/pgsql/bin/postmaster -D /u04/pgsql/data
LOG:  database system was shut down at 2003-11-16 07:29:58 MST
LOG:  checkpoint record is at 0/8019BC
LOG:  redo record is at 0/8019BC; undo record is at 0/0; shutdown TRUE
LOG:  next transaction id: 509; next oid: 16976
LOG:  database system is ready

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.

Figure 2
Connect to the template database

[postgres@koopgate postgres]$ psql template1
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit


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.

Figure 3

template1=# CREATE DATABASE db1;

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.

Figure 4
Connecting to my newly created database

[postgres@koopgate data]$ psql db1
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit


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.

Listing 2
Query pg_stat_database system catalog table to see newly created database

db1=# select datid, datname from pg_stat_database;
 datid |  datname  | 
 16976 | db1       | 
     1 | template1 | 
 16975 | template0 | 
(3 rows)

Listing 3
Get database name and database owner

db1=# select datname, datdba from pg_database;
  datname  |datdba | 
 db1       |     1 |
 template1 |     1 |
 template0 |     1 |

Listing 4
Get users that are defined across the databases

db1=# select usename, usesysid from pg_shadow;
 usename  | usesysid |
 postgres |        1 |

PostgreSQL Archives

Latest Forum Threads
Postgresql Forum
Topic By Replies Updated
Creating a Table Where Date has to be Now OR Future Shadowayara 1 June 6th, 03:56 PM
Streaming Replication on a Single Database lbergeson 0 January 30th, 03:19 PM
Error - SQL state: 22P02 raj_db 1 December 20th, 08:30 AM
Searching in multi-dimensional array fields hdany 0 December 1st, 06:46 AM