Flailing around trying to get a handle on psql here at UW DRCSCS CSCF

Preliminaries to enable your connection and use

To be filled in later.

Connecting and looking around

ubuntu1404-202% psql -h postgres.odyssey.uwaterloo.ca -d odyssey
psql (9.3.24, server 9.5.15)
WARNING: psql major version 9.3, server major version 9.5.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

odyssey=> select version();
odyssey=> SELECT current_date;
    date    
------------
 2019-05-14
(1 row)

odyssey=> select uw_term_from_time ('May 15, 2019');
ERROR:  function uw_term_from_time(unknown) does not exist
LINE 1: select uw_term_from_time ('May 15, 2019');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
odyssey=> set search_path to _instruct, _identity, _quest, public;
SET
odyssey=> select uw_term_from_time ('May 15, 2019');
 uw_term_from_time 
-------------------
              1195
(1 row)

odyssey=> \q
ubuntu1404-202% 

Notes:
- \q to quit/exit

- select is often used to print things not obviously search results.

- uw_term_from_time fails unless search_path has been set as shown.

- There can be psql version problems. Even server 9.5 is not very recent.
- select version(); shows a non-inline result.

Setting the output pager off

Usually you (well, I) want to set the pager off...

odyssey=> \pset pager off
Pager usage is off.
odyssey=> \dt

Getting help

