Getting Started with PostgreSQL
Each student should already have a database set up on grace. The name of the database is the same as your
username. We will be using pgAdmin as a GUI for interacting with
Boot up in Windows.
Under the Start menu, o
> PG Admin III
> pgadmin III.
When pgAdmin starts the screen is divided into three windows. The left side of the screen lists the
databases available. The top right window shows the properties of the object currently selected. The
tom window displays any SQL statements associated with the currently selected object. Grace should
already be listed as a server under the Servers icon at the top of the left window. If grace is not listed and
the number of servers is 0 then click the b
utton above the word Servers(0) with the picture of a plug to
connect to grace. Enter grace.evergreen.edu into the address line. You must enter at least one character
into the description line or the ‘ok’ button will not become active. In the drop down
menu for Maintenance
DB select template1. Then enter your username and password and make sure ‘connect now’ is selected and
hit ok. You should not need to enter anything in the ‘service’ field or change the port number or enter a
value in the SSL field.
Grace should now be available. If grace is available as a server you can expand it
by double clicking on the address.
With grace expanded you should see a list of the databases available. If your database has a red ‘X’ over it
this just means you ha
ve not selected it yet. Select your database and double click. Now you should be
able to expand your database using the standard windows box with the plus and minus signs.
Expand Schemas then public then Tables. Now you should see a list of any tables i
n your database.
There are tutorials both at
and in the help available by clicking the question mark button
Getting Started with SQL ( in PostgreSQ
Open the Query tool by pressing the button with the pencil on it in the tool bar to the right of the button with
trashcan on it. The Query tool is an internal text editor divided into a query pane (upper) and a results pane (lower).
To execute a quer
y, write the query in the upper pane and click the ‘play’ arrow button. You can save your queries
s or by going to File
Comments can be placed between /* */ or on a line preceded by
Some commonly used basic types:
MONEY, VARCHAR(n), CHAR(N), DATE
Creating a table:
You can create a new table by
specifying the table name, along with all column names and their types:
CREATE TABLE club_member
( id INT PRIMARY KEY, name VARCHAR(30) NOT NULL, mailing_address VARCHAR(4
Drop a table:
DROP TABLE table_name;
Basic queries, inserts and updates:
SELECT * FROM club_member ;
returns all data held in the club_member table.
SELECT * FROM club_member WHERE id = 100;
returns the entire record where id is 100
iling_address FROM club_member WHERE name= ‘John Smith’;
returns John Smith’s mailing
INSERT INTO club_member ( id, name, mailing_address ) values ( 1, ‘Lucy Lawless’, ‘1234 Overthere’ );
UPDATE club_member SET name = ‘Zena’ WHERE name = ‘Lucy
DELETE FROM club_member WHERE name = ‘Joe Smith’;