For homeworks 3 and 5 this quarter in CSE 344, we will use the PostgreSQL database management system. This page will tell you how to set up and use Postgres on the Windows computers in the Allen Center basement labs. Where possible, we will note differences when setting up Postgres on your own computer; however we have inevitably forgotten something, and would appreciate corrections and additions.
If you're setting up Postgres on your own computer, you can download and install it from here; the basement labs run Postgres version 8.4. The installer will ask you to create a password for the "database superuser and service account." Remember this password; you'll need it later.
To get a command line that is set up to run psql (the Postgres
command-line client) on the Lab machines,
download and run the Postgres shell launcher script,
hw3shell.cmd.
This script sets the command
search PATH variable to include the Postgres commands.
To test that hw3shell.cmd has set up the tools paths correctly,
enter the command psql -?. You should see a usage message like the following:
psql is the PostgreSQL interactive terminal. Usage: psql [OPTIONS]... [DBNAME [USERNAME]] [[Continues...]]
SQLite stores a single database in a single file, but Postgres stores a set of databases in a whole data folder, which you must create before creating any databases. On your own computer, Postgres already has a data folder, but in the lab, you will need to create one by doing the following:
Z:\ , but access to that network drive may be slow. For faster access,
you can put it on a flash drive, or on the local disk (C:) of the computer;
however, any data you put on the local disk is lost when you log out.initdb command to create the data files:
> initdb -D Z:\pgsql_dataIf installed on your own computer, Postgres runs automatically on system startup,
but not in the lab. To manually start a Postgres server, run the pg_ctl start command by choosing
Start→Run, giving the name of the data folder you created earlier:
> "C:\Program Files\PostgreSQL\8.4\bin\pg_ctl.exe" start -D Z:\pgsql_data
Remember: don't type this into the Postgres shell you got from hw3shell.cmd, type it into the Start→Run menu, or into a separate copy of the Postgres shell.
This will pop up a shell window that displays server starting
among other messages. When done, you can stop PostgreSQL either by hitting
Ctrl-C in the shell window, or by using the pg_ctl stop command:
> "C:\Program Files\PostgreSQL\8.4\bin\pg_ctl.exe" stop -D Z:\pgsql_data
On Windows Vista and 7, you can also type these commands into the Start menu's search box, instead of the Start→Run box.
Unlike SQLite, you need to explicitly create a Postgres database before using it.
To create a database named my_database, run the createdb command from a
Postgres shell that you got from hw3shell.cmd:
> createdb my_database
Make sure to spell the database name using lowercase letters only, as createdb is
case sensitive, and interacts badly with (non-case-sensitive) SQL code.
If you installed postgres yourself, you will need to specify that you are running
as the database superuser postgres:
> createdb -U postgres my_database
The password for this account is the "database superuser and service password" you set earlier.
To run SQL queries on SQLite, you use the sqlite3 program.
In Postgres, you use the
psql utility.
Once the Postgres server is running, you can run psql by opening a Postgres shell and typing
> psql my_database
where my_database is the name of the database you want to use. (For self-installs,
you'll also need to specify -U postgres as with createdb.)
When psql opens, you will see a message like this:
psql (8.4.2) Type "help" for help. alice=#
The line alice=# is the prompt for SQL statements which are sent to the database server,
or non-SQL commands interpreted by psql. Here, "alice" is the name of the database.
To exit psql, type \q and press Enter:
alice=# \q
C:\>
Note that there is no semicolon after \q;
this is required because \q and other backslash-quoted commands are not SQL
and are interpreted by psql, rather than the Postgres server. In this
respect they are like the dot-quoted commands of sqlite3.
Type in a SQL statement to run it. SQL statements can be split across multiple lines;
to send the SQL statement to the server, end the statement with a semicolon and press Enter.
Depending on the command, psql will either respond with a confirmation message:
alice=# DELETE FROM hw1_data alice-# WHERE name='name'; DELETE 1
or display the results of the query in a table:
alice=# SELECT * FROM hw1_data;
name | discount | month | price
--------+----------+-------+-------
bar1 | 15% | apr | 19
bar8 | 15% | apr | 19
gizmo3 | 15% | apr | 19
gizmo7 | 15% | apr | 19
mouse1 | 15% | apr | 19
bar1 | 15% | aug | 19
bar8 | 15% | aug | 19
gizmo3 | 15% | aug | 19
gizmo7 | 15% | aug | 19
mouse1 | 15% | aug | 19
bar1 | 33% | dec | 19
bar8 | 33% | dec | 19
gizmo3 | 33% | dec | 19
[[Continues...]]
(426 rows)
If the result table is too large to fit in the shell window, it will be shown one window-ful at a time; press Enter to go on to the next window, until the end.
If you make a mistake while typing in a query, you can use the up-arrow and down-arrow keys on the keyboard to move between previously entered lines, which you can then edit and resubmit.
To run SQL code from an external file, use the \i psql command:
alice=# \i 'query.sql'
Note that psql follows Postgres in allowing backslash escape sequences in character strings. This means that
an absolute pathname like D:\subdir\query.sql must be written either by
doubling the backslashes, as in 'D:\\subdir\\query.sql', or turning them into forward slashes,
as in 'D:/subdir/query.sql'.
Alternativeley, you can run psql with the query file directly from the shell:
> psql -f "query.sql" my_database
You can send the output of queries to a file instead of
(not in addition to) your console with the \o psql command:
alice=# \o 'query_output.txt'
Note that the SQL code of queries will not be saved to the file. To stop saving query output and send it to your console again, use the same command, but omit the filename:
alice=# \o
You can import data from a file on the client computer into an existing database table using
the \copy psql command:
alice=# \copy hw1_data from 'hw1_data.txt'
For the first homework, there is an SQL script that imports the data from
the data file into a new table hw1_data. This script uses the
\copy command internally.