====== Technical Reference ======
===== Command Line Access =====
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=#