Using PostgreSQL

Table of Contents

1 What is PostgreSQL? Why are we using it and not MySQL, Oracle, Microsoft SQL server ...?

PostgreSQL is an advanced open-source RDBMS, increasingly used by an increasingly large number of high profile projects. We use it in CS 4604 because

a) it is open-source and free ("free as in speech, not as in beer") software;

b) it is not really important which RDBMS you use to learn SQL;

c) among open-source RDBMS, PostgreSQL has some advantages over MySQL (e.g., MySQL does not enforce CHECK constraints).

Extensive documentation is available for PostgreSQL, which you should make your bedtime reading. The documentation is also available with user comments.

2 Can I install PostgreSQL on my own computer?

Yes! You are welcome to do your project on your own computer, as long as I can evaluate the final step of your project. Install version 8.0 or later. PostgreSQL works on Linux and Windows. You can download versions for these operating systems. For Mac OS X, please use the fink system.

3 Using the CS 4604 PostgreSQL server

Follow the following steps to use the CS 4604 PostgreSQL server. Some of the steps below apply only the first time you log in.

  1. ssh to rlogin.cs.vt.edu using your SLO account.
  2. Access the SQL server by typing

    psql -h postdb.cs.vt.edu -U "user name" -d "database name" (the psql shell will prompt you for the password).

    Use the username and password for the PostgreSQL account provided to you. There is no connection between your SLO username/password and the username/password you use to access PostgreSQL. If you provide the correct username and password, you will get a psql prompt where you can type in SQL commands and queries.

  3. If this is the first time you are accessing the PostgreSQL server, change your password by typing the following command at the psql prompt:

    alter user <username> password 'YourNewPassword';

    If you do not find the group database, please me an email and let me know which project group you belong to, the name of the project, and the members of your group. We will give you access to the right database.

  4. If you have any problems, please send email to Haiyan. Be sure to include all the errors you get so that she can help you as quickly as she can.

4 Using PostgreSQL and psql

You will often use the psql command line client to talk to the CS 4604 server, especially for Project assignments 5 and 6. Here are some potentially useful tips.

  1. To copy data from a file into a table or vice-versa, use the \ copy command. Read the documentation for this command, which is about a third of the way down the web page.
  2. You can use temporary tables to stash results of queries. Temporary tables will disappear when you disconnect from the server. Take a look at the documentation for creating tables from queries.
  3. When you create primary keys or unique constraints, PostgreSQL will name the constraints even if you do not. When defining foreign keys, refer to the attributes and not to the names of the constraints.
  4. PostgreSQL does not allow subqueries in CHECK constraints. Nor does it allow the constraint to refer to variables other than columns of the current row. This sad fact is documented (search for the second occurrence of "CHECK (expression)").

Original Author: T. M. Murali <murali AT cs DOT edu>