Vertica system tables

• 1: V_CATALOG schema
• 1.1: ACCESS_POLICY
• 1.2: ALL_TABLES
• 1.3: AUDIT_MANAGING_USERS_PRIVILEGES
• 1.4: CA_BUNDLES
• 1.5: CATALOG_SUBSCRIPTION_CHANGES
• 1.6: CATALOG_SYNC_STATE
• 1.7: CATALOG_TRUNCATION_STATUS
• 1.8: CERTIFICATES
• 1.9: CLIENT_AUTH
• 1.10: CLIENT_AUTH_PARAMS
• 1.11: CLUSTER_LAYOUT
• 1.12: COLUMNS
• 1.13: COMMENTS
• 1.14: COMPLEX_TYPES
• 1.15: CONSTRAINT_COLUMNS
• 1.16: CRYPTOGRAPHIC_KEYS
• 1.17: DATABASES
• 1.18: DIRECTED_QUERIES
• 1.19: DUAL
• 1.20: ELASTIC_CLUSTER
• 1.21: EPOCHS
• 1.22: FAULT_GROUPS
• 1.23: FOREIGN_KEYS
• 1.24: GRANTS
• 1.25: HCATALOG_COLUMNS
• 1.26: HCATALOG_SCHEMATA
• 1.27: HCATALOG_TABLE_LIST
• 1.28: HCATALOG_TABLES
• 1.29: ICEBERG_COLUMNS
• 1.30: INHERITED_PRIVILEGES
• 1.31: INHERITING_OBJECTS
• 1.32: KEYWORDS
• 1.33: LARGE_CLUSTER_CONFIGURATION_STATUS
• 1.34: LICENSE_AUDITS
• 1.35: LICENSES
• 1.36: LOAD_BALANCE_GROUPS
• 1.37: LOG_PARAMS
• 1.38: LOG_QUERIES
• 1.39: LOG_TABLES
• 1.40: MATERIALIZE_FLEXTABLE_COLUMNS_RESULTS
• 1.41: MODELS
• 1.42: NAMESPACES
• 1.43: NETWORK_ADDRESSES
• 1.44: NODE_SUBSCRIPTION_CHANGE_PHASES
• 1.45: NODE_SUBSCRIPTIONS
• 1.46: NODES
• 1.47: ODBC_COLUMNS
• 1.48: PASSWORD_AUDITOR
• 1.49: PASSWORDS
• 1.50: PRIMARY_KEYS
• 1.51: PROFILE_PARAMETERS
• 1.52: PROFILES
• 1.53: PROJECTION_CHECKPOINT_EPOCHS
• 1.54: PROJECTION_COLUMNS
• 1.55: PROJECTION_DELETE_CONCERNS
• 1.56: PROJECTIONS
• 1.57: REGISTERED_MODELS
• 1.58: RESOURCE_POOL_DEFAULTS
• 1.59: RESOURCE_POOLS
• 1.60: ROLES
• 1.61: ROUTING_RULES
• 1.62: SCHEDULER_TIME_TABLE
• 1.63: SCHEMATA
• 1.64: SEQUENCES
• 1.65: SESSION_SUBSCRIPTIONS
• 1.66: SHARDS
• 1.67: STORAGE_LOCATIONS
• 1.68: STORED_PROC_TRIGGERS
• 1.69: SUBCLUSTER_RESOURCE_POOL_OVERRIDES
• 1.70: SUBCLUSTERS
• 1.71: SYSTEM_COLUMNS
• 1.72: SYSTEM_TABLES
• 1.73: TABLE_CONSTRAINTS
• 1.74: TABLES
• 1.75: TEXT_INDICES
• 1.76: TYPES
• 1.77: USER_AUDITS
• 1.78: USER_CLIENT_AUTH
• 1.79: USER_CONFIGURATION_PARAMETERS
• 1.80: USER_FUNCTION_PARAMETERS
• 1.81: USER_FUNCTIONS
• 1.82: USER_PROCEDURES
• 1.83: USER_SCHEDULES
• 1.84: USER_TRANSFORMS
• 1.85: USERS
• 1.86: VIEW_COLUMNS
• 1.87: VIEW_TABLES
• 1.88: VIEWS
• 1.89: WORKLOAD_ROUTING_RULES
• 2: V_MONITOR schema
• 2.1: ACTIVE_EVENTS
• 2.2: ALLOCATOR_USAGE
• 2.3: COLUMN_STORAGE
• 2.4: COMMUNAL_CLEANUP_RECORDS
• 2.5: COMMUNAL_TRUNCATION_STATUS
• 2.6: CONFIGURATION_CHANGES
• 2.7: CONFIGURATION_PARAMETERS
• 2.8: CPU_USAGE
• 2.9: CRITICAL_HOSTS
• 2.10: CRITICAL_NODES
• 2.11: CRITICAL_SUBCLUSTERS
• 2.12: CURRENT_SESSION
• 2.13: DATA_COLLECTOR
• 2.14: DATA_LOADER_EVENTS
• 2.15: DATA_LOADERS
• 2.16: DATA_READS
• 2.17: DATABASE_BACKUPS
• 2.18: DATABASE_CONNECTIONS
• 2.19: DATABASE_MIGRATION_STATUS
• 2.20: DELETE_VECTORS
• 2.21: DEPLOY_STATUS
• 2.22: DEPLOYMENT_PROJECTION_STATEMENTS
• 2.23: DEPLOYMENT_PROJECTIONS
• 2.24: DEPOT_EVICTIONS
• 2.25: DEPOT_FETCH_QUEUE
• 2.26: DEPOT_FETCHES
• 2.27: DEPOT_FILES
• 2.28: DEPOT_PIN_POLICIES
• 2.29: DEPOT_SIZES
• 2.30: DEPOT_UPLOADS
• 2.31: DESIGN_QUERIES
• 2.32: DESIGN_STATUS
• 2.33: DESIGN_TABLES
• 2.34: DESIGNS
• 2.35: DIRECTED_QUERY_STATUS
• 2.36: DISK_QUOTA_USAGES
• 2.37: DISK_RESOURCE_REJECTIONS
• 2.38: DISK_STORAGE
• 2.39: DRAINING_STATUS
• 2.40: ERROR_MESSAGES
• 2.41: EVENT_CONFIGURATIONS
• 2.42: EXECUTION_ENGINE_PROFILES
• 2.43: EXTERNAL_TABLE_DETAILS
• 2.44: HIVE_CUSTOM_PARTITIONS_ACCESSED
• 2.45: HOST_RESOURCES
• 2.46: IO_USAGE
• 2.47: LDAP_LINK_DRYRUN_EVENTS
• 2.48: LDAP_LINK_EVENTS
• 2.49: LOAD_SOURCES
• 2.50: LOAD_STREAMS
• 2.51: LOCK_USAGE
• 2.52: LOCKS
• 2.53: LOG_ROTATE_EVENTS
• 2.54: LOGIN_FAILURES
• 2.55: MEMORY_EVENTS
• 2.56: MEMORY_USAGE
• 2.57: MERGEOUT_PROFILES
• 2.58: MODEL_STATUS_HISTORY
• 2.59: MONITORING_EVENTS
• 2.60: NETWORK_INTERFACES
• 2.61: NETWORK_USAGE
• 2.62: NODE_EVICTIONS
• 2.63: NODE_RESOURCES
• 2.64: NODE_STATES
• 2.65: NOTIFIER_ERRORS
• 2.66: OUTPUT_DEPLOYMENT_STATUS
• 2.67: OUTPUT_EVENT_HISTORY
• 2.68: PARTITION_COLUMNS
• 2.69: PARTITION_REORGANIZE_ERRORS
• 2.70: PARTITION_STATUS
• 2.71: PARTITIONS
• 2.72: PROCESS_SIGNALS
• 2.73: PROJECTION_RECOVERIES
• 2.74: PROJECTION_REFRESHES
• 2.75: PROJECTION_STORAGE
• 2.76: PROJECTION_USAGE
• 2.77: QUERY_CONSUMPTION
• 2.78: QUERY_EVENTS
• 2.79: QUERY_METRICS
• 2.80: QUERY_PLAN_PROFILES
• 2.81: QUERY_PROFILES
• 2.82: QUERY_REQUESTS
• 2.83: REBALANCE_OPERATIONS
• 2.84: REBALANCE_PROJECTION_STATUS
• 2.85: REBALANCE_TABLE_STATUS
• 2.86: RECOVERY_STATUS
• 2.87: REMOTE_REPLICATION_STATUS
• 2.88: REPARENTED_ON_DROP
• 2.89: REPLICATION_STATUS
• 2.90: RESHARDING_EVENTS
• 2.91: RESOURCE_ACQUISITIONS
• 2.92: RESOURCE_POOL_MOVE
• 2.93: RESOURCE_POOL_STATUS
• 2.94: RESOURCE_QUEUES
• 2.95: RESOURCE_REJECTION_DETAILS
• 2.96: RESOURCE_REJECTIONS
• 2.97: RESOURCE_USAGE
• 2.98: SESSION_MARS_STORE
• 2.99: SESSION_PARAMETERS
• 2.100: SESSION_PROFILES
• 2.101: SESSIONS
• 2.102: SPREAD_STATE
• 2.103: SQS_LISTENER_ERROR_HISTORY
• 2.104: STATEMENT_OUTCOMES
• 2.105: STORAGE_BUNDLE_INFO_STATISTICS
• 2.106: STORAGE_BUNDLES
• 2.107: STORAGE_CONTAINERS
• 2.108: STORAGE_POLICIES
• 2.109: STORAGE_TIERS
• 2.110: STORAGE_USAGE
• 2.111: STRATA
• 2.112: STRATA_STRUCTURES
• 2.113: SYSTEM
• 2.114: SYSTEM_RESOURCE_USAGE
• 2.115: SYSTEM_SERVICES
• 2.116: SYSTEM_SESSIONS
• 2.117: TABLE_RECOVERIES
• 2.118: TABLE_RECOVERY_STATUS
• 2.119: TABLE_STATISTICS
• 2.120: TLS_CONFIGURATIONS
• 2.121: TRANSACTIONS
• 2.122: TRUNCATED_SCHEMATA
• 2.123: TUNING_RECOMMENDATIONS
• 2.124: TUPLE_MOVER_OPERATIONS
• 2.125: UDFS_EVENTS
• 2.126: UDFS_OPS_PER_HOUR
• 2.127: UDFS_OPS_PER_MINUTE
• 2.128: UDFS_STATISTICS
• 2.129: UDX_EVENTS
• 2.130: UDX_FENCED_PROCESSES
• 2.131: USER_LIBRARIES
• 2.132: USER_LIBRARY_MANIFEST
• 2.133: USER_SESSIONS