Notes:
- (\? gives Postgres help; \h gives SQL help (potentially a lot of output)

odyssey=> \h
odyssey=> \h SHOW
Command:     SHOW
Description: show the value of a run-time parameter
Syntax:
SHOW name
SHOW ALL

odyssey=> 

odyssey=> \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 odyssey   | postgres | UTF8     | C       | C     | postgres=CTc/postgres+
           |          |          |         |       | =Tc/postgres         +
           |          |          |         |       | _dba=C/postgres
 postgres  | postgres | UTF8     | C       | C     | 
 template0 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C       | C     | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(4 rows)

odyssey=> 

Verifying tables

odyssey-> \dt
odyssey-> \dt uw_unit
           List of relations
 Schema |  Name   | Type  |    Owner    
--------+---------+-------+-------------
 _quest | uw_unit | table | _quest_root
(1 row)

odyssey-> 

odyssey-> \dt std_name
            List of relations
 Schema |   Name   | Type  |    Owner    
--------+----------+-------+-------------
 _quest | std_name | table | _quest_root
(1 row)

odyssey-> \dt std_name_preferred
No matching relations found.
odyssey-> \dt std_name_primary
No matching relations found.
odyssey-> \dt std_plan_history
                List of relations
 Schema |       Name       | Type  |    Owner    
--------+------------------+-------+-------------
 _quest | std_plan_history | table | _quest_root
(1 row)

odyssey-> \dt std_program_history
                 List of relations
 Schema |        Name         | Type  |    Owner    
--------+---------------------+-------+-------------
 _quest | std_program_history | table | _quest_root
(1 row)

odyssey-> \dt std_service_indicator
                  List of relations
 Schema |         Name          | Type  |    Owner    
--------+-----------------------+-------+-------------
 _quest | std_service_indicator | table | _quest_root
(1 row)

odyssey-> \dt std_student
             List of relations
 Schema |    Name     | Type  |    Owner    
--------+-------------+-------+-------------
 _quest | std_student | table | _quest_root
(1 row)

odyssey-> \dt std_subplan_history
                 List of relations
 Schema |        Name         | Type  |    Owner    
--------+---------------------+-------+-------------
 _quest | std_subplan_history | table | _quest_root
(1 row)

odyssey-> \dt std_term_plan
              List of relations
 Schema |     Name      | Type  |    Owner    
--------+---------------+-------+-------------
 _quest | std_term_plan | table | _quest_root
(1 row)

odyssey-> \dt std_term_program
                List of relations
 Schema |       Name       | Type  |    Owner    
--------+------------------+-------+-------------
 _quest | std_term_program | table | _quest_root
(1 row)

odyssey-> \dt std_term_subplan
                List of relations
 Schema |       Name       | Type  |    Owner    
--------+------------------+-------+-------------
 _quest | std_term_subplan | table | _quest_root
(1 row)

odyssey-> 

Revealing the column structure of tables

odyssey=> select column_name from information_schema.columns where table_name = 'std_name' ;
  column_name   
----------------
 uw_id
 name_type_code
 name_as_of
 name_title
 last_name
 first_name
 middle_name
(7 rows)

odyssey=> 

Note the quotation marks (apostrophes) around the table name.

Getting a list of all tables available

Notes:
- If you have not set the pager off, \dt alone probably shows a none-inline result.

odyssey=> \pset pager off
Pager usage is off.
odyssey=> \dt
                              List of relations
  Schema   |                  Name                  | Type  |     Owner      
-----------+----------------------------------------+-------+----------------
 _identity | person_event_type                      | table | _identity_root
 _identity | person_identity                        | table | _identity_root
 _identity | person_identity_log                    | table | _identity_root
 _identity | person_identity_type                   | table | _identity_root
 _identity | person_identity_type_log               | table | _identity_root
 _identity | person_person                          | table | _identity_root
 _identity | person_person_log                      | table | _identity_root
 _identity | update_multiple_report                 | table | _identity_root
 _identity | watiam_duplicate_warning               | table | _identity_root
 _identity | watiam_update_id                       | table | _identity_root
[...]
 _quest    | uw_grading_basis                       | table | _quest_root
 _quest    | uw_group                               | table | _quest_root
 _quest    | uw_group_history                       | table | _quest_root
 _quest    | uw_holiday_type                        | table | _quest_root
 _quest    | uw_location                            | table | _quest_root
 _quest    | uw_location_history                    | table | _quest_root
 _quest    | uw_phone_type                          | table | _quest_root
 _quest    | uw_program_action                      | table | _quest_root
 _quest    | uw_program_reason                      | table | _quest_root
 _quest    | uw_program_status                      | table | _quest_root
 _quest    | uw_room                                | table | _quest_root
 _quest    | uw_study_agreement                     | table | _quest_root
 _quest    | uw_term                                | table | _quest_root
 _quest    | uw_test_component                      | table | _quest_root
 _quest    | uw_test_source                         | table | _quest_root
 _quest    | uw_test_type                           | table | _quest_root
 _quest    | uw_unit                                | table | _quest_root
 _quest    | uw_unit_history                        | table | _quest_root
 _quest    | uw_visible_minority                    | table | _quest_root
(276 rows)

odyssey=> 

Without \pset pager off the output is "paged" to an alternate terminal screen and is not conveniently available.

Defining a (useful) temporary table

odyssey=> create temp table doccies as select * from pps_plan_type where ( program_type_code = 'D' );
SELECT 4
odyssey=> select * from doccies;
 plan_type_code | plan_type_as_of | plan_type_active |     plan_type_description      | pt_coop | pt_joint | pt_cost_recovery | pt_honors | qualification_type_code | program_type_code | degree_level_code | mtcu_level_code 
----------------+-----------------+------------------+--------------------------------+---------+----------+------------------+-----------+-------------------------+-------------------+-------------------+-----------------
 DM             | 1957-01-01      | t                | Doctor of Philosophy Yr 1 of 3 | f       | f        | f                | f         | D                       | D                 | MAST              | M
 DC             | 1957-01-01      | t                | Doctor of Philosophy Co-op     | t       | f        | f                | f         | D                       | D                 | PHD               | D
 DJ             | 1957-01-01      | t                | Doctor of Philosophy Joint     | f       | t        | f                | f         | D                       | D                 | PHD               | D
 D              | 1957-01-01      | t                | Doctor of Philosophy           | f       | f        | f                | f         | D                       | D                 | PHD               | D
(4 rows)

odyssey=> 

odyssey=> select  plan_type_code, plan_code from pps_plan join doccies using (plan_type_code) where unit_code = 'CS';
 plan_type_code | plan_code 
----------------+-----------
 D              | CSQID3
 D              | CSQID
 DM             | CSD3Y1
 D              | CSD3
 D              | CSD
(5 rows)

odyssey=> 

Perhaps using a prefix convention such as t_ might be be a better way to prevent collision with normal names in the schema.

odyssey=> create temp table t_docplans as select * from pps_plan_type where ( program_type_code = 'D' );
SELECT 4
odyssey=> select plan_type_code from t_docplans;
 plan_type_code 
----------------
 DM
 DC
 DJ
 D
(4 rows)

odyssey=> 

-- AdrianPepper - 2019-05-16

Topic revision: r5 - 2019-05-24 - AdrianPepper
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback