CompSci 316 (Fall 2013):
Introduction to Database Systems
Course Information   Lecture Notes   Assignments   Tentative Syllabus   Programming Notes   Homework/Project Submission   Sakai (Grades & Solutions)   Piazza (Q&A)
Getting Started with Windows Azure (Linux) Virtual Machine   Getting Started with Gradiance   PostgreSQL Notes   Using ra   JDBC Notes   Tomcat Notes   Django Notes   PHP Notes   XML Notes

PostgreSQL Notes

Index

General information

The version of PostgreSQL we have installed is 9.1. Complete manuals are available from the PostgreSQL site (link). The most useful sections to this class are probably "Tutorial" and "The SQL Language".

Command-line processor

Interactive mode

After logging in, at a shell prompt, you can start the command-line processor in the interactive mode by entering the following command: 

  psql
When you are inside the command-line processor, you will get a prompt that looks like the following (where USER is your account name): 
  USER=#
You can now use any SQL commands (CREATE, DROP, SELECT, INSERT, DELETE) to manipulate the database. Remember to use semicolon (";") to terminate SQL statements.

Inside psql, you will also find several (non-SQL) psql commands useful (these do not need to be terminated by semicolon):

  • \d: list all tables and views
  • \d NAME: display the schema information for a table or view with NAME
  • \q: quit the command-line processor

The command-line processor provides advanced command-line editing and auto-completion support similar to what you would find in popular interactive shells such as bash.

Note: By default, you will be automatically connected to the server running on the local machine as USER. In particular, you will be connected to a database named USER with default schema named USER. Hence, you do not need to worry about interfering with other users on the machine. To learn about other options available to you (including, for example, working with different databases managed by the database server), type man psql at your shell prompt for the manual.

Immediate mode

You can use psql with the -c option to execute a psql command or SQL statement directly from your shell; e.g.: 

  psql -c "\d"
  psql -c "select * from beer"
After each command/statement completes, control is immediately returned back to your shell. Note that you do not need to end the SQL statements with ; in this case. You may need to enclose command/statement strings in quotes to prevent the shell from interpreting them (for example, the shell would expand * above into a list of files in your current directory, if you do not quote *).

This method of invoking psql may be useful when writing shell scripts. Sometimes you might want to use an extra option -a to have psql echo the command/statement back, which is helpful in debugging the output from scripts.

Batch mode

You can write multiple psql commands and SQL statements in one text file (say, named FILE), and then use the command psql -af FILE to execute them all. Use ";" to signal the end of each SQL statement in the file. You will see examples of this usage in /opt/dbcourse/examples/db-beers/setup.sh, which executes files create.sql, load.sql, and drop.sql in the batch mode.

Loading a database

You can find an example of loading a database from data files in /opt/dbcourse/examples/db-beers/. First, you need to prepare the data files. The example data files in data subdirectory use a delimited ASCII format. Each line of the file represents a row, with columns of the row separated by commas. Then, use the \COPY command to load tables from data (see load.sql for syntax). Remember to check any errors reported.

What if some of the data values contain commas? You should use a different column delimiter, e.g., |. And what if a column has no value and you want to load as having NULL instead of an empty string? The appropriate command would be "\COPY TABLE_NAME FROM 'DATA_FILE' WITH DELIMITER ',' NULL '' CSV".
Last updated Wed Sep 11 10:18:07 EDT 2013