PostgreSQL Use

Tom Kelliher, CS 318

Jan. 30, 2002

One of the homework problems requires that you execute some SQL commands. Each of you now has a PostgreSQL account. Your PostgreSQL username is your phoenix username and your PostgreSQL password is your Goucher ID number (PostgreSQL passwords are not changeable). Here's a shell session I captured using the Unix script command. It demonstrates basic usage of psql, which is a PostgreSQL command line client:

# Initially creating a database, connecting to it within psql, and
# creating a table within the database.  You always connect to the
# template1 database the first time you connect.  The syntax of psql is:
#
#    psql <database name> <database username>
#
# See the man page for psql for more information.

phoenix:~
* psql template1 kelliher
Password:
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

template1=> -- This is an SQL comment.
template1=> --
template1=> -- Notice the nice help meta-commands above.
template1=> -- 
template1=> create database kelliher;
CREATE DATABASE
template1=> -- Note that SQL statements are terminated with
template1=> -- a semicolon.  If you forget, use \g.
template1=> --
template1=> -- Connect to the database I just created.
template1=> --
template1=> \c kelliher
You are now connected to database kelliher.
kelliher=> create table example (
kelliher(>    id     integer,
kelliher(>    name   char(50) );
CREATE
kelliher=> --
kelliher=> -- About to exit
kelliher=> \q

# Once you've created your database, connect to it directly when you start
# psql:

phoenix:~
* psql kelliher kelliher
Password: 
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

kelliher=> \q
phoenix:~
*

1. The first time you run psql, connect to the template1 database. Once you've created your own database, connect to it, instead.

2. You'll need to create your own database. For the name, use your username, as I did in the example above.

   Be warned: if I find databases on the system of which I can't determine the ownership, I will drop them.

3. Once you've created your database, you connect to it and can then create tables, constraints, etc.