1 - V_CATALOG schema

The system tables in this section reside in the v_catalog schema. These tables provide information (metadata) about the objects in a database; for example, tables, constraints, users, projections, and so on.

1.1 - ACCESS_POLICY

Provides information about existing access policies.

Privileges

By default, only the superuser can view this table. Superusers can grant non-superusers access to this table with the following statement. Non-superusers can only see rows for tables that they own:

=> GRANT SELECT ON access_policy TO PUBLIC

Examples

The following query returns all access policies on table public.customer_dimension:

=> \x
=> SELECT policy_type, is_policy_enabled, table_name, column_name, expression FROM access_policy WHERE table_name = 'public.customer_dimension';
-[ RECORD 1 ]-----+----------------------------------------------------------------------------------------
policy_type       | Column Policy
is_policy_enabled | Enabled
table_name        | public.customer_dimension
column_name       | customer_address
expression        | CASE WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END

1.2 - ALL_TABLES

Provides summary information about tables in a Vertica database.

Examples

onenode=> SELECT DISTINCT table_name, table_type FROM all_tables
          WHERE table_name ILIKE 't%';
       table_name       |  table_type
------------------------+--------------
 types                  | SYSTEM TABLE
 trades                 | TABLE
 tuple_mover_operations | SYSTEM TABLE
 tables                 | SYSTEM TABLE
 tuning_recommendations | SYSTEM TABLE
 testid                 | TABLE
 table_constraints      | SYSTEM TABLE
 transactions           | SYSTEM TABLE
(8 rows)
onenode=> SELECT table_name, table_type FROM all_tables
          WHERE table_name ILIKE 'my%';
 table_name | table_type
------------+------------
 mystocks   | VIEW
(1 row)
=> SELECT * FROM all_tables LIMIT 4;
-[ RECORD 1 ]-------------------------------------------
schema_name | v_catalog
table_id    | 10206
table_name  | all_tables
table_type  | SYSTEM TABLE
remarks     | A complete listing of all tables and views
-[ RECORD 2 ]-------------------------------------------
schema_name | v_catalog
table_id    | 10000
table_name  | columns
table_type  | SYSTEM TABLE
remarks     | Table column information
-[ RECORD 3 ]-------------------------------------------
schema_name | v_catalog
table_id    | 10054
table_name  | comments
table_type  | SYSTEM TABLE
remarks     | User comments on catalog objects
-[ RECORD 4 ]-------------------------------------------
schema_name | v_catalog
table_id    | 10134
table_name  | constraint_columns
table_type  | SYSTEM TABLE
remarks     | Table column constraint information

1.3 - AUDIT_MANAGING_USERS_PRIVILEGES

Provides summary information about privileges, creating, modifying, and deleting users, and authentication changes. This table is a join of LOG_PARAMS, LOG_QUERIES, and LOG_TABLES filtered on the Managing_Users_Privileges category.

1.4 - CA_BUNDLES

Stores certificate authority (CA) bundles created by CREATE CA BUNDLE.

Privileges

• See CA bundle OID, name, and owner: Superuser or owner of the CA bundle.

• See CA bundle contents: Owner of the bundle

Joining with CERTIFICATES

CA_BUNDLES only stores OIDs. Since operations on CA bundles require certificate and owner names, you can use the following query to map bundles to certificate and owner names:

=> SELECT user_name AS owner_name,
       owner     AS owner_oid,
       b.name    AS bundle_name,
       c.name    AS cert_name
FROM   (SELECT name,
               STRING_TO_ARRAY(certificates) :: array[INT] AS certs
        FROM   ca_bundles) b
       LEFT JOIN certificates c
              ON CONTAINS(b.certs, c.oid)
       LEFT JOIN users
              ON user_id = owner
ORDER  BY 1;

 owner_name |     owner_oid     | bundle_name  | cert_name
------------+-------------------+--------------+-----------
 dbadmin    | 45035996273704962 | ca_bundle    | root_ca
 dbadmin    | 45035996273704962 | ca_bundle    | ca_cert
(2 rows)

See also

• Managing CA bundles
• ALTER CA BUNDLE
• DROP CA BUNDLE

1.5 - CATALOG_SUBSCRIPTION_CHANGES

Lists the changes made to catalog subscriptions.

1.6 - CATALOG_SYNC_STATE

Shows when an Eon Mode database node synchronized its catalog to communal storage.

1.7 - CATALOG_TRUNCATION_STATUS

Indicates how up to date the catalog is on communal storage. It is completely up to date when the current catalog version is the same as the catalog truncation version.

The catalog truncation version (CTV) is the version that Vertica cluster uses when it revives after a crash, shutdown, or hibernation. A cluster has only one CTV for all nodes in a cluster.

1.8 - CERTIFICATES

Stores certificates created by CREATE CERTIFICATE.

Examples

See Generating TLS certificates and keys.

1.9 - CLIENT_AUTH

Provides information about client authentication methods.

Higher values indicate higher priorities. Vertica tries to authenticate a user with an authentication method in order of priority from highest to lowest. For example:

• A priority of 10 is higher than a priority of 5.

• A priority 0 is the lowest possible value.

Examples

This example shows how to get information about each client authentication method that you created:

=> SELECT * FROM client_auth;
    auth_oid   |  auth_name  | is_auth_enabled | auth_host_type | auth_host_address | auth_method | auth_parameters | auth_priority | method_priority | address_priority
-------------------+-------------+-----------------+----------------+-------------------+-------------+-----------------+---------------+-----------------+------------------
 45035996274059694 | v_gss       | True            | HOST           | 0.0.0.0/0         | GSS         |                 |             0 |               5 |               96
 45035996274059696 | v_trust     | True            | LOCAL          |                   | TRUST       |                 |             0 |               0 |                0
 45035996274059698 | v_ldap      | True            | HOST           | 10.19.133.123/    | LDAP        |                 |             0 |               5 |              128
 45035996274059700 | RejectNoSSL | True            | HOSTNOSSL      | 0.0.0.0/0         | REJECT      |                 |             0 |              10 |               96
 45035996274059702 | v_hash      | True            | LOCAL          |                   | HASH        |                 |             0 |               2 |                0
 45035996274059704 | v_tls       | True            | HOSTSSL        | 1.1.1.1/0         | TLS         |                 |             0 |               5 |               96
(6 rows)

See also

• Client authentication
• Authentication record priority

1.10 - CLIENT_AUTH_PARAMS

Provides information about client authentication methods that have parameter values assigned.

Examples

This example shows how to retrieve parameter names and values for all authentication methods that you created. The authentication methods that have parameters are:

• v_ident

• v_ldap

• v_ldap1

=> SELECT * FROM CLIENT_AUTH_PARAMS;
     auth_oid      |   auth_name   | auth_parameter_name |    auth_parameter_value
-------------------+---------------+---------------------+------------------------------
 45035996273741304 | v_ident       | system_users        | root
 45035996273741332 | v_gss         |                     |
 45035996273741350 | v_password    |                     |
 45035996273741368 | v_trust       |                     |
 45035996273741388 | v_ldap        | host                | ldap://172.16.65.177
 45035996273741388 | v_ldap        | binddn_prefix       | cn=
 45035996273741388 | v_ldap        | binddn_suffix       | ,dc=qa_domain,dc=com
 45035996273741406 | RejectNoSSL   |                     |
 45035996273741424 | RejectWithSSL |                     |
 45035996273741450 | v_md5         |                     |
 45035996273904044 | l_tls         |                     |
 45035996273906566 | v_hash        |                     |
 45035996273910432 | v_ldap1       | host                | ldap://172.16.65.177
 45035996273910432 | v_ldap1       | basedn              | dc=qa_domain,dc=com
 45035996273910432 | v_ldap1       | binddn              | cn=Manager,dc=qa_domain,dc=com
 45035996273910432 | v_ldap1       | bind_password       | secret
 45035996273910432 | v_ldap1       | search_attribute    | cn
