DIsplaying Data Types and Sizes of System Tables

[Brett]

Howdy,

Is there a way that I can see the data types and sizes of the columns in a system table? If I try to export one I get an error:

Code: Select all

dbadmin=> select export_objects('', 'sessions');
ERROR 2425:  Cannot export virtual table sessions

[JimKnicely]

Hi,

Probably the easiest way would be to query the vs_columns table. Like this:

Code: Select all

dbadmin=> select relname, attname, typname from vs_columns where nspname = 'v_monitor' and relname = 'sessions';
 relname  |          attname           |    typname    
----------+----------------------------+---------------
 sessions | node_name                  | varchar(128)
 sessions | user_name                  | varchar(128)
 sessions | client_hostname            | varchar(128)
 sessions | client_pid                 | int
 sessions | login_timestamp            | timestamptz
 sessions | session_id                 | varchar(80)
 sessions | client_label               | varchar(256)
 sessions | transaction_start          | timestamptz
 sessions | transaction_id             | int
 sessions | transaction_description    | varchar(8192)
 sessions | statement_start            | timestamptz
 sessions | statement_id               | int
 sessions | last_statement_duration_us | int
 sessions | runtime_priority           | varchar(8192)
 sessions | current_statement          | varchar(8192)
 sessions | last_statement             | varchar(8192)
 sessions | ssl_state                  | varchar(80)
 sessions | authentication_method      | varchar(80)
 sessions | client_type                | varchar(128)
 sessions | client_version             | varchar(128)
 sessions | client_os                  | varchar(128)
 sessions | epoch                      | int
(22 rows)

Or you could create your own version of the table using the definition from the system table. Like this:

Code: Select all

dbadmin=> create table public.my_sessions as select * from v_monitor.sessions where 1 = 2;
CREATE TABLE
dbadmin=> \d public.my_sessions;
                                                 List of Fields by Tables
 Schema |    Table    |           Column           |     Type      | Size | Default | Not Null | Primary Key | Foreign Key 
--------+-------------+----------------------------+---------------+------+---------+----------+-------------+-------------
 public | my_sessions | node_name                  | varchar(128)  |  128 |         | f        | f           | 
 public | my_sessions | user_name                  | varchar(128)  |  128 |         | f        | f           | 
 public | my_sessions | client_hostname            | varchar(128)  |  128 |         | f        | f           | 
 public | my_sessions | client_pid                 | int           |    8 |         | f        | f           | 
 public | my_sessions | login_timestamp            | timestamptz   |    8 |         | f        | f           | 
 public | my_sessions | session_id                 | varchar(80)   |   80 |         | f        | f           | 
 public | my_sessions | client_label               | varchar(256)  |  256 |         | f        | f           | 
 public | my_sessions | transaction_start          | timestamptz   |    8 |         | f        | f           | 
 public | my_sessions | transaction_id             | int           |    8 |         | f        | f           | 
 public | my_sessions | transaction_description    | varchar(8192) | 8192 |         | f        | f           | 
 public | my_sessions | statement_start            | timestamptz   |    8 |         | f        | f           | 
 public | my_sessions | statement_id               | int           |    8 |         | f        | f           | 
 public | my_sessions | last_statement_duration_us | int           |    8 |         | f        | f           | 
 public | my_sessions | runtime_priority           | varchar(8192) | 8192 |         | f        | f           | 
 public | my_sessions | current_statement          | varchar(8192) | 8192 |         | f        | f           | 
 public | my_sessions | last_statement             | varchar(8192) | 8192 |         | f        | f           | 
 public | my_sessions | ssl_state                  | varchar(80)   |   80 |         | f        | f           | 
 public | my_sessions | authentication_method      | varchar(80)   |   80 |         | f        | f           | 
 public | my_sessions | client_type                | varchar(128)  |  128 |         | f        | f           | 
 public | my_sessions | client_version             | varchar(128)  |  128 |         | f        | f           | 
 public | my_sessions | client_os                  | varchar(128)  |  128 |         | f        | f           | 
(21 rows)