Using PostgreSQL (CS 4604, Spring 2013)

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 for your leisure 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.4 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. Note: Do NOT wait till the assignment due dates to test it out!

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

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

    Use your SLO username as the username. Email Qianzhou for the default password (also posted on Piazza now). 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';

  4. The next step is to see which databases you have access to. At the psql prompt, type \d to show a list of databases that you have access to. One of these two databases is for the project group. All the members of your project have the right to access to this database. This database is named after your project title or group title. The name of the other is the same as your username; only you can access this database.

    If you do not find the group database, please send Qianzhou email to let him know which project group you belong to, the name of the project, and the members of your group. He will give you access to the right database.

  5. If you have any problems, please send email to Qianzhou (with a cc to me). Be sure to include all the errors you get so that we can help you as quickly as we 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 2 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 third occurrence of "CHECK (expression)").
  5. Make sure your database size does not exceed 5Gb. You can use the following commands:
    select pg_size_pretty(pg_relation_size('table_name'));
    select pg_size_pretty(pg_database_size('database_name'));
Last modified: Thur Feb 7 8:59pm EDT 2013