(17 rows)

1.11 - CLUSTER_LAYOUT

Shows the relative position of the actual arrangement of the nodes participating in the cluster and the fault groups (in an Enterprise Mode database) or subclusters (in an Eon Mode database) that affect them. Ephemeral nodes are not shown in the cluster layout ring because they hold no resident data.

See also

1.12 - COLUMNS

Provides table column information. For columns of Iceberg external tables, see ICEBERG_COLUMNS.

Examples

=> SELECT table_schema, table_name, column_name, data_type, is_nullable
   FROM columns WHERE table_schema = 'store'
   AND data_type = 'Date';
 table_schema |    table_name     |      column_name       | data_type | is_nullable
--------------+-------------------+------------------------+-----------+-------------
 store        | store_dimension   | first_open_date        | Date      | f
 store        | store_dimension   | last_remodel_date      | Date      | f
 store        | store_orders_fact | date_ordered           | Date      | f
 store        | store_orders_fact | date_shipped           | Date      | f
 store        | store_orders_fact | expected_delivery_date | Date      | f
 store        | store_orders_fact | date_delivered         | Date      | f
6 rows)

In the following query, datetime_precision is NULL because the table definition declares no precision:

=> CREATE TABLE c (c TIMESTAMP);
CREATE TABLE
=> SELECT table_name, column_name, datetime_precision FROM columns
   WHERE table_name = 'c';
 table_name | column_name | datetime_precision
------------+-------------+--------------------
 c          | c           |
(1 row)

In the following example, timestamp precision is set:

=> DROP TABLE c;
=> CREATE TABLE c (c TIMESTAMP(4));
CREATE TABLE
=> SELECT table_name, column_name, datetime_precision FROM columns
   WHERE table_name = 'c';
 table_name | column_name | datetime_precision
------------+-------------+--------------------
 c          | c           |                  4

An IDENTITY column sequence is defined in a table's DDL. Column values automatically increment as new rows are added. The following query returns identity columns:

=> CREATE TABLE employees (employeeID IDENTITY, fname varchar(36), lname varchar(36));
CREATE TABLE
=> SELECT table_name, column_name, is_identity FROM columns WHERE is_identity = 't';
 table_name | column_name | is_identity
------------+-------------+-------------
 employees  | employeeID  | t
(1 row)

You can query the SEQUENCES table to get detailed information about an IDENTITY column sequence:

=> SELECT sequence_schema, sequence_name, identity_table_name, sequence_id FROM sequences WHERE identity_table_name ='employees';
 sequence_schema |      sequence_name       | identity_table_name |    sequence_id
-----------------+--------------------------+---------------------+-------------------
 public          | employees_employeeID_seq | employees           | 45035996273848816
(1 row)

For details about sequences and IDENTITY columns, see Sequences.

1.13 - COMMENTS

Returns information about comments associated with objects in the database.

Examples

=> COMMENT ON COLUMN public.t.x1 IS 'Column comment';

=> SELECT * FROM COMMENTS;
-[ RECORD 1 ]------+------------------------------
comment_id | 45035996273838776
object_id | 45035996273838760
object_type | COLUMN
object_schema | public
object_name | t
child_object | x1
owner_id | 45035996273704962
owner_name | dbadmin
creation_time | 2023-02-01 10:47:13.028663+08
last_modified_time | 2023-02-01 10:47:13.028663+08
comment | Column comment

1.14 - COMPLEX_TYPES

Contains information about inlined complex types.

Each complex type in each external table has a unique type internally, even if the types are structurally the same (like two different ROW(int,int) cases). This inlined type is created when the table using it is created and is automatically dropped when the table is dropped. Inlined complex types cannot be shared or reused in other tables.

Each row in the COMPLEX_TYPES table represents one component (field) in one complex type. A ROW produces one row per field, an ARRAY produces one, and a MAP produces two.

Arrays of primitive types used in native (ROS) tables are not included in the COMPLEX_TYPES table. They are included instead in the TYPES table.

This table does not include complex types in Iceberg tables.

Examples

The following example shows the type and field values after defining a single external table.

=> CREATE EXTERNAL TABLE warehouse(
    name VARCHAR, id_map MAP<INT,VARCHAR>,
    data row(record INT, total FLOAT, description VARCHAR(100)),
    prices ARRAY[INT], comment VARCHAR(200), sales_total FLOAT, storeID INT)
  AS COPY FROM ... PARQUET;

=> SELECT type_id,type_kind,type_name,field_id,field_name,field_type_name,field_position
    FROM COMPLEX_TYPES ORDER BY type_id,field_name;

      type_id      | type_kind |       type_name       | field_id | field_name  | field_type_name | field_position
-------------------+-----------+-----------------------+----------+-------------+-----------------+----------------
 45035996274278280 | Map       | _ct_45035996274278280 |        6 | key         | int             |              0
 45035996274278280 | Map       | _ct_45035996274278280 |        9 | value       | varchar(80)     |              1
 45035996274278282 | Row       | _ct_45035996274278282 |        9 | description | varchar(80)     |              2
 45035996274278282 | Row       | _ct_45035996274278282 |        6 | record      | int             |              0
 45035996274278282 | Row       | _ct_45035996274278282 |        7 | total       | float           |              1
 45035996274278284 | Array     | _ct_45035996274278284 |        6 |             | int             |              0
(6 rows)

1.15 - CONSTRAINT_COLUMNS

Records information about table column constraints.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

1.16 - CRYPTOGRAPHIC_KEYS

Stores private keys created by CREATE KEY.

Examples

See Generating TLS certificates and keys.

1.17 - DATABASES

Provides information about the databases in this Vertica installation.

Examples

This example queries the databases table from a master database.

=> SELECT * FROM DATABASES;
-[ RECORD 1 ]----------+-------------------------------------------------------------
database_id            | 45035996273704976
database_name          | VMart
owner_id               | 45035996273704962
owner_name             | dbadmin
start_time             | 2017-10-22 05:16:22.066961-04
compliance_message     | The database is in compliance with respect to raw data size.
export_subnet          | 0
load_balance_policy    | none
backend_address_family | ipv4
branch_name            |

1.18 - DIRECTED_QUERIES

Returns information about directed queries.

Privileges

Truncated query results

Query results for the fields INPUT_QUERY and ANNOTATED_QUERY are truncated after ~32K characters. You can get the full content of both fields in two ways:

• Use the statement GET DIRECTED QUERY.

• Use EXPORT_CATALOG to export directed queries.

1.19 - DUAL

DUAL is a single-column "dummy" table with one record whose value is X; for example:

=> SELECT * FROM DUAL;
 dummy
-------
 X
(1 row)

You can write the following types of queries:

=> SELECT 1 FROM dual;
 ?column?
----------
        1
(1 row)
=> SELECT current_timestamp, current_user FROM dual;
           ?column?            | current_user
-------------------------------+--------------
 2010-03-08 12:57:32.065841-05 | release
(1 row)
=> CREATE TABLE t1(col1 VARCHAR(20), col2 VARCHAR(2));
=> INSERT INTO T1(SELECT 'hello' AS col1, 1 AS col2 FROM dual);)
=> SELECT * FROM t1;
 col1  | col2
-------+------
 hello | 1
