Getting started with PostgreSQL

Official PostgreSQL docs

PostgreSQL is an open-source database system which you will be using as the backend of your calendar system.  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.  

1. The command line client insists that you choose a database.  Since you have not created a database, the first time you run psql, you will use a default database called template1.  After logging into cubist, and setting your path, run: psql template1.  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 uw email name as a login name.  Use psql -U [username] template1 if your uw name is different than your unix login name.) Then you should see a prompt that looks like: template1==>
2. 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. 
3. First let's create a sample database.  First use: CREATE DATABASE [sample_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.
4. Next we will execute a set of SQL commands contained in this file.  After glancing at it, you should be able to execute it by using at the prompt: \i ~gerome/beersbarsdrinkers.sql.  You can use \i to execute any file which contains SQL.  Please make sure you have created a new database before running this otherwise you will create new tables in template1 which is a bad thing.  You should see a prompt like testdb_yourname==>
5. 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 | gerome
beers | table | gerome
drinkers | table | gerome
frequents | table | gerome
likes | table | gerome
sells | table | gerome
test_id_seq | sequence | pgsql
(7 rows)

 

6. To take a closer look at the data, try executing the following at the command line: SELECT * FROM Beers ORDERBY price; Again, don't forget the semicolon!
7. 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.
8. To quit the psql client use \q.
9. 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.