Logging in to PostgreSQL using psql

psql is a simple command-line client for the PostgreSQL database and should be available on the linux machine at the university.

Assuming your account is tda357_XXX, you connect to the database as follow:

psql -h ate.ita.chalmers.se -U tda357_XXX

Enter you password and you will then get a prompt where you can type sql commands, e.g.:

tda357_XXX=> CREATE TABLE h2g2 ( the_answer INT );
CREATE TABLE
tda357_XXX=> INSERT INTO h2g2 VALUES (42);
INSERT 0 1
tda357_XXX=> SELECT * FROM h2g2;
the_answer
------------
         42
(1 row)



Hint: "\q" quits psql. 

Hint: "\i file.sql" runs every statement in file.sql.

Installing and setting up a database on your own machine

Installing Postgres on your own machine has multiple advantages: 1) You can work from home without VPN connection to chalmers 2) You and your teammate can work independently without disrupting one anothers work.

Step 1 is downloading and installing Postgres. There are multiple ways of doing this for each operating system. We recommend following the procedure on this page. The BigSQL installer seems to work well for Windows, the Postgres.app for MacOS. Or you can use whatever package manager your OS provides. When installing you need to choose a root password that you remember. We recommend choosing English language settings if asked, so you get error messages that give useful Google-hits.

After installing Postgres open a terminal window (run "cmd.exe" on Windows, open Terminal on mac). Try running the command "psql -U postgres" (may require superuser privileges). If it starts psql, good! If not, try one of:

  • Add the psql binary to your PATH variable and restart the terminal
  • Navigate your terminal to the install location of psql "cd [path-to-psql-binary]" (usually the bin subdirectory of the installation directory)
  • On Windows: Run "[path-to-psql-binary]\psql.exe" instead of just "psql" (including the quotes if the path contains spaces).
  • Ask someone who got it working for help.

After you get psql working, run the following commands inside psql to set up an environment similar to the one on the Chalmers machines (where [username] is replaced by you TDA357-xyz username and [password] with the password for that account):

  • CREATE DATABASE local;
  • CREATE USER [username] WITH PASSWORD '[password]';
  • GRANT ALL PRIVILEGES ON DATABASE local TO [username];
    •

Now each time you want to work with Postgres, run "psql -U [username] local", and follow the workflow described on this page.

Basic workflow using psql

Ensure you have a terminal window where you can run psql to connect to a database (on your own machine or a Chalmers machine).

Create a text file called "script.sql" (or any other name) in the same directory that you are running psql from (the working directory of your terminal).

Add the line "DROP OWNED BY [username] CASCADE;" to the start of the file - this will delete everything every time the file is executed. Add some test SQL code e.g. create a table and run a query (remember semicolons!).

Now cycle through this simple loop until you are done with the assignment :).

  • Make a small change to script.sql file (preferably with an editor that provides syntax highlighting for SQL files)
  • run "\i script.sql" in psql, and read all the error messages, query outputs etc.
  • Repeat

    Hint: By running "psql -U [username] -v ON_ERROR_STOP=1" (adding the database name at the end if running on your own machine, or -h ... if connecting to Chalmers server) execution of a file will stop if there is an error in any statement (useful when running lots of inserts for instance).

    Hint: For solving the lab assignments, always work incrementally: Start with a view/trigger that does something (but not everything) and works, then extend it gradually until it works they way you intended. There is a risk that you will have to backtrack and change stuff you already made, but that risk is basically 100% if you write the whole assignment in one go (and there is the additional risk that you never even get started if you try to solve the whole problem at once).

    • Changing Your Password

    You can change your PostgreSQL password by executing the command:

    ALTER USER <username> WITH PASSWORD '<newpassword>';

    Where <username> is your PostgreSQL username, and <newpassword> is the password you would like to use in the future. This command, like all other SQL commands, should be terminated with a semicolon.

    Getting Information About Your Schema

    If you need to inspect your database, you can use psql commands. For instance, \d will list all tables and views, or \dft will list all trigger functions. Use \? to get a list of all the available commands and their description.

    Deleting Everything

    The following line can be run to delete all of your tables, views and triggers:

    DROP OWNED BY <username> CASCADE;

    This command may give some warning about failure to revoke privileges, but those can be ignored ... probably.