(1 row

Restrictions

You cannot create projections for DUAL.

1.20 - ELASTIC_CLUSTER

Returns information about cluster elasticity, such as whether Elastic cluster is running.

Privileges

Superuser

See also

• ENABLE_ELASTIC_CLUSTER
• Elastic cluster

1.21 - EPOCHS

For the most recently closed epochs, lists the date and time of the close and the corresponding epoch number of the closed epoch. The EPOCHS table may return a varying number of rows depending on current commit activities.

Examples

=> SELECT * FROM EPOCHS;
       epoch_close_time        | epoch_number
-------------------------------+--------------
 2018-11-12 16:05:15.552571-05 |           16
(1 row)

Querying for historical data

If you need historical data about epochs and corresponding date information, query the DC_TRANSACTION_ENDS table.

=> select dc.end_epoch,min(dc.time),max(dc.time) from dc_transaction_ends dc group by end_epoch;
 end_epoch |             min               | max
-----------+-------------------------------+-------------------------------
       214 |  2018-10-12 08:05:47.02075-04 | 2018-10-15 10:22:24.015292-04
       215 | 2018-10-15 10:22:47.015172-04 | 2018-10-15 13:00:44.888984-04
                          ...
       226 | 2018-10-15 15:03:47.015235-04 | 2018-10-15 20:37:34.346667-04
       227 | 2018-10-15 20:37:47.008137-04 | 2018-10-16 07:39:00.29917-04
       228 | 2018-10-16 07:39:47.012411-04 | 2018-10-16 08:16:01.470232-04
       229 | 2018-10-16 08:16:47.018899-04 | 2018-10-16 08:21:13.854348-04
       230 | 2018-10-16 08:21:47.013767-04 | 2018-10-17 12:21:09.224094-04
       231 |  2018-10-17 12:21:09.23193-04 | 2018-10-17 15:11:59.338777-04

See also

• Epoch management parameters
• Epoch functions

1.22 - FAULT_GROUPS

View the fault groups and their hierarchy in the cluster.

Examples

Show the current hierarchy of fault groups in the cluster:

vmartdb=> SELECT member_type, member_name, parent_type, CASE
          WHEN parent_type = 'DATABASE' THEN ''
          ELSE parent_name END FROM fault_groups
          ORDER BY member_name;
 member_type | member_name           | parent_type | parent_name
-------------+-----------------------+-------------+-------------
 NODE        | v_vmart_node0001      | FAULT GROUP | two
 NODE        | v_vmart_node0002      | FAULT GROUP | two
 NODE        | v_vmart_node0003      | FAULT GROUP | three
 FAULT GROUP | one                   | DATABASE    |
 FAULT GROUP | three                 | DATABASE    |
 FAULT GROUP | two                   | FAULT GROUP | one

View the distribution of the segment layout:

vmartdb=> SELECT segment_layout from elastic_cluster;
                           segment_layout
-------------------------------------------------------------------------
 v_vmart_node0001[33.3%] v_vmart_node0003[33.3%] v_vmart_node0004[33.3%]
(1 row)

See also

• High availability with fault groups
• Fault groups

1.23 - FOREIGN_KEYS

Provides foreign key information.

Examples

mydb=> SELECT
          constraint_name,
          table_name,
          ordinal_position,
          reference_table_name
       FROM foreign_keys ORDER BY 3;
      constraint_name      |    table_name     | ordinal_position | reference_table_name
---------------------------+-------------------+------------------+-----------------------
 fk_store_sales_date       | store_sales_fact  |                1 | date_dimension
 fk_online_sales_saledate  | online_sales_fact |                1 | date_dimension
 fk_store_orders_product   | store_orders_fact |                1 | product_dimension
 fk_inventory_date         | inventory_fact    |                1 | date_dimension
 fk_inventory_product      | inventory_fact    |                2 | product_dimension
 fk_store_sales_product    | store_sales_fact  |                2 | product_dimension
 fk_online_sales_shipdate  | online_sales_fact |                2 | date_dimension
 fk_store_orders_product   | store_orders_fact |                2 | product_dimension
 fk_inventory_product      | inventory_fact    |                3 | product_dimension
 fk_store_sales_product    | store_sales_fact  |                3 | product_dimension
 fk_online_sales_product   | online_sales_fact |                3 | product_dimension
 fk_store_orders_store     | store_orders_fact |                3 | store_dimension
 fk_online_sales_product   | online_sales_fact |                4 | product_dimension
 fk_inventory_warehouse    | inventory_fact    |                4 | warehouse_dimension
 fk_store_orders_vendor    | store_orders_fact |                4 | vendor_dimension
 fk_store_sales_store      | store_sales_fact  |                4 | store_dimension
 fk_store_orders_employee  | store_orders_fact |                5 | employee_dimension
 fk_store_sales_promotion  | store_sales_fact  |                5 | promotion_dimension
 fk_online_sales_customer  | online_sales_fact |                5 | customer_dimension
 fk_store_sales_customer   | store_sales_fact  |                6 | customer_dimension
 fk_online_sales_cc        | online_sales_fact |                6 | call_center_dimension
 fk_store_sales_employee   | store_sales_fact  |                7 | employee_dimension
 fk_online_sales_op        | online_sales_fact |                7 | online_page_dimension
 fk_online_sales_shipping  | online_sales_fact |                8 | shipping_dimension
 fk_online_sales_warehouse | online_sales_fact |                9 | warehouse_dimension
 fk_online_sales_promotion | online_sales_fact |               10 | promotion_dimension
(26 rows)

1.24 - GRANTS

Returns information about privileges that are explicitly granted on database objects. Information about inherited privileges is not included.

Examples

The following query shows the privileges that are granted to user Rob or role R1. An asterisk (*) appended to a privilege indicates that the user can grant the privilege to other users:

=> SELECT grantor,privileges_description,object_name,object_type,grantee FROM grants WHERE grantee='Rob' OR grantee='R1';
 grantor |  privileges_description   | object_name | object_type  | grantee
 --------+---------------------------+-------------+--------------+---------
 dbadmin | USAGE                     | general     | RESOURCEPOOL | Rob
 dbadmin | USAGE, CREATE             | s1          | SCHEMA       | Rob
 dbadmin | INSERT*, SELECT*, UPDATE* | t1          | TABLE        | Rob
 dbadmin | SELECT                    | t1          | TABLE        | R1
 dbadmin | USAGE                     | s1          | SCHEMA       | R1
 dbadmin |                           | R1          | ROLE         | Rob
 (6 rows)

See also

• HAS_ROLE
• ROLES
• USERS
• Database users and privileges

1.25 - HCATALOG_COLUMNS

Describes the columns of all tables available through the HCatalog Connector. Each row in this table corresponds to to a column in a table accessible through the HCatalog Connector. See Using the HCatalog Connector for more information.

Privileges

No explicit permissions are required; however, users see only the records that correspond to schemas they have permissions to access.

Notes

If you are using WebHCat instead of HiveServer2, querying this table results in one web service call to the WebHCat server for each table in each HCatalog schema. If you need to perform multiple queries on this table in a short period of time, consider creating a copy of the table using a CREATE TABLE AS statement to improve performance. The copy does not reflect any changes made to the schema of the Hive tables after it was created, but it is much faster to query.

Examples

The following example demonstrates finding the column information for a specific table:

=> SELECT * FROM HCATALOG_COLUMNS WHERE table_name = 'hcatalogtypes'
-> ORDER BY ordinal_position;
-[ RECORD 1 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | intcol
hcatalog_data_type       | int
data_type                | int
data_type_id             | 6
data_type_length         | 8
character_maximum_length |
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 1
-[ RECORD 2 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | floatcol
hcatalog_data_type       | float
data_type                | float
data_type_id             | 7
data_type_length         | 8
character_maximum_length |
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 2
-[ RECORD 3 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | doublecol
hcatalog_data_type       | double
data_type                | float
data_type_id             | 7
data_type_length         | 8
character_maximum_length |
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 3
-[ RECORD 4 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | charcol
hcatalog_data_type       | string
data_type                | varchar(65000)
data_type_id             | 9
data_type_length         | 65000
character_maximum_length | 65000
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 4
-[ RECORD 5 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | varcharcol
hcatalog_data_type       | string
data_type                | varchar(65000)
data_type_id             | 9
data_type_length         | 65000
character_maximum_length | 65000
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 5
-[ RECORD 6 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | boolcol
hcatalog_data_type       | boolean
data_type                | boolean
data_type_id             | 5
data_type_length         | 1
character_maximum_length |
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 6
-[ RECORD 7 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | timestampcol
hcatalog_data_type       | string
data_type                | varchar(65000)
data_type_id             | 9
data_type_length         | 65000
character_maximum_length | 65000
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 7
-[ RECORD 8 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | varbincol
hcatalog_data_type       | binary
data_type                | varbinary(65000)
data_type_id             | 17
data_type_length         | 65000
character_maximum_length | 65000
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 8
-[ RECORD 9 ]------------+-----------------
table_schema             | hcat
hcatalog_schema          | default
table_name               | hcatalogtypes
is_partition_column      | f
column_name              | bincol
hcatalog_data_type       | binary
data_type                | varbinary(65000)
data_type_id             | 17
data_type_length         | 65000
character_maximum_length | 65000
numeric_precision        |
numeric_scale            |
datetime_precision       |
interval_precision       |
ordinal_position         | 9

See also

• HCATALOG_SCHEMATA
• HCATALOG_TABLES
• HCATALOG_TABLE_LIST

1.26 - HCATALOG_SCHEMATA

Lists all of the schemas defined using the HCatalog Connector. See Using the HCatalog Connector.

Unlike other HCatalog Connector-related system tables, this table makes no calls to Hive, so querying incurs very little overhead.

Privileges

No explicit permissions are required; however, users see only the records that correspond to schemas they have permissions to access.

See also

• HCATALOG_COLUMNS
• HCATALOG_TABLE_LIST
• HCATALOG_TABLES

1.27 - HCATALOG_TABLE_LIST

A concise list of all tables contained in all Hive schemas and databases available through the HCatalog Connector. See Using the HCatalog Connector.

Privileges

No explicit permissions are required; however, users see only the records that correspond to schemas they have permissions to access.

Notes

• Querying this table results in one call to HiveServer2 for each Hive schema defined using the HCatalog Connector. This means that the query usually takes longer than querying other system tables.

• Querying this table is faster than querying HCATALOG_TABLES. Querying HCATALOG_TABLE_LIST only makes one HiveServer2 call per HCatalog schema versus one call per table for HCATALOG_TABLES.

Examples

The following example demonstrates defining a new HCatalog schema then querying HCATALOG_TABLE_LIST. Note that one table defined in a different HCatalog schema also appears. HCATALOG_TABLE_LIST lists all of the tables available in any of the HCatalog schemas:

=> CREATE HCATALOG SCHEMA hcat WITH hostname='hcathost'
-> HCATALOG_SCHEMA='default' HCATALOG_DB='default' HCATALOG_USER='hcatuser';
CREATE SCHEMA
=> \x
Expanded display is on.
=> SELECT * FROM v_catalog.hcatalog_table_list;
-[ RECORD 1 ]------+------------------
table_schema_id    | 45035996273748980
table_schema       | hcat
hcatalog_schema    | default
table_name         | weblogs
hcatalog_user_name | hcatuser
-[ RECORD 2 ]------+------------------
table_schema_id    | 45035996273748980
table_schema       | hcat
hcatalog_schema    | default
table_name         | tweets
hcatalog_user_name | hcatuser
-[ RECORD 3 ]------+------------------
table_schema_id    | 45035996273748980
table_schema       | hcat
hcatalog_schema    | default
table_name         | messages
hcatalog_user_name | hcatuser
-[ RECORD 4 ]------+------------------
table_schema_id    | 45035996273864948
table_schema       | hiveschema
hcatalog_schema    | default
table_name         | weblogs
hcatalog_user_name | hcatuser

See also

• HCATALOG_COLUMNS
• HCATALOG_SCHEMATA
• HCATALOG_TABLES

1.28 - HCATALOG_TABLES

Returns a detailed list of all tables made available through the HCatalog Connector. See Using the HCatalog Connector.

Privileges

No explicit permissions are required; however, users see only the records that correspond to schemas they have permissions to access.

See also

• HCATALOG_SCHEMATA
• HCATALOG_COLUMNS
• HCATALOG_TABLE_LIST

1.29 - ICEBERG_COLUMNS

Provides column information for Iceberg external tables. The information in this table is drawn from the Iceberg metadata files at query time.

1.30 - INHERITED_PRIVILEGES

Provides summary information about privileges inherited by objects from GRANT statements on parent schemas, excluding inherited grant options.

For information about explicitly granted permissions, see system table GRANTS.

Examples

The following query returns the privileges that the tables and views inherit from their parent schema, customers.

=> SELECT object_schema,object_name,object_type,privileges_description,principal,grantor FROM inherited_privileges WHERE object_schema='customers';
 object_schema |  object_name    | object_type |                          privileges_description                           | principal | grantor
 --------------+-----------------+-------------+---------------------------------------------------------------------------+-----------+---------
 customers     | cust_info       | Table       | INSERT, SELECT, UPDATE, DELETE, ALTER, REFERENCES, DROP, TRUNCATE         | dbadmin   | dbadmin
 customers     | shipping_info   | Table       | INSERT, SELECT, UPDATE, DELETE, ALTER, REFERENCES, DROP, TRUNCATE         | dbadmin   | dbadmin
 customers     | cust_set        | View        | SELECT, ALTER, DROP                                                       | dbadmin   | dbadmin
 customers     | cust_info       | Table       | SELECT                                                                    | Val       | dbadmin
 customers     | shipping_info   | Table       | SELECT                                                                    | Val       | dbadmin
 customers     | cust_set        | View        | SELECT                                                                    | Val       | dbadmin
 customers     | sales_model     | Model       | USAGE, ALTER, DROP                                                        | Pooja     | dbadmin
 customers     | shipping_info   | Table       | INSERT                                                                    | Pooja     | dbadmin
 (8 rows)

See also

• INHERITING_OBJECTS
• Database users and privileges
• GET_PRIVILEGES_DESCRIPTION

1.31 - INHERITING_OBJECTS

Provides information about the objects (table, view, or model) that inherit privileges from their parent schemas.

For information about the particular privileges inherited from schemas and their associated GRANT statements, see the INHERITED_PRIVILEGES table.

Examples

The following query returns the tables and views that inherit their privileges from their parent schema, customers.

=> SELECT * FROM inheriting_objects WHERE object_schema='customers';
     object_id     |     schema_id     | object_schema |  object_name      | object_type
 ------------------+-------------------+---------------+-------------------+-------------
 45035996273980908 | 45035996273980902 | customers     | cust_info         | table
 45035996273980984 | 45035996273980902 | customers     | shipping_info     | table
 45035996273980980 | 45035996273980902 | customers     | cust_set          | view
 45035996273980983 | 45035996273980901 | ml_models     | clustering_model  | model
 (3 rows)

See also

• INHERITED_PRIVILEGES
• Inherited privileges
• Database users and privileges
• GET_PRIVILEGES_DESCRIPTION

1.32 - KEYWORDS

Identifies Vertica reserved and non-reserved keywords.

Examples

The following query gets all reserved keywords that begin with B:

=> SELECT * FROM keywords WHERE reserved = 'R' AND keyword ilike 'B%';
 keyword | reserved
---------+----------
 BETWEEN | R
 BIGINT  | R
 BINARY  | R
 BIT     | R
 BOOLEAN | R
 BOTH    | R
(6 rows)

See also

1.33 - LARGE_CLUSTER_CONFIGURATION_STATUS

Shows the current cluster nodes and control node (spread hosts) designations in the Catalog so you can see if they match.

See also

Large Cluster

1.34 - LICENSE_AUDITS

Lists the results of Vertica's license automatic compliance audits. See How Vertica Calculates Database Size.

1.35 - LICENSES

For all licenses, provides information on license types, the dates for which licenses are valid, and the limits the licenses impose.

1.36 - LOAD_BALANCE_GROUPS

Lists the objects contained by all load balance groups. Each row in this table represents a single object that is a member of a load balance group. If a load balance group does not contain any objects, it appears once in this table with its type column set to 'Empty Group.'

Examples

=> SELECT * FROM LOAD_BALANCE_GROUPS;
     name      |   policy   |  filter   |         type          | object_name
---------------+------------+-----------+-----------------------+-------------
 group_1       | ROUNDROBIN |           | Network Address Group | node01
 group_1       | ROUNDROBIN |           | Network Address Group | node02
 group_2       | ROUNDROBIN |           | Empty Group           |
 group_all     | ROUNDROBIN |           | Network Address Group | node01
 group_all     | ROUNDROBIN |           | Network Address Group | node02
 group_all     | ROUNDROBIN |           | Network Address Group | node03
 group_fault_1 | RANDOM     | 0.0.0.0/0 | Fault Group           | fault_1
(7 rows)

See also

• NETWORK_ADDRESSES
• ALTER LOAD BALANCE GROUP
• ALTER NETWORK ADDRESS
• ALTER ROUTING RULE
• CREATE LOAD BALANCE GROUP

1.37 - LOG_PARAMS

Provides summary information about changes to configuration parameters related to authentication and security run in your database.

Examples

The following example queries the LOG_PARAMS system table and shows only the most recent configuration parameter for this user under the Authentication category:

=> SELECT * FROM log_params limit 1;

--------------------------------------------------------------------------------------------------------

issued_time   | 2018-02-12 13:41:20.837452-05
user_name     | dbadmin
user_id       | 45035996273704962
hostname      | ::1:50690
session_id    | v_vmart_node0001-341751:0x13878
audit_type    | Param
audit_tag_name| SecurityAlgorithm
request_type  | UTILITY
request_id    | 8
subject       | MD5
request       | select set_config_parameter('SecurityAlgorithm','MD5',null);
success       | t
category      | Authentication
(1 row)

1.38 - LOG_QUERIES

Provides summary information about some queries related to authentication and security run in your database.

Examples

The following example queries the LOG_QUERIES system table and shows only the most recent query for this user under the Managing_Users_Privileges category:

=> SELECT * FROM log_queries limit 1;
---------------------------------------------------------------------------

issued_time   | 2018-01-22 10:36:55.634349-05
user_name     | dbadmin
user_id       | 45035996273704962
hostname      |
session_id    | v_vmart_node0001-237210:0x37e1d
audit_type    | Query
audit_tag_name| REVOKE ROLE
request_type  | DDL
request_id    | 2
subject       |
request       | revoke all privileges from Joe;
success       | f
category      | Managing_Users_Privileges
(1 row)

1.39 - LOG_TABLES

Provides summary information about queries on system tables.

Examples

The following example shows recent queries on configuration parameters:

dbadmin=> SELECT issued_time, audit_type, request_type, subject, request, category FROM log_tables
   WHERE category ilike '%Managing_Config_Parameters%' ORDER BY issued_time DESC LIMIT 4;
-[ RECORD 1 ]+-------------------------------------------------------------------------------------
issued_time  | 2020-05-14 14:14:53.453552-04
audit_type   | Table
request_type | QUERY
subject      | vs_nodes
request      | SELECT * from vs_nodes order by name  limit 1;
category     | Managing_Config_Parameters
-[ RECORD 2 ]+-------------------------------------------------------------------------------------
issued_time  | 2020-05-14 14:14:27.546474-04
audit_type   | Table
request_type | QUERY
subject      | vs_nodes
request      | SELECT * from vs_nodes order by name ;
category     | Managing_Config_Parameters
-[ RECORD 3 ]+-------------------------------------------------------------------------------------
issued_time  | 2020-05-14 08:54:32.86881-04
audit_type   | Table
request_type | QUERY
subject      | vs_parameters_mismatch
request      | select * from configuration_parameters where parameter_name = 'MaxDepotSizePercent';
category     | Managing_Config_Parameters
-[ RECORD 4 ]+-------------------------------------------------------------------------------------
issued_time  | 2020-05-14 08:54:32.86881-04
audit_type   | Table
request_type | QUERY
subject      | vs_nodes
request      | select * from configuration_parameters where parameter_name = 'MaxDepotSizePercent';
category     | Managing_Config_Parameters

1.40 - MATERIALIZE_FLEXTABLE_COLUMNS_RESULTS

Contains the results of running the MATERIALIZE_FLEXTABLE_COLUMNS function. The table contains information about keys that the function evaluated. It does not contain information about all keys.

Examples

=> \x
Expanded display is on.
=> SELECT table_id, table_schema, table_name, key_name, status, message FROM MATERIALIZE_FLEXTABLE_COLUMNS_RESULTS
WHERE table_name = 'mountains_hybrid';
-[ RECORD 1 ]+-------------------------------------------------------
table_id     | 45035996273708192
table_schema | public
table_name   | mountains_hybrid
key_name     | type
status       | ADDED
message      | Added successfully
-[ RECORD 2 ]+-------------------------------------------------------
table_id     | 45035996273708192
table_schema | public
table_name   | mountains_hybrid
key_name     | height
status       | ADDED
message      | Added successfully
-[ RECORD 3 ]+-------------------------------------------------------
table_id     | 45035996273708192
table_schema | public
table_name   | mountains_hybrid
key_name     | name
status       | EXISTS
message      | Column of same name already exists in table definition

1.41 - MODELS

Lists details about the machine-learning models in the database.

Examples

=> SELECT * FROM models;
-[ RECORD 1 ]---------+------------------------------
model_id              | 45035996273850350
model_name            | xgb_iris
schema_id             | 45035996273704984
schema_name           | public
owner_id              | 45035996273704962
owner_name            | dbadmin
category              | VERTICA_MODELS
model_type            | XGB_CLASSIFIER
is_complete           | t
create_time           | 2022-11-29 13:38:59.259531-05
size                  | 11018
is_inherit_privileges | f
-[ RECORD 2 ]---------+------------------------------
model_id              | 45035996273866816
model_name            | arima_weather
schema_id             | 45035996273704984
schema_name           | public
owner_id              | 45035996273704962
owner_name            | dbadmin
category              | VERTICA_MODELS
model_type            | ARIMA
is_complete           | t
create_time           | 2023-03-31 13:13:06.342661-05
size                  | 2770
is_inherit_privileges | f

1.42 - NAMESPACES

Lists details about the namespaces in an Eon Mode database.

Example

List the details of all namespaces in your database:

=> SELECT * FROM NAMESPACES;
   namespace_oid   |  namespace_name   | is_default | default_shard_count
-------------------+-------------------+------------+---------------------
 45035996273704988 | default_namespace | t          |                   9
 32582959204859183 | analytics         | f          |                  12
(2 rows)

1.43 - NETWORK_ADDRESSES

Lists information about the network addresses defined in your database using the CREATE NETWORK ADDRESS statement.

Examples

=> \x
Expanded display is on.

=> SELECT * FROM v_catalog.network_addresses;
-[ RECORD 1 ]----+-----------------
name             | node01
node             | v_vmart_node0001
address          | 10.20.100.247
port             | 5433
address_family   | ipv4
is_enabled       | t
is_auto_detected | f
-[ RECORD 2 ]----+-----------------
name             | node02
node             | v_vmart_node0002
address          | 10.20.100.248
port             | 5433
address_family   | ipv4
is_enabled       | t
is_auto_detected | f
-[ RECORD 3 ]----+-----------------
name             | node03
node             | v_vmart_node0003
address          | 10.20.100.249
port             | 5433
address_family   | ipv4
is_enabled       | t
is_auto_detected | f

See also

• LOAD_BALANCE_GROUPS
• ALTER LOAD BALANCE GROUP
• ALTER NETWORK ADDRESS
• ALTER ROUTING RULE
• CREATE LOAD BALANCE GROUP

1.44 - NODE_SUBSCRIPTION_CHANGE_PHASES

In an Eon Mode database, stores information about changes to node's shard subscriptions.

Examples

=> SELECT NODE_NAME, SUBSCRIPTION_CHANGE_TYPE, SHARD_NAME,
   ACTION  FROM node_subscription_change_phases
   ORDER BY start_time ASC LIMIT 10;

      NODE_NAME       | SUBSCRIPTION_CHANGE_TYPE | SHARD_NAME  |         ACTION
----------------------+--------------------------+-------------+------------------------
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0007 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0010 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0004 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0005 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | replica     | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0005 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0006 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0008 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0011 | COLLECT SHARD METADATA
 v_verticadb_node0001 | CREATE SUBSCRIPTION      | segment0002 | COLLECT SHARD METADATA

1.45 - NODE_SUBSCRIPTIONS

Eon Mode only

Lists information about database node subscriptions to shards.

Examples

The following example queries the NODE_SUBSCRIPTIONS table in a database with two three-node subclusters (a primary and a secondary) in a 12-shard database.

=> SELECT node_name, shard_name, subscription_state, is_primary,
          is_participating_primary AS is_p_primary
       FROM NODE_SUBSCRIPTIONS ORDER BY node_name, shard_name;

      node_name       | shard_name  | subscription_state | is_primary | is_p_primary
----------------------+-------------+--------------------+------------+--------------
 v_verticadb_node0001 | replica     | ACTIVE             | t          | t
 v_verticadb_node0001 | segment0001 | ACTIVE             | t          | t
 v_verticadb_node0001 | segment0003 | ACTIVE             | f          | f
 v_verticadb_node0001 | segment0004 | ACTIVE             | t          | t
 v_verticadb_node0001 | segment0006 | ACTIVE             | f          | f
 v_verticadb_node0001 | segment0007 | ACTIVE             | t          | t
 v_verticadb_node0001 | segment0009 | ACTIVE             | f          | f
 v_verticadb_node0001 | segment0010 | ACTIVE             | t          | t
 v_verticadb_node0001 | segment0012 | ACTIVE             | f          | f
 v_verticadb_node0002 | replica     | ACTIVE             | f          | t
 v_verticadb_node0002 | segment0001 | ACTIVE             | f          | f
 v_verticadb_node0002 | segment0002 | ACTIVE             | t          | t
 v_verticadb_node0002 | segment0004 | ACTIVE             | f          | f
 v_verticadb_node0002 | segment0005 | ACTIVE             | t          | t
 v_verticadb_node0002 | segment0007 | ACTIVE             | f          | f
 v_verticadb_node0002 | segment0008 | ACTIVE             | t          | t
 v_verticadb_node0002 | segment0010 | ACTIVE             | f          | f
 v_verticadb_node0002 | segment0011 | ACTIVE             | t          | t
 v_verticadb_node0003 | replica     | ACTIVE             | f          | t
 v_verticadb_node0003 | segment0002 | ACTIVE             | f          | f
 v_verticadb_node0003 | segment0003 | ACTIVE             | t          | t
 v_verticadb_node0003 | segment0005 | ACTIVE             | f          | f
 v_verticadb_node0003 | segment0006 | ACTIVE             | t          | t
 v_verticadb_node0003 | segment0008 | ACTIVE             | f          | f
 v_verticadb_node0003 | segment0009 | ACTIVE             | t          | t
 v_verticadb_node0003 | segment0011 | ACTIVE             | f          | f
 v_verticadb_node0003 | segment0012 | ACTIVE             | t          | t
 v_verticadb_node0004 | replica     | ACTIVE             | f          | t
 v_verticadb_node0004 | segment0001 | ACTIVE             | f          | t
 v_verticadb_node0004 | segment0003 | ACTIVE             | f          | f
 v_verticadb_node0004 | segment0004 | ACTIVE             | f          | t
 v_verticadb_node0004 | segment0006 | ACTIVE             | f          | f
 v_verticadb_node0004 | segment0007 | ACTIVE             | f          | t
 v_verticadb_node0004 | segment0009 | ACTIVE             | f          | f
 v_verticadb_node0004 | segment0010 | ACTIVE             | f          | t
 v_verticadb_node0004 | segment0012 | ACTIVE             | f          | f
 v_verticadb_node0005 | replica     | ACTIVE             | f          | t
 v_verticadb_node0005 | segment0001 | ACTIVE             | f          | f
 v_verticadb_node0005 | segment0002 | ACTIVE             | f          | t
 v_verticadb_node0005 | segment0004 | ACTIVE             | f          | f
 v_verticadb_node0005 | segment0005 | ACTIVE             | f          | t
 v_verticadb_node0005 | segment0007 | ACTIVE             | f          | f
 v_verticadb_node0005 | segment0008 | ACTIVE             | f          | t
 v_verticadb_node0005 | segment0010 | ACTIVE             | f          | f
 v_verticadb_node0005 | segment0011 | ACTIVE             | f          | t
 v_verticadb_node0006 | replica     | ACTIVE             | f          | t
 v_verticadb_node0006 | segment0002 | ACTIVE             | f          | f
 v_verticadb_node0006 | segment0003 | ACTIVE             | f          | t
 v_verticadb_node0006 | segment0005 | ACTIVE             | f          | f
 v_verticadb_node0006 | segment0006 | ACTIVE             | f          | t
 v_verticadb_node0006 | segment0008 | ACTIVE             | f          | f
 v_verticadb_node0006 | segment0009 | ACTIVE             | f          | t
 v_verticadb_node0006 | segment0011 | ACTIVE             | f          | f
 v_verticadb_node0006 | segment0012 | ACTIVE             | f          | t
(54 rows)

1.46 - NODES

Lists details about the nodes in the database.

Example

=> SELECT NODE_NAME, NODE_STATE, IS_PRIMARY, IS_READONLY, NODE_TYPE,
          SUBCLUSTER_NAME FROM NODES ORDER BY NODE_NAME ASC;
      NODE_NAME       | NODE_STATE | IS_PRIMARY | IS_READONLY | NODE_TYPE |  SUBCLUSTER_NAME
----------------------+------------+------------+-------------+-----------+--------------------
 v_verticadb_node0001 | UP         | t          | f           | PERMANENT | default_subcluster
 v_verticadb_node0002 | UP         | t          | f           | PERMANENT | default_subcluster
 v_verticadb_node0003 | UP         | t          | f           | PERMANENT | default_subcluster
 v_verticadb_node0004 | UP         | f          | f           | PERMANENT | analytics
 v_verticadb_node0005 | UP         | f          | f           | PERMANENT | analytics
 v_verticadb_node0006 | UP         | f          | f           | PERMANENT | analytics
(6 rows)

1.47 - ODBC_COLUMNS

Provides table column information. The format is defined by the ODBC standard for the ODBC SQLColumns metadata. Details on the ODBC SQLColumns format are available in the ODBC specification: http://msdn.microsoft.com/en-us/library/windows/desktop/ms711683%28v=vs.85%29.aspx.

1.48 - PASSWORD_AUDITOR

Stores information about user accounts, account expirations, and password hashing algorithms.

1.49 - PASSWORDS

Contains information on current user passwords. This table also includes information on past passwords if any Profiles have PASSWORD_REUSE_TIME or PASSWORD_REUSE_MAX parameters set. See CREATE PROFILE for details.

Examples

The following query returns the SHA-512 hashed password and salt of user 'u1'.

=> SELECT user_name, password, salt FROM passwords WHERE user_name='u1';
 user_name |                         password                       |               salt
-----------+--------------------------------------------------------+----------------------------------
 u1        | sha512f3f802f1c56e2530cd9c3164cc7b8002ba444c0834160f10 | f05e9d859fb441f9f612f8a787bfc872
(1 row)

1.50 - PRIMARY_KEYS

Provides primary key information.

1.51 - PROFILE_PARAMETERS

Defines what information is stored in profiles.

1.52 - PROFILES

Provides information about password policies that you set using the CREATE PROFILE statement.

Notes

Non-superusers querying this table see only the information for the profile to which they are assigned.

See also

• CREATE PROFILE
• ALTER PROFILE

1.53 - PROJECTION_CHECKPOINT_EPOCHS

Provides details on checkpoint epochs, applies only to Enterprise Mode.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

Examples

=> SELECT node_name, projection_schema, projection_name, is_up_to_date, checkpoint_epoch FROM projection_checkpoint_epochs
    WHERE projection_name ilike 't1_b%' ORDER BY projection_name, node_name;
    node_name     | projection_schema | projection_name | is_up_to_date | checkpoint_epoch
------------------+-------------------+-----------------+---------------+------------------
 v_vmart_node0001 | public            | t1_b1           | t             |              965
 v_vmart_node0002 | public            | t1_b1           | t             |              965
 v_vmart_node0003 | public            | t1_b1           | t             |              965
 v_vmart_node0001 | public            | t1_b0           | t             |              965
 v_vmart_node0002 | public            | t1_b0           | t             |              965
 v_vmart_node0003 | public            | t1_b0           | t             |              965
(6 rows)

dbadmin=> INSERT INTO t1 VALUES (100, 101, 102);
 OUTPUT
--------
      1
(1 row)

dbadmin=> COMMIT;
COMMIT
dbadmin=> SELECT node_name, projection_schema, projection_name, is_up_to_date, checkpoint_epoch FROM projection_checkpoint_epochs
           WHERE projection_name ILIKE 't1_b%' ORDER BY projection_name, node_name;
    node_name     | projection_schema | projection_name | is_up_to_date | checkpoint_epoch
------------------+-------------------+-----------------+---------------+------------------
 v_vmart_node0001 | public            | t1_b1           | t             |              966
 v_vmart_node0002 | public            | t1_b1           | t             |              966
 v_vmart_node0003 | public            | t1_b1           | t             |              966
 v_vmart_node0001 | public            | t1_b0           | t             |              966
 v_vmart_node0002 | public            | t1_b0           | t             |              966
 v_vmart_node0003 | public            | t1_b0           | t             |              966
(6 rows)

1.54 - PROJECTION_COLUMNS

Provides information about projection columns, such as encoding type, sort order, type of statistics, and the time at which columns statistics were last updated.

Examples

See Statistics Data in PROJECTION_COLUMNS

See also

• PROJECTIONS
• ANALYZE_STATISTICS
• CREATE PROJECTION

1.55 - PROJECTION_DELETE_CONCERNS

Lists projections whose design are liable to cause performance issues when deleting data. This table is generated by calling the EVALUATE_DELETE_PERFORMANCE function. See Optimizing DELETE and UPDATE for more information.

1.56 - PROJECTIONS

Provides information about projections.

Examples

The following projection defines a range of orders placed since the first of the current year:

=> CREATE PROJECTION ytd_orders AS SELECT * FROM store_orders ORDER BY order_date
    ON PARTITION RANGE BETWEEN date_trunc('year',now())::date AND NULL;

Given that range, the PARTITION_RANGE_MIN and PARTITION_RANGE_MAX columns in the PROJECTIONS table contain the following values:

=> SELECT projection_name partition_range_min, partition_range_min, partition_range_max 
    FROM projections WHERE projection_name ILIKE 'ytd_orders%';
 partition_range_min | partition_range_min | partition_range_max
---------------------+---------------------+---------------------
 ytd_orders_b1       | 2024-01-01          | infinity
 ytd_orders_b0       | 2024-01-01          | infinity
(2 rows)

The following projection defines a range of orders placed since the third quarter of last year:

=> CREATE PROJECTION q3_td AS SELECT * FROM store_orders ORDER BY order_date
    ON PARTITION RANGE BETWEEN add_months(date_trunc('year',now()), -3)::date AND NULL;

Given that definition, the PARTITION_RANGE_MIN_EXPRESSION and PARTITION_RANGE_MAX_EXPRESSION columns in the PROJECTIONS table contain the following values:

=> SELECT projection_name, partition_range_min_expression, partition_range_max_expression 
    FROM projections WHERE projection_name ILIKE 'Q3_td%';
 projection_name |       partition_range_min_expression        | partition_range_max_expression
-----------------+---------------------------------------------+--------------------------------
 q3_td_b1        | add_months(date_trunc('year', now()), (-3)) | NULL
 q3_td_b0        | add_months(date_trunc('year', now()), (-3)) | NULL

See also

1.57 - REGISTERED_MODELS

Lists details about registered machine learning models in the database. The table lists only registered models for which the caller has USAGE privileges.

For a table that lists all models, registered and unregistered, see MODELS.

Example

If a user with the MLSUPERVISOR role queries REGISTERED_MODELS, all registered models are listed:

=> SELECT * FROM REGISTERED_MODELS;
  registered_name | registered_version |    status    |        registered_time        |      model_id     | schema_name |    model_name     |      model_type       |    category
------------------+--------------------+--------------+-------------------------------+-------------------+-------------+-------------------+-----------------------+----------------
 linear_reg_app   |                  2 | UNDER_REVIEW | 2023-01-29 09:09:00.082166-04 | 45035996273714020 | public      | linear_reg_spark1 | PMML_REGRESSION_MODEL | PMML
 linear_reg_app   |                  1 | PRODUCTION   | 2023-01-24 06:19:04.553102-05 | 45035996273850350 | public      | native_linear_reg | LINEAR_REGRESSION     | VERTICA_MODELS
 logistic_reg_app |                  2 | PRODUCTION   | 2023-01-25 08:45:11.279013-02 | 45035996273855542 | public      | log_reg_cgd       | LOGISTIC_REGRESSION   | VERTICA_MODELS
 logistic_reg_app |                  1 | ARCHIVED     | 2023-01-22 04:29:25.990626-02 | 45035996273853740 | public      | log_reg_bfgs      | LOGISTIC_REGRESSION   | VERTICA_MODELS
(4 rows)

See also

• REGISTER_MODEL
• CHANGE_MODEL_STATUS
• Model versioning
• MODEL_STATUS_HISTORY

1.58 - RESOURCE_POOL_DEFAULTS

Returns default parameter settings for built-in and user-defined resource pools. Use ALTER RESOURCE POOL to restore resource pool parameters to their default settings.

For information about valid parameters for built-in resource pools and their default settings, see Built-in resource pools configuration.

To obtain a resource pool's current settings, query system table RESOURCE_POOLS.

Privileges

None

1.59 - RESOURCE_POOLS

Displays settings for built-in and user-defined resource pools. For information about defining resource pools, see CREATE RESOURCE POOL and ALTER RESOURCE POOL.

1.60 - ROLES

Contains the names of all roles the user can access, along with any roles that have been assigned to those roles.

See also

• GRANTS
• HAS_ROLE
• USERS

1.61 - ROUTING_RULES

Lists the routing rules that map incoming IP addresses to a load balancing group.

Examples

=> SELECT * FROM routing_rules;
-[ RECORD 1 ]----+-----------------
name             | internal_clients
source_address   | 192.168.1.0/24
destination_name | group_1
-[ RECORD 2 ]----+-----------------
name             | etl_rule
source_address   | 10.20.100.0/24
destination_name | group_2
-[ RECORD 3 ]----+-----------------
name             | subnet_192
source_address   | 192.0.0.0/8
destination_name | group_all
-[ RECORD 4 ]----+--------------
name             | all_ipv6
source_address   | 0::0/0
destination_name | default_ipv6

See also

1.62 - SCHEDULER_TIME_TABLE

Contains information about scheduled tasks.

This table is local to the active scheduler node (ASN) and is only populated when queried from that node. To get the ASN, use ACTIVE_SCHEDULER_NODE:

=> SELECT active_scheduler_node();
 active_scheduler_node
-----------------------
 initiator
(1 row)

Examples

To view scheduled tasks, execute the following statement on the ASN:

=> SELECT * FROM scheduler_time_table;
  schedule_name |        attached_trigger        | scheduled_execution_time 
----------------+--------------------------------+--------------------------
 daily_1am      | log_user_actions               | 2022-06-01 01:00:00-00
 logging_2022   | refresh_logs                   | 2022-06-01 12:00:00-00

1.63 - SCHEMATA

Provides information about schemas in the database.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

1.64 - SEQUENCES

Displays information about sequences.

Examples

Create a sequence:

=> CREATE SEQUENCE my_seq MAXVALUE 5000 START 150;
CREATE SEQUENCE

Return information about this sequence:

=> SELECT sequence_schema, sequence_name, owner_name, session_cache_count, increment_by, current_value FROM sequences;
 sequence_schema |      sequence_name       | owner_name | session_cache_count | increment_by | current_value
-----------------+--------------------------+------------+---------------------+--------------+--------------
 public          | my_seq                   | dbadmin    |              250000 |            1 |           149
(2 rows)

IDENTITY columns are sequences that are defined in a table's DDL. IDENTITY column values automatically increment as new rows are added. To identify IDENTITY columns and their tables, query the system table COLUMNS:

=> CREATE TABLE employees (employeeID IDENTITY, fname varchar(36), lname varchar(36));
CREATE TABLE
=> SELECT table_name, column_name, is_identity FROM columns WHERE is_identity = 't';
 table_name | column_name | is_identity
------------+-------------+-------------
 employees  | employeeID  | t
(1 row)

Query SEQUENCES to get detailed information about the IDENTITY column sequence in employees:

=> SELECT sequence_schema, sequence_name, identity_table_name, sequence_id FROM sequences
      WHERE identity_table_name ='employees';
 sequence_schema |      sequence_name       | identity_table_name |    sequence_id
-----------------+--------------------------+---------------------+-------------------
 public          | employees_employeeID_seq | employees           | 45035996273848816
(1 row)

Use the vsql command \ds to list all named and IDENTITY column sequences. The following results show the two sequences created previously:

=> \ds
                                                   List of Sequences
 Schema |         Sequence         | CurrentValue | IncrementBy | Minimum |       Maximum       | AllowCycle | Comment
--------+--------------------------+--------------+-------------+---------+---------------------+------------+---------
 public | employees_employeeID_seq |            0 |           1 |       1 | 9223372036854775807 | f          |
 public | my_seq                   |          149 |           1 |       1 |                5000 | f          |
(2 rows)

1.65 - SESSION_SUBSCRIPTIONS

In an Eon Mode database, lists the shard subscriptions for all nodes, and whether the subscriptions are used to resolve queries for the current session. Nodes that will participate in resolving queries in this session have TRUE in their IS_PARTICIPATING column.

Examples

The following example demonstrates listing the subscriptions that are either participating or collaborating in the current session:

=> SELECT node_name, shard_name, is_collaborating, is_participating
        FROM V_CATALOG.SESSION_SUBSCRIPTIONS
        WHERE is_participating = TRUE OR is_collaborating = TRUE
        ORDER BY shard_name, node_name;
      node_name       | shard_name  | is_collaborating | is_participating
----------------------+-------------+------------------+------------------
 v_verticadb_node0004 | replica     | f                | t
 v_verticadb_node0005 | replica     | f                | t
 v_verticadb_node0006 | replica     | t                | f
 v_verticadb_node0007 | replica     | f                | t
 v_verticadb_node0008 | replica     | t                | f
 v_verticadb_node0009 | replica     | t                | f
 v_verticadb_node0007 | segment0001 | f                | t
 v_verticadb_node0008 | segment0001 | t                | f
 v_verticadb_node0005 | segment0002 | f                | t
 v_verticadb_node0009 | segment0002 | t                | f
 v_verticadb_node0004 | segment0003 | f                | t
 v_verticadb_node0006 | segment0003 | t                | f
(12 rows)

1.66 - SHARDS

Lists the shards in your database.

Examples

=> SELECT * FROM SHARDS;
-[ RECORD 1 ]----+------------------
shard_oid        | 45035996273704980
shard_name       | replica
shard_type       | Replica
lower_hash_bound |
upper_hash_bound |
is_replicated    | t
has_objects      | t
...

1.67 - STORAGE_LOCATIONS

Provides information about storage locations, including IDs, labels, and status.

Privileges

Superuser

See also

• DISK_STORAGE
• MEASURE_LOCATION_PERFORMANCE
• SET_LOCATION_PERFORMANCE
• STORAGE_POLICIES
• STORAGE_USAGE
• Storage functions

1.68 - STORED_PROC_TRIGGERS

Contains information about Triggers.

Examples

To view triggers:

=> SELECT * FROM stored_proc_triggers;
  trigger_name  | schema_name |  owner  | procedure_name | procedure_args | enabled
----------------+-------------+---------+----------------+----------------+---------
 raise_trigger  | public      | dbadmin | raiseXY        | 2              | t

1.69 - SUBCLUSTER_RESOURCE_POOL_OVERRIDES

Displays subcluster-level overrides of settings for built-in global resource pools.

1.70 - SUBCLUSTERS

This table lists all of the subclusters defined in the database. It contains an entry for each node in the database listing which subcluster it belongs to. Any subcluster that does not contain a node has a single entry in this table with empty NODE_NAME and NODE_OID columns. This table is only populated if the database is running in Eon Mode.

Examples

=> \x
Expanded display is on.
dbadmin=> SELECT * FROM SUBCLUSTERS;
-[ RECORD 1 ]----+---------------------
subcluster_oid   | 45035996273704978
subcluster_name  | default_subcluster
node_oid         | 45035996273704982
node_name        | v_verticadb_node0001
parent_oid       | 45035996273704976
parent_name      | verticadb
is_default       | t
is_primary       | t
control_set_size | -1
-[ RECORD 2 ]----+---------------------
subcluster_oid   | 45035996273704978
subcluster_name  | default_subcluster
node_oid         | 45035996273840970
node_name        | v_verticadb_node0002
parent_oid       | 45035996273704976
parent_name      | verticadb
is_default       | t
is_primary       | t
control_set_size | -1
-[ RECORD 3 ]----+---------------------
subcluster_oid   | 45035996273704978
subcluster_name  | default_subcluster
node_oid         | 45035996273840974
node_name        | v_verticadb_node0003
parent_oid       | 45035996273704976
parent_name      | verticadb
is_default       | t
is_primary       | t
control_set_size | -1

See also

• CRITICAL_SUBCLUSTERS
• ALTER SUBCLUSTER
• DEMOTE_SUBCLUSTER_TO_SECONDARY
• PROMOTE_SUBCLUSTER_TO_PRIMARY
• SHUTDOWN_SUBCLUSTER

1.71 - SYSTEM_COLUMNS

Provides table column information for SYSTEM_TABLES.

1.72 - SYSTEM_TABLES

Returns a list of all system table names.