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
. Then when you use the
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
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
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.
Example 2-22. Creating a 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,
, to access the
newly created database. To do so, enter the command shown in Example 2-23.
Example 2-23. Accessing a database with psql
You can now start entering SQL commands (e.g., such as SELECT) at the
prompt. If you are unfamiliar with
, 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.
Example 2-24. Querying a system table
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