Step 10: Creating a Database

Now that the PostgreSQL database system is running, you have the option of using the default database, template1. If you create a new database, and you would like all of your consecutive databases to have the same system-wide options, then you should first configure the template1 database to have those options enabled. For instance, if you plan to use the PL/pgSQL language to program, then you should install the PL/pgSQL language into template1 before using createdb . Then when you use the createdb command, the database created will inherit template1’s objects, and thus, inherit the PL/pgSQL language. For more information on installing the PL/pgSQL language into a database, refer to Chapter 11.

The next step will be to create a new database. This will be a simple test database. We do not recommend using the default template1 database for testing purposes. As you have not created any users with database-creation rights, you will want to make sure that you are logged in as the postgres user when adding a new database. You can also create users that are allowed to add databases, which is discussed later in Chapter 10. To create a new database named testdb, enter the command shown in Example 2-22.

$ 
createdb testdb

CREATE DATABASE 

You should receive a message that says CREATE DATABASE, indicating that creation of the database was successful. You can now use PostgreSQL's command line interface, psql , to access the newly created database. To do so, enter the command shown in Example 2-23.

$ 
psql testdb

You can now start entering SQL commands (e.g., such as SELECT) at the psql prompt. If you are unfamiliar with psql , please see Chapter 4 for an introduction.

To verify that the database is working correctly, you can issue the command shown in Example 2-24, which should give you a listing of the languages installed in the database.

testdb=# 
SELECT * FROM pg_language;

 lanname  | lanispl | lanpltrusted | lanplcallfoid | lancompiler
----------+---------+--------------+---------------+-------------
 internal | f       | f            |             0 | n/a
 C        | f       | f            |             0 | /bin/cc
 sql      | f       | f            |             0 | postgres
(3 rows)