Using PostgreSQL at IMADA

In DM505, we will be using the relational database system PostgreSQL. This note explains how to connect to the system at IMADA.

dbhost

The PostgreSQL system resides on the machine dbhost, and you first need to log in to that (using your normal IMADA password):

ssh dbhost

This assumes you are already logged into some other IMADA machine (which itself can be done using ssh from e.g. a machine at home (dbhost itself does not seem to accept ssh connections from non-IMADA machines)).

If you are using ssh for the first time between the two machines in question, you will be asked whether you want to continue the ssh connection (answer "yes"), and you will receive a warning about addition to a list of known hosts (ignore the warning).

The PostgreSQL System

The system is composed of a database server part (the program postmaster), and a client part (the program psql). You will only be using psql, which provides a command-line interface to PostgreSQL.

The PostgreSQL system has it own set of usernames and passwords, which are unrelated to the usernames and passwords of IMADA accounts. For all participants with an existing IMADA account, an PostgreSQL account has been created, with the same username as their IMADA account, and with a password which has been mailed to them. If you do not yet have an IMADA account, contact staff in order to get one, and then contact the lecturer for PostgreSQL account creation. If you have an account, but are unable to connect, contact the lecturer.

Creating Databases

Your first action should be to create a database. Each database is a collection (empty at the beginning) of relations. You can create a new database with the name dbname using the command

createdb dbname

If the command createdb is reported as not found, you will need to set your PATH variable - see below. In the createdb command, if dbname is omitted, the name will default to your IMADA username.

Databases can be destroyed again using the command

dropdb dbname

Again, you may need to set your PATH variable - see below.

Using Databases

When using PostgreSQL, you connect to one database at a time. Your start a connection to a database by

psql dbname

This will put you in interactive mode, which allows you to define relations, insert tuples, and make queries by issuing SQL commands at the prompt.

Note that SQL is a "free format" language, allowing you to write the entire query on a single line. It is also case insensitive. Hence, you may write queries like "select name from student where sid = 101;" in psql.

Beside SQL commands, psql has a set of so-called "meta commands". Among these are

\d : Show a list all relations in the current database.
\q : Quit psql.
\i filename : Read SQL commands from filename and execute them.
\o filename : Write output to filename.

Alternatively, input and output files can be specified when starting psql:

psql -f infile -o outfile

Setting your PATH variable

If your shell is not able to find the PostgreSQL commands createdb, dropdb, and psql, you will need to update your PATH variable. The method depends on the type of shell you are using (which you can find out by the command echo $SHELL).

If your are using tcsh, add the line

setenv PATH ${PATH}:/usr/local/postgresql/bin

to the file .tcshrc in your home directory (creating the file if it does not exist).

If your are using bash, add the line

export PATH=$PATH:/usr/local/postgresql/bin

to the file .bashrc in your home directory (creating the file if it does not exist).

Documentation

PostgreSQL comes with ample documentation. See for instance the SQL description and the refence section (including the man page of psql).

When looking up documentation, note that the Postgresql installation at Imada is version 7.3, which is not the newest available. By the way, the links above is to the documentation for version 7.4, because it is better structured than the documentation for version 7.3 (while the two versions hopefully are not very different).

Sample Test Data

To learn SQL, you need to try out in practice some actual SQL commands. Here is a list of easy SQL examples which you may start out with. However, for more interesting queries, you need more data (relation schemas and relation instances). For your convenience, here is a zip-archive containing sample data (from the database textbook Database Management Systems by Ramakrishnan and Gehrke, ISBN 0-07-115110-9 , here adapted to PostgreSQL syntax). The relations constitute a couple of small (non-related) databases, but the grouping into databases, and the meaning of the relations, are somewhat self-explanatory from the relation and attribute names.

In the archive, the file dbbook.sql contains SQL commands for creating the tables. These can be executed using the \i filename meta command described above. The files with extension .txt contain the data. These can be loaded using the \copy meta-command in psql. For instance, the data for the relation "sailors" can be loaded as follows, assuming the data is in the file sailors.txt in the current directory of psql (note that meta-commands are not terminated by a semicolon):

\copy sailors FROM 'sailors.txt' WITH DELIMITER ',' NULL ''

The details of the \copy meta-command are similar to those of the COPY command in SQL (which for safety reasons requires superuser privileges, since it accesses the world as the user running the databaser server program) - see the documentation for COPY.

The data in the archive cannot be added in arbitrary order, due to the foreign key constraints appearing in the table definitions (try it out and see for yourself).