Having an efficient workflow for postgres is essential to completing the lab in a timely manner.

Usually, that means using a text editor to edit a number of .sql files (the ones requested in the assignment) and running the psql command line application with a script that:

1. Deletes everything in the database

2. Runs all your files to recreate the database and print any errors that occur.

To get this running you will need to install postgres, and run the psql tool in a terminal window. There are various GUI tools for editing Postgres databases, but more often than not these are not very helpful.

TLDR:

Run this in a terminal window to start psql and connect to the database "portal" after creating it:

psql -v ON_ERROR_STOP=1 -U postgres portal

First time running psql

This assumes you have successfully installed the latest version of PostgreSQL (see instructions further down), opened a terminal window, and navigated the terminal to a directory where you have created .sql files (tables.sql, views.sql and inserts.sql) and downloaded the runtests.sql file from the Assignment text.

Run this command:

psql -U postgres

If the terminal says psql is not a valid command, you have not added the bin directory of postgres to your path environment variable. Either do that or locate the psql binary (e.g. psql.exe on Windows) and run it with its complete path. In windows command prompt (cmd.exe) that might look something like this (note the quotes):

"C:\Program Files\PostgreSQL\13\bin\psql.exe" -U postgres

In MS Powershell, something like this should work:

& 'C:\Program Files\PostgreSQL\13\bin\psql.exe' -U postgres

These assume you are using version 13 of postgres and have installed it to the default directory, adjust as needed (e.g. 12 instead of 13 if using version 12)

If it works you are prompted to enter the password you used when installing. When at the psql prompt (postgres=#) input this command:

CREATE DATABASE portal;

It should echo "CREATE DATABASE". If it does, type \quit to exit the application and restart it with this command in your operating system terminal:

psql -v ON_ERROR_STOP=1 -U postgres portal

This will connect you to the database portal, using the user postgres.

Workflow

Now run the command \i runtests.sql at the psql prompt to execute the (so far empty?) files. This will give errors since the views do not yet exist, so edit runtests.sql, commenting out the SELECT clauses (-- for single line comments) and all the inserts in inserts.sql, then gradually return them as you progress in the lab.

Add a CREATE TABLE statement to tables.txt and check that it works by re-running \i runtests.sql (just press up arrow and enter in the terminal).

The workflow from here on out will be making edits in whatever text editor you are using (one with SQL syntax highlighting is VERY useful), re-running the \i command, and fixing any error you get etc.

Installing PostgreSQL on Windows

Follow the link to the EnterpriseDB installer from this page: https://www.postgresql.org/download/windows/

When prompted for a password, use "postgres" or choose a simple one that you do not mind storing in plain text, also using the same password for both members of the group is beneficial. The password does not need to be strong.

After installing, you will need to add the \bin subdirectory of the installation folder to your windows path.

Installing PostgreSQL on MacOS

Follow the instructions on https://postgresapp.com/

When prompted for a password, use "postgres" or choose a simple one that you do not mind storing in plain text, also using the same password for both members of the group is beneficial. The password does not need to be strong.

Installing on other operating systems

Follow instructions on https://www.postgresql.org/download/.