PostgreSQL is relational database management system. Complete manuals are available from the PostgreSQL site (link). The most useful sections to this class are probably “Tutorial” and “The SQL Language.”

Interactive Use of Command-Line Processor psql

psql is the PostgreSQL command-line processor. To run it, open up a terminal in your virtual machine, and issue the following commands:
cd ~/working-directory/
psql database-name
For example, for Homework #3, your working-directory would be hw03 and your database-name would be congress.

When you are inside psql, you should see the following prompt, waiting for your commands:
database-name=#
When you see this command, you are inside psql instead of the terminal shell, so shell commands (such as ls and cat) would not work. Instead, issue SQL statements or psql commands.

A number of psql commands (they all start with \) are especially useful:

• \d lists all tables and views in the database.
• \d table_or_view_name displays the table or view’s schema.
• \i file.sql executes the SQL statement in the file. This command is very handy when you want to use a text editor to edit SQL instead of typing directly into psql.
• \q exits psql. You will be returned back to the terminal shell.

You can also type SQL statements. You can enter them on multiple lines, and you need to use semicolon ; to signal the end of the statement so psql will begin to execute it. If you ever see the prompt database-name-# (instead of database-name=#), that means psql thinks that you haven’t finished typing your statement yet.

As with most command-line tools, psql has decent support for auto-completion to save you some typing. [TAB] is the auto-completion key. For example, \i f[TAB] will complete the file name starting with f in your working directory (or list all possible alternatives when there are many such files). SEL[TAB] will give you SELECT, and SELECT * FROM per[TAB] will try to complete a table or view name starting with per. psql also remembers your command history. [UP] and [DOWN] keys will cycle through your command history, and you can edit the old commands and issue them.

Whenever psql needs to display a lot of information to you (e.g., a huge result table with many rows), it will enter a “paging” mode, which lets you view the output as if you are looking it using the the shell command less. When you are in this mode, remember the following useful key strokes/sequences:

• [SPACE] goes forward one page, and b goes back one page.
• [UP] scrolls up one line, and [DOWN] scrolls down one line.
• g goes to the beginning of the output, and G goes to the end of the output.
• / lets you search for a string, and n goes to the next match in the output.
• q quits the pager.

If your query hangs (you don’t see any prompt after hitting [RETURN]) and you want to kill it, type [CTRL]-c to abort execution. You should get your psql prompt back.

Two-Window Setup When Working with psql

This setup was used by Jun when giving Lecture #3. Basically, you open one terminal to run psql interactively, and a second window for your favorite text editor—it can be a terminal-based editor such as vim or nano (see shell tips for more info), or a window-based editor such as Leafpad (Start->Accessories->Leafpad in your virtual machine).

You write SQL in a .sql file in the second window, save it (and make sure it’s in the same working directory as psql), and then switch to the terminal running psql and run the file with the \i command.