Connecting to MATLAB

The PostgreSQL server that we will be using for the remainder of the semester is located on port 5433 on the UofT MATLAB server.

Please establish a connection to MATLAB using your favourite SSH tool.

On Windows, you can use Windows Powershell, Command Prompt, or download and install PuTTY.

On Mac or Linux, you can use Terminal.

Your username and password is the same as the one that you log into ACORN and Quercus with.

Execute the command ssh [username]@mathlab.utsc.utoronto.ca. Replace [username] with your UTORid.

You will be prompted for your password. Enter your password. The password field is invisible to your terminal.

Connecting to PostgreSQL

Connect to the local PostgreSQL server by executing the command psql while on MATLAB.

You will be prompted for a password. By default, everyone’s password is password.

xucharle@mathlab:~$ psql
Password for user xucharle:
psql (12.7 (Ubuntu 12.7-0ubuntu0.20.04.1))
Type "help" for help.

xucharle=>

Getting Started with PostgreSQL

Databases

Every student works inside their own individual database. You can list all databases inside the server.

xucharle=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 xucharle  | xucharle | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =T/xucharle          +
           |          |          |             |             | xucharle=CTc/xucharle
(1 row)

If you are brought to a subscreen, you can navigate the subscreen using your arrow keys. Alternatively, you can exit the subscreen by pressing Q.

You are able to change databases at your own convenience.

xucharle=> \c xucharle
You are now connected to database "xucharle" as user "xucharle".

You can check which database you are connected to.

xucharle=> SELECT current_database();
 current_database
------------------
 xucharle
(1 row)

Schemas

Every database has a set of schemas.

xucharle=> \dn
  List of schemas
  Name  |  Owner
--------+----------
 public | postgres
(1 row)

By default, you are on the public schema. This is stored as a local variable called search_path.

xucharle=> SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

You can change your schema by modifying this variable.

xucharle=> SET search_path TO public;
SET

In case an error occurs, you can reset the variable to its default value.

xucharle=> SET search_path TO DEFAULT;
SET

Tables

Every schema has a set of tables.

xucharle=> \dt
            List of relations
   Schema   |   Name   | Type  |  Owner
------------+----------+-------+----------
 university | course   | table | xucharle
 university | offering | table | xucharle
 university | student  | table | xucharle
 university | took     | table | xucharle
(4 rows)

You can list the attributes of a specific table.

xucharle=> \d course
                    Table "university.course"
 Column  |         Type          | Collation | Nullable | Default
---------+-----------------------+-----------+----------+---------
 cnum    | integer               |           | not null |
 name    | character varying(40) |           | not null |
 dept    | department            |           | not null |
 breadth | boolean               |           |          |
Indexes:
    "course_pkey" PRIMARY KEY, btree (cnum, dept)
Referenced by:
    TABLE "offering" CONSTRAINT "offering_cnum_dept_fkey" FOREIGN KEY (cnum, dept) REFERENCES course(cnum, dept)

SQL Files

You are able to execute entire SQL files through the main command line shell.

xucharle@mathlab:~/cscc43f20_space$ psql -f coursesDDL.txt
Password for user xucharle:
psql:coursesDDL.txt:1: NOTICE:  schema "university" does not exist, skipping
DROP SCHEMA
CREATE SCHEMA
...

You can also execute the same program while inside PostgreSQL’s interactive shell.

xucharle=> \i coursesDDL.txt
psql:coursesDDL.txt:1: NOTICE:  drop cascades to 8 other objects
DETAIL:  drop cascades to type university.grade
drop cascades to type university.cgpa
...

Executing Queries

With the correct database and schema selected, we are able to query the tables.

xucharle=> SELECT * FROM course;
 cnum | name | dept | breadth
------+------+------+---------
(0 rows)

We are able to query tables across different schemas by adding a prefix to the table name.

However, we will not be able to query across different databases.

xucharle=> SHOW search_path;
   search_path
-----------------
 "$user", public
(1 row)

xucharle=> SELECT * FROM course;
ERROR:  relation "course" does not exist
LINE 1: SELECT * FROM course;
                      ^

xucharle=> SELECT * FROM university.course;
 cnum | name | dept | breadth
------+------+------+---------
(0 rows)

Quit PostgreSQL

Exit the the PostgreSQL session and return to the main command line shell.

xucharle=> \q
xucharle@mathlab:~$

Tutorial

For this week’s tutorial, please download the following files and load them into your own individual database.

Download

$ wget https://ta.kthisiscvpv.com/cscc43/week4/coursesDDL.txt
$ wget https://ta.kthisiscvpv.com/cscc43/week4/coursesData.txt

Upload to PostgreSQL

$ psql -f coursesDDL.txt -f coursesData.txt

Connect to PostgreSQL

$ psql

Reference the University Schema

=> SET search_path TO university;