TWiki> CF Web>AdrianPepperPsqlNotes (2019-05-29, AdrianPepper) EditAttach

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

One can use artbitrary unlikely names to avoid collision. And also, in fact, if you use the same name as an existing table, it simple prevents access to the real table during your session. That might actually be useful for some testing. But, in general, perhaps, using a prefix convention such as t_ might be be a good 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=> 


odyssey=> create temp table t_masterplans as select * from pps_plan_type where ( program_type_code = 'M' );
SELECT 52
odyssey=>

odyssey=> select  plan_type_code, plan_code from pps_plan join t_masterplans using (plan_type_code) where unit_code = 'CS';
 plan_type_code | plan_code
----------------+------------
 M25            | HLTHINMC
 M20            | HLTHINM
 M3             | CSQIM
 M18            | CSQFINM
 M              | CSM2
 M3             | CSCOURSEM
 M3             | CSM
 M3C            | CSMC
 M3R            | CSSEMR
 M3C            | CSCOURSEMC
 M3             | CSSTATCMPM
(11 rows)

odyssey=>


odyssey=> select distinct uw_id, userid, plan_code from std_student join std_term_plan using (uw_id) join pps_plan using (plan_code) natural join t_masterplans where term_id = 1195 and unit_code = 'CS' limit 10;
  uw_id   |  userid  | plan_code
----------+----------+------------
 NNNNNNNN | y69bai   | CSCOURSEM
 NNNNNNNN | h2gidwan | CSCOURSEMC
 NNNNNNNN | ihnicaci | CSCOURSEMC
 NNNNNNNN | a4balakr | CSM
 NNNNNNNN | cz3liu   | CSM
 NNNNNNNN | ndezoysa | CSM
 NNNNNNNN | z2masud  | CSM
 NNNNNNNN | ka2weath | CSCOURSEM
 NNNNNNNN | rnadri   | CSM
 NNNNNNNN | t3tiwari | CSCOURSEMC
(10 rows)

odyssey=>


odyssey=> select distinct plan_code from std_student join std_term_plan using (uw_id) join pps_plan using (plan_code) natural join t_masterplans where term_id = 1195 and unit_code = 'CS';
 plan_code
------------
 CSCOURSEMC
 CSM
 CSMC
 CSQIM
 CSCOURSEM
(5 rows)

odyssey=>


odyssey=> select distinct plan_code from std_student join std_term_plan using (uw_id) join pps_plan using (plan_code) natural join t_masterplans where unit_code = 'CS';
 plan_code
------------
 CSQIM
 CSCOURSEMC
 CSM
 HLTHINM
 CSCOURSEM
 HLTHINMC
 CSMC
 CSSEMR
(8 rows)

odyssey=>

-- AdrianPepper - 2019-05-16

Edit | Attach | Watch | Print version | History: r7 < r6 < r5 < r4 < r3 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r7 - 2019-05-29 - AdrianPepper
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2025 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback