Now that Postgres is up and running we can create some databases to experiment with. Here, we describe the basic commands for managing a database.
Most Postgres applications assume that the database name, if not specified, is the same as the name on your computer account.
If your database administrator has set up your account without database creation privileges, then she should have told you what the name of your database is. If this is the case, then you can skip the sections on creating and destroying databases.
Let's say you want to create a database named mydb. You can do this with the following command:
% createdb mydb
If you do not have the privileges required to create a database, you will see the following:
% createdb mydb
NOTICE:user "your username" is not allowed to create/destroy databases
createdb: database creation failed on mydb.
Postgres allows you to create any number of databases at a given site and you automatically become the database administrator of the database you just created. Database names must have an alphabetic first character and are limited to 32 characters in length. Not every user has authorization to become a database administrator. If Postgres refuses to create databases for you, then the site administrator needs to grant you permission to create databases. Consult your site administrator if this occurs.
Once you have constructed a database, you can access it by:
Running the Postgres terminal monitor programs (e.g. psql) which allows you to interactively enter, edit, and execute SQL commands.
Using an existing native frontend tool like pgaccess or ApplixWare (via ODBC) to create and manipulate a database.
Using a language like perl or tcl which has a supported interface for Postgres. Some of these languages also have convenient and powerful GUI toolkits which can help you construct custom applications. pgaccess, mentioned above, is one such application written in tk/tcl and can be used as an example.
Writing a C program using the LIBPQ subroutine library. This allows you to submit SQL commands from C and get answers and status messages back to your program. This interface is discussed further in The PostgreSQL Programmer's Guide.
% psql mydb
You will be greeted with the following message:
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: template1
mydb=>
This prompt indicates that the terminal monitor is listening to you and that you can type SQL queries into a workspace maintained by the terminal monitor. The psql program responds to escape codes that begin with the backslash character, "\" For example, you can get help on the syntax of various Postgres SQL commands by typing:
mydb=> \h
Once you have finished entering your queries into the workspace, you can pass the contents of the workspace to the Postgres server by typing:
mydb=> \g
This tells the server to process the query. If you terminate
your query with a semicolon, the "\g" is
not necessary. psql will
automatically process semicolon terminated queries. To read queries
from a file, say myFile, instead of entering them interactively,
type:
mydb=> \i fileName
To get out of psql and
return to Unix, type
mydb=> \q
and psql will quit and
return you to your command shell. (For more escape codes, type
\h at the monitor prompt.) White space
(i.e., spaces, tabs and newlines) may be used freely in
SQL queries. Single-line comments
are denoted by "--". Everything after
the dashes up to the end of the line is ignored. Multiple-line
comments, and comments within a line, are denoted by
"/* ... */".
If you are the database administrator for the database mydb, you can destroy it using the following Unix command:
% dropdb mydb
This action physically removes all of the Unix files
associated with the database and cannot be undone, so this should
only be done with a great deal of forethought.