Back to course home page

FORDHAM UNIVERSITY, Fordham College Lincoln Center
CSEU 3500 -- Data Base Systems
Dept. of Computer & Info. Sciences, Spring, 2004

Using PostgreSQL

The PostgreSQL system with the employee database for the Chapter 4 homework is installed on host erdos. The PostgreSQL interactive SQL parser is named psql.

Documentation: A man page for psql is available, which describes the command-line options and gives some examples of how to use it. Use ``man psql'' on erdos to read it.

There is also very complete documentation about the query processing engine and the SQL syntax it accepts. Using any web browser, visit http://www.postgresql.org/idocs/

You can run the PostgreSQL interpreter in interactive mode or batch mode.

Interactive mode: To run SQL in interactive, line-oriented mode, give the command:
psql dbname
where dbname is the name of the database to which you wish to connect. In this mode, commands are entered line by line, in response to a prompt in the form of the database name followed by => for the first line, and -> for subsequent lines.

For the homework Exercise 4.2, the name of the database is employee so you would say
psql employee

The query processor maintains a command history, so you can recall previously typed lines using the up-arrow key, and you can edit a line by moving around with the left and right arrow keys and deleting or inserting text. In PostgreSQL, a SQL command can span many lines, and is not sent to be interpreted until it is terminated either by a semicolon or the special command g, and you press the Enter key.

If you discover an error in a query after several lines have already been typed, you have two basic options for fixing it:

1. Use the r command (reset) to clear the command buffer, then use command-line recall and editing to recall and fix the previously typed lines.
2. Use the e command (edit) to invoke an editor to edit the command buffer. By default, psql uses the vi editor. If you are not comfortable with vi, you can change the editor, but this must be done before the interactive SQL session, by setting one of the environment variables PSQL_EDITOR, EDITOR, or VISUAL to the name of your preferred editor. If you prefer emacs, it is best to use it in client-server mode to avoid the overhead of starting a fresh emacs for every query edit. To do this, prior to starting psql, use the shell command
   setenv PSQL_EDITOR emacsclient
   (This command needs only be done once per login session. You can also put it into the file .cshrc that resides in your home directory, and it will be done automatically.) Then separately fire up emacs, and within emacs, issue the command
   M-x server-start
   Then when you edit a query, a buffer will automatically be opened in the running emacs. When you are finished editing it, save the buffer and give the emacs the command M-x # to send it to psql.

When you are finished using psql, give the command to quit:
q

Batch mode: After you have become familiar with SQL in interactive mode, the best way to produce output to hand in is to run it in batch mode.

To run the SQL interpreter in batch mode, give the command
psql -e -f filename employee
where filename is the name of a previously prepared file of SQL commands. The -e option specifies echoing of the commands as they are processed; otherwise only the output of the queries will appear. You can redirect this output to a file or to the printer, e.g.
psql -e -f filename employee | lpr

The employee database: In order to make the attribute names legal SQL, the schemas for the tables in the employee database have been modified from the ones given in the text, by replacing hyphens with underscores. The table schemas in the PostgreSQL employee database are as follows:

employee ( employee_name, street, city )

works ( employee_name, company_name, salary )

company ( company_name, city )

manages ( employee_name, manager_name )

Practice: For practice, you can try out the example queries in Chapter 4 using the banking enterprise database, which has also been installed on erdos, containing the data shown in Figures 3.1-3.7. The attribute names are the same as those given in the text, except that hyphens have been replaced by underscores. To access this database, on erdos use the command
psql banking

You can also practice creating tables and performing insertion, deletion, and update operations. Your account on erdos has been given permission to create a database, in which you can define any tables you like. The database can be named anything you like, but to avoid collisions with other users on the system, it is best to give it the same name as your login name. To create your database, at the shell prompt say
createdb
This creates your personal database. Since no database name is specified in this command, the default is to name it with your login name. This command only needs to be done once; the database is permanent. Then connect to the database to create tables and to perform queries and updates by saying
psql
Again, the default in the absence of a database name is to use the database with your login name.

Both the employee and the banking databases can be installed on a computer where you have a SQL processor installed. Scripts that create the databases and populate them with data are on the class web page, http://www.dsm.fordham.edu/~moniot/Classes/Database/. These use insert statements in standard SQL to populate the tables, so the scripts should work properly with little or no modification on any reasonable SQL system.