Technical Reference

Our postgres server can be accessed from the command line via psql (the PostgreSQL interactive terminal):

$ psql -h postgres -U username -d dbname

where username is name of your CS account and dbname is the name of your database. You will be prompted to enter your password. You should enter your Linux CS account password, not your Vassar password. For more information about the psql command see: https://www.postgresql.org/docs/11/app-psql.html

For example:

mlsmith@mote:~$ psql -h postgres -U mlsmith -d university
Password for user mlsmith: 
psql (10.10 (Ubuntu 10.10-0ubuntu0.18.04.1))
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

university=# 

From within psql there, a few useful commands are:

\dt          -- to display metadata for the tables in the database
\d tablename -- to display metadata for a particular table in the database
\?           -- to display help on psql command line options
\h           -- to display help on SQL commands 
\q           -- to quit out of psql and return to the linux prompt ($)

For example:

university=# \dt
           List of relations
 Schema |    Name    | Type  |  Owner  
--------+------------+-------+---------
 public | advisor    | table | mlsmith
 public | classroom  | table | mlsmith
 public | course     | table | mlsmith
 public | department | table | mlsmith
 public | instructor | table | mlsmith
 public | prereq     | table | mlsmith
 public | section    | table | mlsmith
 public | student    | table | mlsmith
 public | takes      | table | mlsmith
 public | teaches    | table | mlsmith
 public | time_slot  | table | mlsmith
(11 rows)

university=# \d instructor
                     Table "public.instructor"
  Column   |         Type          | Collation | Nullable | Default 
-----------+-----------------------+-----------+----------+---------
 id        | character varying(5)  |           | not null | 
 name      | character varying(20) |           | not null | 
 dept_name | character varying(20) |           |          | 
 salary    | numeric(8,2)          |           |          | 
Indexes:
    "instructor_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "instructor_salary_check" CHECK (salary > 29000::numeric)
Foreign-key constraints:
    "instructor_dept_name_fkey" FOREIGN KEY (dept_name) REFERENCES department(dept_name) ON DELETE SET NULL
Referenced by:
    TABLE "advisor" CONSTRAINT "advisor_i_id_fkey" FOREIGN KEY (i_id) REFERENCES instructor(id) ON DELETE SET NULL
    TABLE "teaches" CONSTRAINT "teaches_id_fkey" FOREIGN KEY (id) REFERENCES instructor(id) ON DELETE CASCADE

university=# 

The other useful–and cool–thing you can do from inside psql is issue SQL commands. For example:

university=# select * from instructor;
  id   |    name    | dept_name  |  salary  
-------+------------+------------+----------
 10101 | Srinivasan | Comp. Sci. | 65000.00
 12121 | Wu         | Finance    | 90000.00
 15151 | Mozart     | Music      | 40000.00
 22222 | Einstein   | Physics    | 95000.00
 32343 | El Said    | History    | 60000.00
 33456 | Gold       | Physics    | 87000.00
 45565 | Katz       | Comp. Sci. | 75000.00
 58583 | Califieri  | History    | 62000.00
 76543 | Singh      | Finance    | 80000.00
 76766 | Crick      | Biology    | 72000.00
 83821 | Brandt     | Comp. Sci. | 92000.00
 98345 | Kim        | Elec. Eng. | 80000.00
(12 rows)

university=#