Getting started with PostgreSQL
Official PostgreSQL docs
PostgreSQL is an open-source database system which you will be using as the
backend of your project. The server is the postmaster process
running on cubist. Your system will post queries and updates to PostgreSQL
through perl, php, c/c++ programs. But it will be convenient for you to
manage your database directly. Also, any interface from another
programming language simply submits SQL strings to the PostgreSQL server, so you
may want to debug your SQL separately. For these purposes we use a
command-line client called psql, which will allow you to run queries,
create tables, change permissions, and so forth.
- The command line client insists that you choose a database. The first
time you run psql, you will use a default database. After logging
into cubist, and setting your path, run: psql [database name],
which is the same as your CSE email name. Then You will be prompted for
a password which you should have received.
Your username for PostgreSQL is inherited from your unix login, by
default. (Your actual PostgreSQL account uses you CSE email name as a
login name. Use psql -U [username] [database name] if you want to logon
to your members' database.) Then you should see a prompt that looks like: [database name]->
- From this prompt you can issue a range of commands preceeded by \.
To get a list of commands, type \? and return. In addition, from this
prompt you can input raw SQL and execute it.
- After you login to your database, you can create more databases if you want;
however, this is just for your own excitment. Note: you don't
need to create new database in order to work on your project. Use: CREATE DATABASE
[new_database_name]; at the prompt, and hit return. We
all share the same namespace for databases, so think of an original
name. Don't forget the
semicolon! All text you enter at the prompt is buffered until a semi-colon
is seen. Use \p to see the current buffer.
- Next we will execute a set of SQL commands contained in this
file, please copy this into your cubist directory where you logon to postgreSQL
. After glancing at it, you should be able to execute it by
using at the prompt: \i beersbarsdrinkers.sql and hit return. You can
use \i to execute any file which contains SQL. Please make sure
you are in your own database before running this.
- To get some sense of the tables you have created type \d.
This provides a summary of the tables in the current database. You
should see this, with you as owner of each table.
List of relations
Name | Type | Owner
-------------+----------+--------
bars | table | manchun
beers | table | manchun
drinkers | table | manchun
frequents | table | manchun
likes | table | manchun
sells | table | manchun
(6 rows)
- To take a closer look at the data, try executing the following at the
command line: SELECT * FROM Beers ORDER BY price; Again, don't forget
the semicolon!
- PostgreSQL uses permissions to control access. To see a summary of
permissions for the current database, use \z. Like most other things
permissions are modified through SQL commands. See
this page for a clear explanation. When you create your calendar
database, you will likely need to allow your group members read and write
access.
- To quit the psql client use \q.
- You can now follow a similar routine to create the calendar database and
add sample data. When creating a database it is a good idea to
structure an SQL source file and execute it, rather than entering random
lines of SQL into the psql client, because you will be able to recover and
modify them that way. The the SQL
reference for help defining the tables of your new database. You
can also start running the sample code that accesses the testdb you have
just created.