This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

V_CATALOG schema

The system tables in this section reside in the 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 - ACCESS_POLICY

Provides information about existing access policies.

Provides information about existing access policies.

Column Name Data Type Description
ACCESS_POLICY_OID INTEGER The unique identifier for the access policy.
TABLE_NAME VARCHAR Name of the table specified in the access policy.
IS_POLICY_ENABLED BOOLEAN Whether the access policy is enabled.
POLICY_TYPE VARCHAR

The type of access policy assigned to the table:

  • Column Policy

  • Row Policy

EXPRESSION VARCHAR The expression used when creating the access policy.
COLUMN_NAME VARCHAR The column to which the access policy is assigned. Row policies apply to all columns in the table.
TRUST_GRANTS BOOLEAN If true, GRANT statements override the access policy when determining whether a user can perform DML operations on the column or row.

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

2 - ALL_RESTORE_POINTS

Provides information about all restore points.

Provides information about existing restore points.

Column Name Data Type Description
RP_ID INTEGER The unique identifier for the restore point.
RP_OID INTEGER The current identifier of the restore point. This can change if the cluster is restarted.
ARCHIVE_OID INTEGER The identifier for the archive.
STATE VARCHAR

Can be one of the following states:

  • SAVING

  • COMPLETE

  • REMOVING

SAVE_TIME VARCHAR The time at which the restore point was saved.
CATALOG_VERSION VARCHAR The catalog version that was saved.
VERTICA_VERSION VARCHAR The version of Vertica for the restore point.

3 - ALL_TABLES

Provides summary information about tables in a Vertica database.

Provides summary information about tables in a Vertica database.

Column Name Data Type Description
SCHEMA_NAME VARCHAR The name of the schema that contains the table.
TABLE_ID INTEGER Catalog-assigned numeric value that uniquely identifies the table.
TABLE_NAME VARCHAR The table name.
TABLE_TYPE VARCHAR

The type of table, which can be one of the following:

  • TABLE

  • SYSTEM TABLE

  • VIEW

  • GLOBAL TEMPORARY

  • LOCAL TEMPORARY

REMARKS VARCHAR A brief comment about the table. You define this field by using the COMMENT ON TABLE and COMMENT ON VIEW commands.

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

4 - AUDIT_MANAGING_USERS_PRIVILEGES

Provides summary information about privileges, creating, modifying, and deleting users, and authentication changes.

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.

Column Name Data Type Description
ISSUED_TIME VARCHAR The time at which the query was executed.
USER_NAME VARCHAR Name of the user who issued the query at the time Vertica recorded the session.
USER_ID INTEGER Numeric representation of the user who ran the query.
HOSTNAME VARCHAR The hostname, IP address, or URL of the database server.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
AUDIT_TYPE VARCHAR

The type of operation for the audit:

  • Query

  • Parameter

  • Table

AUDIT_TAG_NAME VARCHAR The tag name for the specific parameter, query, or table.
REQUEST_TYPE VARCHAR

The type of query request. Examples include, but are not limited to:

  • QUERY

  • DDL

  • LOAD

  • UTILITY

  • TRANSACTION

  • PREPARE

  • EXECUTE

  • SET

  • SHOW

REQUEST_ID INTEGER The ID of the privilege request.
SUBJECT VARCHAR The name of the table or parameter that was queried or the subject of a query.
REQUEST VARCHAR Lists the privilege request.
SUCCESS VARCHAR Indicates whether or not the operation was successful.
CATEGORY VARCHAR The audit parent category, Managing_Users_Privileges.

5 - CA_BUNDLES

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

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

Column Name Data Type Description
OID INTEGER The object identifier.
NAME VARCHAR The name of the CA bundle.
OWNER INTEGER The OID of the owner of the CA bundle.
CERTIFICATES INTEGER The OIDs of the CA certificates inside the 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

6 - CATALOG_SUBSCRIPTION_CHANGES

Lists the changes made to catalog subscriptions.

Lists the changes made to catalog subscriptions.

Column Name Data Type Description
EVENT_TIMESTAMP TIMESTAMP The time a catalog subscription changed.
SESSION_ID VARCHAR A unique numeric ID assigned by the Vertica catalog, which identifies the session for which profiling information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
USER_ID INTEGER A unique numeric ID assigned by the Vertica catalog, which identifies the user.
USER_NAME VARCHAR The user who made changes to the subscriptions.
TRANSACTION_ID INTEGER Identifier for the transaction within the session, if any. If a session is active but no transaction has begun, TRANSACTION_ID returns NULL.
SHARD_NAME VARCHAR The name of the shard.
SHARD_OID INTEGER The OID of the shard.
SUBSCRIBER_NODE_NAME VARCHAR The node name or names subscribed to the shard.
SUBSCRIBER_NODE_OID INTEGER The OID of the subscribing node or nodes.
OLD_STATE VARCHAR The previous state of the node subscription.
NEW_STATE VARCHAR The current state of the node subscription.
WAS_PRIMARY BOOLEAN Defines whether the node was the primary subscriber.
IS_PRIMARY BOOLEAN Defines whether the node is currently the primary subscriber.
CATALOG_VERSION INTEGER The version of the catalog at the time of the subscription change.

7 - CATALOG_SYNC_STATE

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

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

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
SYNC_CATALOG_VERSION INTEGER The version number of the catalog being synchronized.
EARLIEST_CHECKPOINT_VERSION INTEGER The earliest checkpoint that is maintained in communal storage.
SYNC_TRAILING_INTERVAL INTEGER The difference between the global catalog version and the synchronized catalog version for a node.
LAST_SYNC_AT TIMESTAMPTZ The date and time the last time the catalog was synchronized.

8 - CATALOG_TRUNCATION_STATUS

Indicates how up to date the catalog is on communal storage.

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.

Column Name Data Type Description
CURRENT_CATALOG_VERSION INTEGER The version number of the catalog currently on the cluster.
TRUNCATION_CATALOG_VERSION INTEGER The version number as of the last time the catalog was synced on communal storage.

9 - CERTIFICATES

Stores certificates created by CREATE CERTIFICATE.

Stores certificates created by CREATE CERTIFICATE.

Column Name Data Type Description
OID INTEGER The object identifier.
NAME VARCHAR The name of the certificate.
OWNER INTEGER The owner of the object.
SIGNED_BY INTEGER The OID of the signing certificate.
PRIVATE_KEY INTEGER The OID of the certificate's private key.
START_DATE TIMESTAMPTZ When the certificate becomes valid.
EXPIRATION_DATE TIMESTAMPTZ When the certificate expires.
ISSUER VARCHAR The signing CA.
SUBJECT VARCHAR The entity for which the certificate is issued.
SERIAL VARCHAR The certificate's serial number.
x509v3_EXTENSIONS VARCHAR

Lists additional attributes specified during the certificate's creation.

For more information on extensions, see the OpenSSL documentation.

CERTIFICATE_TEXT VARCHAR The contents of the certificate.

Examples

See Generating TLS certificates and keys.

10 - CLIENT_AUTH

Provides information about client authentication methods.

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.

Column Name Data Type Description
AUTH_OID INTEGER Unique identifier for the authentication method.
AUTH_NAME VARCHAR User-given name of the authentication method.
IS_AUTH_ENABLED BOOLEAN Indicates if the authentication method is enabled.
AUTH_HOST_TYPE VARCHAR

The authentication host type, one of the following:

  • LOCAL

  • HOST

  • HOSTSSL

  • HOSTNOSSL

AUTH_HOST_ADDRESS VARCHAR If AUTH_HOST_TYPE is HOST, AUTH_HOST_ADDRESS is the IP address (or address range) of the remote host.
AUTH_METHOD VARCHAR

Authentication method to be used.

Valid values:

  • IDENT

  • GSS

  • HASH

  • LDAP

  • REJECT

  • TLS

  • TRUST

AUTH_PARAMETERS VARCHAR The parameter names and values assigned to the authentication method.
AUTH_PRIORITY INTEGER The priority specified for the authentication. Authentications with higher values are used first.
METHOD_PRIORITY INTEGER

The priority of this authentication based on the AUTH_METHOD.

Vertica only considers METHOD_PRIORITY when deciding between multiple authentication methods of equal AUTH_PRIORITY.

ADDRESS_PRIORITY INTEGER

The priority of this authentication based on the specificity of the AUTH_HOST_ADDRESS, if any. More specific IP addresses (fewer zeros) are used first.

Vertica only considers ADDRESS_PRIORITY when deciding between multiple authentication methods of equal AUTH_PRIORITY and METHOD_PRIORITY.

IS_FALLTHROUGH_ENABLED Boolean Whether authentication fallthrough is enabled.

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

11 - CLIENT_AUTH_PARAMS

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

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

Column Name Data Type Description
AUTH_OID INTEGER A unique identifier for the authentication method.
AUTH_NAME VARCHAR Name that you defined for the authentication method.
AUTH_PARAMETER_NAME VARCHAR

Parameter name required by the authentication method. Some examples are:

  • system_users

  • binddn_prefix

  • host

AUTH_PARAMETER_VALUE VARCHAR Value of the specified parameter.

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)

12 - 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.

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.

Column Name Data Type Description
CLUSTER_POSITION INTEGER

Position of the node in the cluster ring, counting forward from 0.

NODE_ID INTEGER A unique numeric ID assigned by the Vertica catalog that identifies the node.
NODE_NAME VARCHAR The name of the node in the cluster ring. Only permanent nodes participating in database activity appear in the cluster layout. Ephemeral nodes are not shown in the output.
FAULT_GROUP_ID INTEGER

A unique numeric ID assigned by the Vertica catalog that identifies the fault group. This column can only have a value in an Enterprise Mode database.

FAULT_GROUP_NAME VARCHAR The name of the fault group for the node. This column can only have a value in an Enterprise Mode database.
FAULT_GROUP_TIER INTEGER

The node's depth in the fault group tree hierarchy. For example if the node:

  • Is not in a fault group, output is null

  • Is in the top level fault group, output is 0

  • Is in a fault group's child, output is 1

  • Is a fault group's grandchild, output is 2

This column can only have a value in an Enterprise Mode database.

SUBCLUSTER_ID INTEGER Unique identifier for the subcluster. This column only has a value in an Eon Mode database.
SUBCLUSTER_NAME VARCHAR The name of the subcluster containing the node. This column only has a value in an Eon Mode database.

See also

Large cluster

13 - COLUMNS

Provides table column information.

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

Column Name Data Type Description
TABLE_ID INTEGER Catalog-assigned numeric value that uniquely identifies the table.
TABLE_NAMESPACE VARCHAR For Eon Mode databases, name of the namespace that contains the table.
TABLE_SCHEMA VARCHAR

Name of the table's schema.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

TABLE_NAME VARCHAR Name of the table containing the column.
IS_SYSTEM_TABLE BOOLEAN Whether the table is a system table.
COLUMN_ID VARCHAR Catalog-assigned VARCHAR value that uniquely identifies a table column.
COLUMN_NAME VARCHAR Name of the column.
DATA_TYPE VARCHAR

Column data type.

Arrays of primitive types show the name ARRAY[type]. Other complex types show the inline name of the type, which matches the type_name value in the COMPLEX_TYPES table. For example: _ct_45035996273833610.

DATA_TYPE_ID INTEGER Catalog-assigned unique numeric ID of the data type.
DATA_TYPE_LENGTH INTEGER Maximum allowable length of the data type.
CHARACTER_MAXIMUM_LENGTH VARCHAR Maximum allowable length of a VARCHAR column.
NUMERIC_PRECISION INTEGER Number of significant decimal digits for a NUMERIC column.
NUMERIC_SCALE INTEGER Number of fractional digits for a NUMERIC column.
DATETIME_PRECISION INTEGER Declared precision for a TIMESTAMP column, or NULL if no precision was declared.
INTERVAL_PRECISION INTEGER Number of fractional digits retained in the seconds field of an INTERVAL column.
ORDINAL_POSITION INTEGER Column position respective to other columns in the table.
IS_NULLABLE BOOLEAN Whether the column can contain NULL values.
COLUMN_DEFAULT VARCHAR Expression set on a column with the DEFAULT constraint.
COLUMN_SET_USING VARCHAR Expression set on a column with the SET USING constraint.
IS_IDENTITY BOOLEAN Whether the column is an IDENTITY column.

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.

14 - COMMENTS

Returns information about comments associated with objects in the database.

Returns information about comments associated with objects in the database.

Column Name Data Type Description
COMMENT_ID INTEGER Comment's internal ID number
OBJECT_ID INTEGER Internal ID number of the object associated with the comment.
OBJECT_TYPE VARCHAR

Type of object associated with the comment, one of the following:

  • COLUMN

  • CONSTRAINT

  • FUNCTION

  • LIBRARY

  • NODE

  • PROJECTION

  • SCHEMA

  • SEQUENCE

  • TABLE

  • VIEW

OBJECT_SCHEMA VARCHAR Name of the schema containing the object.
OBJECT_NAME VARCHAR Name of the top-level object associated with the comment, such as a table or projection.
CHILD_OBJECT VARCHAR Name of the specific object associated with the comment, such as a table column. If the comment is associated with a top-level object, this value is NULL.
OWNER_ID VARCHAR Internal ID of the object's owner.
OWNER_NAME VARCHAR Object owner's name.
CREATION_TIME TIMESTAMPTZ When the comment was created.
LAST_MODIFIED_TIME TIMESTAMPTZ When the comment was last modified.
COMMENT VARCHAR Comment text.

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

15 - COMPLEX_TYPES

Contains information about inlined 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.

Column Name Data Type Description
TYPE_ID INTEGER A unique identifier for the inlined complex type.
TYPE_KIND VARCHAR The specific kind of complex type: row, array, or map.
TYPE_NAME VARCHAR The generated name of this type. All names begin with _ct_ followed by a number.
FIELD_ID INTEGER A unique identifier for the field.
FIELD_NAME VARCHAR The name of the field, if specified in the table definition, or a generated name beginning with "f".
FIELD_TYPE_NAME VARCHAR The type of the field's value.
FIELD_POSITION INTEGER The field's position in its containing complex type (0-based).
FIELD_LENGTH INTEGER Number of bytes in the field value, or -1 if the value is not a scalar type.
CHARACTER_MAXIMUM_LENGTH INTEGER Maximum allowable length of the column.
NUMERIC_PRECISION INTEGER Number of significant decimal digits.
NUMERIC_SCALE INTEGER Number of fractional digits.
DATETIME_PRECISION INTEGER For TIMESTAMP data type, returns the declared precision; returns NULL if no precision was declared.
INTERVAL_PRECISION INTEGER Number of fractional digits retained in the seconds field.

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)

16 - CONSTRAINT_COLUMNS

Records information about table column constraints.

Records information about table column constraints.

Column Name Data Type Description
CONSTRAINT_ID INTEGER Unique numeric ID assigned by the Vertica catalog that identifies the constraint.
TABLE_NAMESPACE VARCHAR For Eon Mode databases, name of the namespace that contains the table.
TABLE_SCHEMA VARCHAR

Name of the schema that contains this table.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

TABLE_ID INTEGER Unique numeric ID assigned by the Vertica catalog that identifies the table.
TABLE_NAME VARCHAR Name of the table in which the column resides.
COLUMN_NAME VARCHAR Name of the column that is constrained. For check constraints, if more than one column is referenced, each appears as a separate row.
CONSTRAINT_NAME VARCHAR Constraint name for which information is listed.
CONSTRAINT_TYPE CHAR

The constraint type, one of the following:

  • c: check

  • f: foreign

  • n: not null

  • p: primary

  • u: unique

IS_ENABLED BOOLEAN Indicates whether a constraint for a primary key, unique key, or check constraint is currently enabled.
REFERENCE_TABLE_ID INTEGER Catalog-assigned integer value that uniquely identifies the referenced table
REFERENCE_TABLE_NAMESPACE VARCHAR For Eon Mode databases, references the TABLE_NAMESPACE column in the PRIMARY_KEYS table.
REFERENCE_TABLE_SCHEMA VARCHAR

Schema name for which information is listed.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

REFERENCE_TABLE_NAME VARCHAR References the TABLE_NAME column in the PRIMARY_KEYS table.
REFERENCE_COLUMN_NAME VARCHAR References the COLUMN_NAME column in the PRIMARY_KEYS table.

Privileges

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

17 - CRYPTOGRAPHIC_KEYS

Stores private keys created by CREATE KEY.

Stores private keys created by CREATE KEY.

Column Name Data Type Description
OID INTEGER The object identifier.
NAME VARCHAR Name of the key.
OWNER INTEGER The owner of the object.
TYPE INTEGER

The type of key.

  • 0 = AES

  • 1 = RSA

LENGTH INTEGER The size of the key in bits.
HAS_PASSWORD BOOLEAN Whether the key has a password.
KEY VARCHAR The private key.

Examples

See Generating TLS certificates and keys.

18 - DATABASES

Provides information about the databases in this Vertica installation.

Provides information about the databases in this Vertica installation.

Column Name Data Type Description
DATABASE_ID INTEGER The database's internal ID number
DATABASE_NAME VARCHAR The database's name
OWNER_ID INTEGER The database owner's ID
OWNER_NAME INTEGER The database owner's name
START_TIME TIMESTAMPTZ The date and time the database last started
COMPLIANCE_MESSAGE VARCHAR Message describing the current state of the database's license compliance
EXPORT_SUBNET VARCHAR

Can be either of the following:

  • The subnet (on the public network) used by the database for import/export

  • The public address of the subnet that the Vertica native load balancing uses for load balancing

LOAD_BALANCE_POLICY VARCHAR The current native connection load balance policy, which controls whether client connection requests are redirected to other hosts in the database. See About native connection load balancing.
BACKEND_ADDRESS_FAMILY VARCHAR The Internet Protocol (IP) addressing standard used for internode communications. This value is either ipv4 or ipv6.
BRANCH_NAME VARCHAR This column is no longer used.

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            |

19 - DIRECTED_QUERIES

Returns information about directed queries.

Returns information about directed queries.

Column Name Data Type Description
QUERY_NAME VARCHAR

Directed query's unique identifier, used by statements such as ACTIVATE DIRECTED QUERY. How this identifier is set depends on how it was created:

  • Direct call to CREATE DIRECTED QUERY: Set by the user-supplied query-name argument.

  • Call to SAVE_PLANS: Concatenated from the following strings:

    save_plans_query-label_query-number_save-plans-version
    

where:

  • query-label is a LABEL hint embedded in the input query associated with this directed query. If theinput query contains no label, then this string is set to nolabel.
  • query-number is an integer in a continuous sequence between 0 and budget-query, which uniquely identifies this directed query from others in the same SAVE_PLANS-generated set.
  • [save-plans-version](/en/sql-reference/system-tables/v-catalog-schema/directed-queries/#SAVE_PLANS_VERSION) identifies the set of directed queries to which this directed query belongs.
IS_ACTIVE BOOLEAN Whether the directed query is active
VERTICA_VERSION VARCHAR Vertica version installed when this directed query was created.
COMMENT VARCHAR User-supplied or optimizer-generated comment on a directed query, up to 128 characters.
SAVE_PLANS_VERSION INTEGER

One of the following:

  • 0: Generated by a direct call to CREATE DIRECTED QUERY.
  • >0: Identifies a set of directed queries that were generated by the same call to SAVE_PLANS. All directed queries of the set share the same SAVE_PLANS_VERSION integer, which increments by 1 the previous highest SAVE_PLANS_VERSION setting. Use this identifier to activate, deactivate, and drop a set of directed queries.
USERNAME VARCHAR User that created this directed query.
CREATION_DATE VARCHAR When the directed query was created.
SINCE_DATE VARCHAR Populated by SAVE_PLANS-generated directed queries, the earliest timestamp of input queries eligible to be saved as directed queries.
INPUT_QUERY VARCHAR Input query associated with this directed query. Multiple directed queries can map to the same input query.
ANNOTATED_QUERY VARCHAR Query with embedded hints that was paired with the input query of this directed query, where the hints encapsulated the query plan saved with CREATE DIRECTED QUERY.
DIGEST INTEGER Hash of saved query plan data, used by the optimizer to map identical input queries to the same active directed query.

Privileges

Superuser

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:

20 - DUAL

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

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.

21 - ELASTIC_CLUSTER

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

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

Column Name Data Type Description
SCALING_FACTOR INTEGER This value is only meaningful when you enable local segments. SCALING_FACTOR influences the number of local segments on each node. Initially—before a rebalance runs—there are scaling_factor number of local segments per node. A large SCALING_FACTOR is good for rebalancing a potentially wide range of cluster configurations quickly. However, too large a value might lead to ROS pushback, particularly in a database with a table with a large number of partitions. See SET_SCALING_FACTOR for more details.
MAXIMUM_SKEW_PERCENT INTEGER This value is only meaningful when you enable local segments. MAXIMUM_SKEW_PERCENT is the maximum amount of skew a rebalance operation tolerates, which preferentially redistributes local segments; however, if after doing so the segment ranges of any two nodes differs by more than this amount, rebalance will separate and distribute storage to even the distribution.
SEGMENT_LAYOUT VARCHAR Current, offset=0, segment layout. New segmented projections will be created with this layout, with segments rotated by the corresponding offset. Existing segmented projections will be rebalanced into an offset of this layout.
LOCAL_SEGMENT_LAYOUT VARCHAR Similar to SEGMENT_LAYOUT but includes details that indicate the number of local segments, their relative size and node assignment.
VERSION INTEGER Number that gets incremented each time the cluster topology changes (nodes added, marked ephemeral, marked permanent, etc). Useful for monitoring active and past rebalance operations.
IS_ENABLED BOOLEAN True if Elastic Cluster is enabled, otherwise false.
IS_LOCAL_SEGMENT_ENABLED BOOLEAN True if local segments are enabled, otherwise false.
IS_REBALANCE_RUNNING BOOLEAN True if rebalance is currently running, otherwise false.

Privileges

Superuser

See also

22 - EPOCHS

For the most recently closed epochs, lists the date and time of the close and the corresponding epoch number of the closed epoch.

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.

Column Name Data Type Description
EPOCH_CLOSE_TIME DATETIME The date and time that the epoch closed.
EPOCH_NUMBER INTEGER The epoch number of the closed epoch.

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

23 - FAULT_GROUPS

View the fault groups and their hierarchy in the cluster.

View the fault groups and their hierarchy in the cluster.

Column Name Data Type Description
MEMBER_ID INTEGER A unique numeric ID assigned by the Vertica catalog that identifies the fault group.
MEMBER_TYPE VARCHAR The type of fault group. Values can be either NODE or FAULT GROUP.
MEMBER_NAME VARCHAR Name associated with this fault group. Values will be the node name or the fault group name.
PARENT_ID INTEGER

A unique numeric ID assigned by the Vertica catalog that identifies the parent fault group. The parent fault group can contain:

  • Nodes

  • Other fault groups

  • Nodes and other fault groups

PARENT_TYPE VARCHAR

The type of parent fault group, where the default/root parent is the DATABASE object. Can be one of the following objects:

  • FAULT GROUP

  • DATABASE

PARENT_NAME VARCHAR The name of the fault group that contains nodes or other fault groups or both nodes and fault groups.
IS_AUTOMATICALLY_GENERATED BOOLEAN If true, denotes whether Vertica Analytic Database created fault groups for you to manage the fault tolerance of control nodes in large cluster configurations. If false, denotes that you created fault groups manually. See Fault Groups for more information

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

24 - FOREIGN_KEYS

Provides foreign key information.

Provides foreign key information.

Column Name Data Type Description
CONSTRAINT_ID INTEGER A unique numeric ID assigned by the Vertica catalog, which identifies the constraint.
CONSTRAINT_NAME VARCHAR The constraint name for which information is listed.
COLUMN_NAME VARCHAR The name of the column that is constrained.
ORDINAL_POSITION VARCHAR The position of the column within the key. The numbering of columns starts at 1.
TABLE_NAME VARCHAR The table name for which information is listed.
REFERENCE_TABLE_NAME VARCHAR References the TABLE_NAME column in the PRIMARY_KEYS table.
CONSTRAINT_TYPE VARCHAR The constraint type, f, for foreign key.
REFERENCE_COLUMN_NAME VARCHAR References the COLUMN_NAME column in the PRIMARY_KEY table.
TABLE_NAMESPACE_ID VARCHAR Unique numeric identifier for the table namespace.
TABLE_NAMESPACE VARCHAR For Eon Mode databases, namespace that contains the table for which information is listed.
TABLE_SCHEMA VARCHAR

The schema name for which information is listed.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

REFERENCE_TABLE_NAMESPACE_ID VARCHAR For Eon Mode databases, references the TABLE_NAMESPACE_ID column in the PRIMARY_KEYS table.
REFERENCE_TABLE_NAMESPACE VARCHAR For Eon Mode databases, references the TABLE_NAMESPACE column in the PRIMARY_KEYS table.
REFERENCE_TABLE_SCHEMA VARCHAR

References the TABLE_SCHEMA column in the PRIMARY_KEYS table.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

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)

25 - GRANTS

Returns information about privileges that are explicitly granted on database objects.

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

Column Name Data Type Description
GRANTEE VARCHAR The user being granted permission.
GRANTEE_ID INTEGER Catalog-assigned integer value that uniquely identifies theuser granted permissions.
GRANT_ID INTEGER Catalog-assigned integer value that uniquely identifies thegrant operation.
GRANTOR VARCHAR The user granting the permission.
GRANTOR_ID INTEGER Catalog-assigned integer value that uniquely identifies theuser who performed the grant operation.
OBJECT_ID INTEGER Catalog-assigned integer value that uniquely identifies theobject granted.
OBJECT_NAME VARCHAR The name of the object that is being granted privileges. Note that for schema privileges, the schema name appears in the OBJECT_NAME column instead of the OBJECT_SCHEMA column.
OBJECT_NAMESPACE VARCHAR For Eon Mode databases, namespace of the object that is being granted privileges.
OBJECT_SCHEMA VARCHAR

Schema of the object that is being granted privileges.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

OBJECT_TYPE VARCHAR The object type on which the grant was applied—for example, ROLE, SCHEMA, DATABASE, RESOURCEPOOL.
PRIVILEGES_DESCRIPTION VARCHAR Lists the privileges granted on an object—for example INSERT, SELECT. An asterisk in PRIVILEGES_DESCRIPTION output shows that the privilege grant included WITH GRANT OPTION.

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

26 - HCATALOG_COLUMNS

Describes the columns of all tables available through the HCatalog Connector.

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.

Column Name Data Type Description
TABLE_SCHEMA VARCHAR(128) The name of the Vertica Analytic Database schema that contains the table containing this column
HCATALOG_SCHEMA VARCHAR(128) The name of the Hive schema or database that contains the table containing this column
TABLE_NAME VARCHAR(128) The name of the table that contains the column
IS_PARTITION_COLUMN BOOLEAN Whether the table is partitioned on this column
COLUMN_NAME VARCHAR(128) The name of the column
HCATALOG_DATA_TYPE VARCHAR(128) The Hive data type of this column
DATA_TYPE VARCHAR(128) The Vertica Analytic Database data type of this column
DATA_TYPE_ID INTEGER Numeric ID of the column's Vertica Analytic Database data type
DATA_TYPE_LENGTH INTEGER The number of bytes used to store this data type
CHARACTER_MAXIMUM_LENGTH INTEGER For string data types, the maximum number of characters it can hold
NUMERIC_PRECISION INTEGER For numeric types, the precision of the values in the column
NUMERIC_SCALE INTEGER For numeric data types, the scale of the values in the column
DATETIME_PRECISION INTEGER For datetime data types, the precision of the values in the column
INTERVAL_PRECISION INTEGER For interval data types, the precision of the values in the column
ORDINAL_POSITION INTEGER The position of the column within the table

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

27 - HCATALOG_SCHEMATA

Lists all of the schemas defined using the HCatalog Connector.

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.

Column Name Data Type Description
SCHEMA_ID INTEGER The Vertica Analytic Database ID number for the schema
SCHEMA_NAME VARCHAR(128) The name of the schema defined in the Vertica Analytic Database catalog
SCHEMA_OWNER_ID INTEGER The ID number of the user who owns the Vertica Analytic Database schema
SCHEMA_OWNER VARCHAR(128) The username of the Vertica Analytic Database schema's owner
CREATE_TIME TIMESTAMPTZ The date and time the schema as created
HOSTNAME VARCHAR(128) The host name or IP address of the database server that holds the Hive metadata
PORT INTEGER The port number on which the metastore database listens for connections
HIVESERVER2_HOSTNAME VARCHAR(128) The host name or IP address of the HiveServer2 server for the Hive database
WEBSERVICE_HOSTNAME VARCHAR(128) The host name or IP address of the WebHCat server for the Hive database, if used
WEBSERVICE_PORT INTEGER The port number on which the WebHCat server listens for connections
WEBHDFS_ADDRESS VARCHAR (128) The host and port ("host:port") for the WebHDFS service, used for reading ORC and Parquet files
HCATALOG_SCHEMA_NAME VARCHAR(128) The name of the schema or database in Hive to which the Vertica Analytic Database schema is mapped/
HCATALOG_USER_NAME VARCHAR(128) The username the HCatalog Connector uses to authenticate itself to the Hive database.
HCATALOG_CONNECTION_TIMEOUT INTEGER The number of seconds the HCatalog Connector waits for a successful connection to the HiveServer or WebHCat server. A value of 0 means wait indefinitely.
HCATALOG_SLOW_TRANSFER_LIMIT INTEGER The lowest data transfer rate (in bytes per second) from the HiveServer2 or WebHCat server that the HCatalog Connector accepts.
HCATALOG_SLOW_TRANSFER_TIME INTEGER The number of seconds the HCatalog Connector waits before enforcing the data transfer rate lower limit by breaking the connection and terminating the query.
SSL_CONFIG VARCHAR(128) The path of the Hadoop ssl-client.xml configuration file, if using HiveServer2 with SSL wire encryption.
CUSTOM_PARTITIONS BOOLEAN Whether the Hive schema uses custom partition locations.

Privileges

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

See also

28 - HCATALOG_TABLE_LIST

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

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

Column Name Data Type Description
TABLE_SCHEMA_ID INTEGER Internal ID number for the schema containing the table
TABLE_SCHEMA VARCHAR(128) Name of the Vertica Analytic Database schema through which the table is available
HCATALOG_SCHEMA VARCHAR(128) Name of the Hive schema or database containing the table
TABLE_NAME VARCHAR(128) The name of the table
HCATALOG_USER_NAME VARCHAR(128) Name of Hive user used to access the table

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

29 - HCATALOG_TABLES

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

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

Column Name Data Type Description
TABLE_SCHEMA_ID INTEGER ID number of the schema
TABLE_SCHEMA VARCHAR(128) The name of the Vertica Analytic Database schema through which the table is available
HCATALOG_SCHEMA VARCHAR(128) The name of the Hive schema or database that contains the table
TABLE_NAME VARCHAR(128) The name of the table
HCATALOG_USER_NAME VARCHAR(128) The name of the HCatalog user whose credentials are used to access the table's data
MIN_FILE_SIZE_BYTES INTEGER The file size of the table's smallest data file, if using WebHCat; null if using HiveServer2
TOTAL_NUMBER_FILES INTEGER The number of files used to store this table's data in HDFS
LOCATION VARCHAR(8192) The URI for the directory containing this table's data, normally an HDFS URI
LAST_UPDATE_TIME TIMESTAMPTZ The last time data in this table was updated, if using WebHCat; null if using HiveServer2
OUTPUT_FORMAT VARCHAR(128) The Hive SerDe class used to output data from this table
LAST_ACCESS_TIME TIMESTAMPTZ The last time data in this table was accessed, if using WebHCat; null if using HiveServer2
MAX_FILE_SIZE_BYTES INTEGER The size of the largest data file for this table, if using WebHCat; null if using HiveServer2
IS_PARTITIONED BOOLEAN Whether this table is partitioned
PARTITION_EXPRESSION VARCHAR(128) The expression used to partition this table
TABLE_OWNER VARCHAR(128) The Hive user that owns this table in the Hive database, if using WebHCat; null if using HiveServer2
INPUT_FORMAT VARCHAR(128) The SerDe class used to read the data from this table
TOTAL_FILE_SIZE_BYTES INTEGER Total number of bytes used by all of this table's data files
HCATALOG_GROUP VARCHAR(128) The permission group assigned to this table, if using WebHCat; null if using HiveServer2
PERMISSION VARCHAR(128) The Unix file permissions for this group, as shown by the ls -l command, if using WebHCat; null if using HiveServer2

Privileges

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

See also

30 - ICEBERG_COLUMNS

Provides column information for Iceberg external tables.

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

Column Name Data Type Description
TABLE_ID INTEGER Catalog-assigned numeric value that uniquely identifies the table.
TABLE_SCHEMA VARCHAR

Name of the table's schema.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

TABLE_NAME VARCHAR Name of the table containing the column.
COLUMN_ID VARCHAR Catalog-assigned VARCHAR value that uniquely identifies a table column.
COLUMN_NAME VARCHAR Name of the column.
DATA_TYPE VARCHAR Column data type.
DATA_TYPE_ID INTEGER Catalog-assigned unique numeric ID of the data type.
DATA_TYPE_LENGTH INTEGER Maximum allowable length of the data type.
CHARACTER_MAXIMUM_LENGTH VARCHAR Maximum allowable length of a VARCHAR column.
NUMERIC_PRECISION INTEGER Number of significant decimal digits for a NUMERIC column.
NUMERIC_SCALE INTEGER Number of fractional digits for a NUMERIC column.
DATETIME_PRECISION INTEGER Declared precision for a TIMESTAMP column, or NULL if no precision was declared.
INTERVAL_PRECISION INTEGER Number of fractional digits retained in the seconds field of an INTERVAL column.
IS_NULLABLE BOOLEAN Whether the column can contain NULL values.
WRITE_DEFAULT VARCHAR Field value for any records written after the field was added to the schema, if the writer does not supply the field’s value.
INITIAL_DEFAULT VARCHAR Field value for all records that were written before the field was added to the schema.

31 - INHERITED_PRIVILEGES

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

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.

Column Name Data Type Description
OBJECT_ID INTEGER Catalog-assigned integer value that uniquely identifies theobject inheriting the privileges.
SCHEMA_ID INTEGER Catalog-assigned integer value that uniquely identifies theparent schema.
OBJECT_NAMESPACE VARCHAR For Eon Mode databases, namespace of the table or view.
OBJECT_SCHEMA VARCHAR

Name of the parent schema of a table or view.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

OBJECT_NAME VARCHAR Name of the table or view.
OBJECT_TYPE VARCHAR

The object type, one of the following:

  • Table

  • View

  • Model

PRIVILEGES_DESCRIPTION VARCHAR Lists the privileges inherited on an object. An asterisk (*) appended to a privilege indicates that the user can grant the privilege to other users by granting the privilege on the parent schema.
PRINCIPAL VARCHAR Name of the role or user inheriting the privileges in the row.
PRINCIPAL_ID INTEGER Catalog-assigned integer value that uniquely identifies theuser inheriting the privileges.
GRANTOR VARCHAR User that granted the privileges on the parent schema to the principal.
GRANTOR_ID INTEGER Catalog-assigned integer value that uniquely identifies theuser who performed the grant operation.
GRANT_ID INTEGER Catalog-assigned integer value that uniquely identifies thegrant operation.

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

32 - INHERITING_OBJECTS

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

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.

Column Name Data Type Description
OBJECT_ID INTEGER Catalog-assigned integer value that uniquely identifies theobject inheriting the privileges.
SCHEMA_ID INTEGER Catalog-assigned integer value that uniquely identifies theparent schema.
OBJECT_SCHEMA VARCHAR Name of the parent schema.
OBJECT_NAME VARCHAR Name of the object.
OBJECT_TYPE VARCHAR

The object type, one of the following:

  • Table

  • View

  • Model

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

33 - KEYWORDS

Identifies Vertica reserved and non-reserved keywords.

Identifies Vertica reserved and non-reserved keywords.

Column Name Data Type Description
KEYWORD VARCHAR Vertica-reserved or non-reserved keyword.
RESERVED VARCHAR

Indicates whether a keyword is reserved or non-reserved:

  • R: reserved

  • N: non-reserved

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

Keywords

34 - 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.

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

Column Name Data Type Description
NODE_NAME VARCHAR The name of the node in the cluster.
SPREAD_HOST_NAME VARCHAR The host name of the control node (the host that manages control message responsibilities)
CONTROL_NODE_NAME VARCHAR The name of the control node

See also

Large Cluster

35 - LICENSE_AUDITS

Lists the results of Vertica's license automatic compliance audits.

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

Column Name Data Type Description
DATABASE_SIZE_BYTES INTEGER The estimated raw data size of the database
LICENSE_SIZE_BYTES INTEGER The licensed data allowance
USAGE_PERCENT FLOAT Percentage of the licensed allowance used
AUDIT_START_TIMESTAMP TIMESTAMPTZ When the audit started
AUDIT_END_TIMESTAMP TIMESTAMPTZ When the audit finished
CONFIDENCE_LEVEL_PERCENT FLOAT The confidence level of the size estimate
ERROR_TOLERANCE_PERCENT FLOAT The error tolerance used for the size estimate
USED_SAMPLING BOOLEAN Whether data was randomly sampled (if false, all of the data was analyzed)
CONFIDENCE_INTERVAL_LOWER_BOUND_BYTES INTEGER The lower bound of the data size estimate within the confidence level
CONFIDENCE_INTERVAL_UPPER_BOUND_BYTES INTEGER The upper bound of the data size estimate within the confidence level
SAMPLE_COUNT INTEGER The number of data samples used to generate the estimate
CELL_COUNT INTEGER The number of cells in the database
AUDITED_DATA VARCHAR The type of data audited, which includes regular (non-flex), flex, external, and total data

36 - LICENSES

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

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

Column Name Data Type Description
LICENSE_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the license.
NAME VARCHAR The license’s name. (The license name in this column could be represented by a long license key.)
LICENSEE VARCHAR The entity to which the product is licensed.
START_DATE VARCHAR The start date for which the license is valid.
END_DATE VARCHAR The end date until which the license is valid (or "Perpetual" if the license has no expiration).
LICENSETYPE VARCHAR The type of the license (for example, Premium Edition).
PARENT VARCHAR The parent license (field is blank if there is no parent).
SIZE VARCHAR The size limit for data on the license.
IS_SIZE_LIMIT_ENFORCED BOOLEAN Indicates whether the license includes enforcement of data and node limits, where t is true and f is false.
NODE_RESTRICTION VARCHAR The node limit the license imposes.
CONFIGURED_ID INTEGER A long license key.

37 - LOAD_BALANCE_GROUPS

Lists the objects contained by all 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.'

Column Name Data Type Description
NAME VARCHAR The name of the load balance group
POLICY VARCHAR

The policy that sets how the group chooses the node for a connection. Contains one of the following:

  • ROUNDROBIN

  • RANDOM

  • NONE

FILTER VARCHAR The IP address range in CIDR format to select the members of a fault group that are included in the load balance group. This column only has a value if the TYPE column is 'Fault Group' or 'Subcluster.'
TYPE VARCHAR

The type of object contained in the load balance group. Contains one of:

  • Fault Group

  • Subcluster

  • Network Address Group

  • Empty Group

OBJECT_NAME VARCHAR The name of the fault group or network address included in the load balance group. This column is NULL if the group contains no objects.

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

38 - LOG_PARAMS

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

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

Column Name Data Type Description
ISSUED_TIME VARCHAR The time at which the query was executed.
USER_NAME VARCHAR Name of the user who issued the query at the time Vertica recorded the session.
USER_ID INTEGER Numeric representation of the user who ran the query.
HOSTNAME VARCHAR The hostname, IP address, or URL of the database server.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
AUDIT_TYPE VARCHAR The type of operation for the audit, in this case, Parameter.
AUDIT_TAG_NAME VARCHAR The tag for the specific parameter.
REQUEST_TYPE VARCHAR The type of query request.
REQUEST_ID INTEGER Unique identifier of the query request in the user session.
SUBJECT VARCHAR The new value of the parameter.
REQUEST VARCHAR Lists the query request.
SUCCESS VARCHAR Indicates whether or not the operation was successful.
CATEGORY VARCHAR The audit parent category, such as Authentication.

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)

39 - LOG_QUERIES

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

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

Column Name Data Type Description
ISSUED_TIME VARCHAR The time at which the query was executed.
USER_NAME VARCHAR Name of the user who issued the query at the time Vertica recorded the session.
USER_ID INTEGER Numeric representation of the user who ran the query.
HOSTNAME VARCHAR The hostname, IP address, or URL of the database server.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
AUDIT_TYPE VARCHAR The type of operation for the audit, in this case, Query.
AUDIT_TAG_NAME VARCHAR The tag for the specific query.
REQUEST_TYPE VARCHAR

The type of query request. Examples include, but are not limited to:

  • QUERY

  • DDL

  • LOAD

  • UTILITY

  • TRANSACTION

  • PREPARE

  • EXECUTE

  • SET

  • SHOW

REQUEST_ID INTEGER The ID of the query request.
SUBJECT VARCHAR The subject of the query.
REQUEST VARCHAR Lists the query request.
SUCCESS VARCHAR Indicates whether or not the operation was successful.
CATEGORY VARCHAR The audit parent category, such as Managing_Users_Privileges.

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)

40 - LOG_TABLES

Provides summary information about queries on system tables.

Provides summary information about queries on system tables.

Column Name Data Type Description
ISSUED_TIME VARCHAR Time of query execution.
USER_NAME VARCHAR Name of user who issued the query at the time Vertica recorded the session.
USER_ID INTEGER Numeric representation of the user who ran the query.
HOSTNAME VARCHAR The hostname, IP address, or URL of the database server.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
AUDIT_TYPE VARCHAR The type of operation for the audit, in this case, Table.
AUDIT_TAG_NAME VARCHAR The tag for the specific table.
REQUEST_TYPE VARCHAR The type of query request. In this case, QUERY.
REQUEST_ID INTEGER Unique identifier of the query request in the user session.
SUBJECT VARCHAR The name of the table that was queried.
REQUEST VARCHAR Lists the query request.
SUCCESS VARCHAR Indicates whether or not the operation was successful.
CATEGORY VARCHAR The audit parent category—for example, Views, Security, and Managing_Users_Privileges.

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

41 - MATERIALIZE_FLEXTABLE_COLUMNS_RESULTS

Contains the results of running the MATERIALIZE_FLEXTABLE_COLUMNS function.

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.

Column Name Data Type Description
TABLE_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the table.
TABLE_SCHEMA VARCHAR The schema name for which information is listed.
TABLE_NAME VARCHAR The table name for which information is listed.
CREATION_TIME VARCHAR Timestamp when the key was materialized.
KEY_NAME VARCHAR Name of the key from the VMap column that was materialized.
STATUS VARCHAR

Status of the materialized column, one of the following:

  • ADDED

  • EXISTS

  • ERROR

MESSAGE BOOLEAN

Message associated with the status in the previous column, one of the following:

  • Added successfully

  • Column of same name already exists in table definition

  • Add operation failed

  • No data type guess provided to add column

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

42 - MODELS

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

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

Column Name Data Type Description
MODEL_ID INTEGER The model's internal ID.
MODEL_NAME VARCHAR(128) The name of the model.
SCHEMA_ID INTEGER The schema's internal ID.
SCHEMA_NAME VARCHAR(128) The name of the schema.
OWNER_ID INTEGER The model owner's ID.
OWNER_NAME VARCHAR(128) The user who created the model.
CATEGORY VARCHAR(128) The type of model. By default, models created in Vertica are assigned to the Vertica_Models category.
MODEL_TYPE VARCHAR(128) The type of algorithm used to create the model.
IS_COMPLETE VARCHAR(128) Denotes whether the model is complete and ready for use in machine learning functions. This field is usually false when the model is being trained. Once the training is complete, the field is set to true.
CREATE_TIME TIMESTAMPTZ The time the model was created.
SIZE INTEGER The size of the model in bytes.
IS_INHERIT_PRIVILEGES BOOLEAN Whether the model inherits the privileges of its parent schema.

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

43 - NAMESPACES

Lists details about the namespaces in an Eon Mode database.

Lists details about the namespaces in an Eon Mode database.

Column Name Data Type Description
NAMESPACE_OID INTEGER Catalog-assigned numeric value that uniquely identifies the namesapce.
NAMESPACE_NAME VARCHAR Name of the namespace
IS_DEFAULT BOOLEAN Whether the namespace is the default namespace of the database.
DEFAULT_SHARD_COUNT VARCHAR Shard count of the namespace.

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)

44 - NETWORK_ADDRESSES

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

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

Column Name Data Type Description
NAME VARCHAR The name of the network address.
NODE VARCHAR The name of the node that owns the network address.
ADDRESS VARCHAR The network address's IP address. This address can be either in IPv4 or IPv6 format.
PORT INT The network address's port number.
ADDRESS_FAMILY VARCHAR The format of the network address's IP address. This values is either 'ipv4' or 'ipv6'.
IS_ENABLED BOOLEAN Whether the network address is enabled. You can disable network addresses to prevent their use. If the address is disabled, the value in this column is False.
IS_AUTO_DETECTED BOOLEAN Whether Vertica created the network address automatically.

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

45 - NODE_SUBSCRIPTION_CHANGE_PHASES

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

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

Column Name Data Type Description
node_name VARCHAR Name of the node
subscription_change_type VARCHAR The change being made to the subscription
session_id INTEGER ID of the session in which the change was initiated
transaction_id INTEGER ID of the transaction in which the change was initiated
user_id INTEGER ID of user that initiated the change
user_name VARCHAR Name of user that initiated the change
subscription_oid INTEGER Session object ID
subscriber_node_oid INTEGER Object ID of node that requested the subscription
subscriber_node_name VARCHAR Name of the node that requested the subscription
shard_oid INTEGER Object ID of the shard to which the node is subscribed
shard_name VARCHAR Name of the shard to which the node is subscribed
min_time TIMESTAMPTZ Start time of the subscription change
max_time TIMESTAMPTZ Completion time of the subscription change
source_node_oid INTEGER Object ID of the node from which catalog objects were fetched
source_node_name VARCHAR Name of the node from which catalog objects were fetched
num_objs_affected INTEGER Number of catalog objects affected by the subscription change
action VARCHAR Description of the action taken
new_content_size INTEGER Total size of the catalog objects that were fetched for the subscription change
phase_limit_reached BOOLEAN Reached maximum number of retries?
START_TIME TIMESTAMPTZ When the subscription change started
END_TIME TIMESTAMPTZ When the subscription change was finished
retried BOOLEAN Retry of subscription phase?
phase_result VARCHAR

Outcome of the subscription change, one of the following:

  • Success

  • Failure

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

46 - NODE_SUBSCRIPTIONS

Lists information about database node subscriptions to shards.

Eon Mode only

Lists information about database node subscriptions to shards.

Column Name Data Type Description
SUBSCRIPTION_OID INTEGER Subscription OID
NODE_OID INTEGER Subscribed node OID
NODE_NAME VARCHAR Name of the node
SHARD_OID INTEGER OID of the shard to which the node is subscribed
SHARD_NAME VARCHAR Name of the shard to which the node is subscribed
SUBSCRIPTION_STATE VARCHAR Node's current subscription state
FROM _VERSION INTEGER Deprecated
IS_PRIMARY BOOLEAN Specifies whether the node is currently the primary subscriber.
IS_RESUBSCRIBING BOOLEAN

Indicates whether a subscription is resubscribing to a node:

  • t (true): A subscription is resubscribing, only applies to PENDING subscriptions created during the cluster or node startup.

  • f (false): A subscription is not resubscribing, applies to PENDING subscriptions created with REBALANCE_SHARDS that transitioned to an ACTIVE state.

CREATOR_TID INTEGER ID of transaction that created this subscription
SUBSCRIBED_TO_METADATA_AT INTEGER Deprecated
IS_PARTICIPATING_PRIMARY BOOLEAN Whether this node is the participating primary subscriber for the shard. If true, the node listed in NODE_NAME is the only one that reads from and writes to communal storage for this shard in the subcluster. Other nodes in the subcluster that subscribe to the same shard receive data from this node via peer-to-peer transfers.

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)

47 - NODES

Lists details about the nodes in the database.

Lists details about the nodes in the database.

Column Name Data Type Description
NODE_NAME VARCHAR(128) The name of the node.
NODE_ID INTEGER Catalog-assigned integer value that uniquely identifies the node.
NODE_STATE VARCHAR(128)

The node's current state, one of the following:

  • UP

  • DOWN

  • READY

  • UNSAFE

  • SHUTDOWN

  • SHUTDOWN_ERROR

  • RECOVERING

  • RECOVER_ERROR

  • RECOVERED

  • INITIALIZING

  • STANDBY

  • NEEDS_CATCHUP

IS_PRIMARY BOOLEAN Whether the node is a primary or secondary node. Primary nodes are the only ones Vertica considers when determining the K-Safety of an Eon Mode database. The node inherits this property from the subcluster that contains it.
IS_READONLY BOOLEAN Whether the node is in read-only mode or not. This column is TRUE if the Eon Mode database is read-only due to the loss of quorum or primary shard coverage. See Database Read-Only Mode.
NODE_ADDRESS VARCHAR(80) The host address of the node.
NODE_ADDRESS_FAMILY VARCHAR(10) The IP Version of the node_address. For example, ipv4.
EXPORT_ADDRESS VARCHAR(8192) The IP address of the node (on the public network) used for import/export operations and native load-balancing.
EXPORT_ADDRESS_FAMILY VARCHAR(10) The IP Version of the export_address. For example, ipv4.
CATALOG_PATH VARCHAR(8192) The absolute path to the catalog on the node.
NODE_TYPE VARCHAR(9) The type of the node. For more information on the types of nodes, refer to Setting node type.
IS_EPHEMERAL BOOLEAN (Deprecated) True if this node has been marked as ephemeral. (in preparation for removing it from the cluster).
STANDING_IN_FOR VARCHAR(128) The name of the node that this node is currently replacing.
SUBCLUSTER_NAME VARCHAR(128) In an Eon Mode database, the name of the subcluster that contains the node. Nodes belong to exactly one subcluster.
SANDBOX VARCHAR(128) In an Eon Mode database, the name, if any, of the sandbox to which the node belongs. NULL if the node is not a member of an active sandbox.
LAST_MSG_FROM_NODE_AT TIMESTAMPTZ The date and time the last message was received from this node.
NODE_DOWN_SINCE TIMESTAMPTZ The amount of time that the replaced node has been unavailable.
BUILD_INFO VARCHAR(128) The version of the Vertica server binary the node is running.

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)

48 - ODBC_COLUMNS

Provides table column information.

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.

Column Name Data Type Description
SCHEMA_NAME VARCHAR Name of the schema in which the column resides. If the column does not reside in a schema, this field is empty.
TABLE_NAME VARCHAR Name of the table in which the column resides.
COLUMN_NAME VARCHAR Name of the column.
DATA_TYPE INTEGER Data type of the column. This can be an ODBC SQL data type or a driver-specific SQL data type. This column corresponds to the ODBC_TYPE column in the TYPES table.
DATA_TYPE_NAME VARCHAR Driver-specific data type name.
COLUMN_SIZE INTEGER ODBC-defined data size of the column.
BUFFER_LENGTH INTEGER Transfer octet length of a column is the maximum number of bytes returned to the application when data is transferred to its default C data type. See http://msdn.microsoft.com/en-us/library/windows/desktop/ms713979%28v=vs.85%29.aspx
DECIMAL_DIGITS INTEGER Total number of significant digits to the right of the decimal point. This value has no meaning for non-decimal data types.
NUM_PREC_RADIX INTEGER Radix Vertica reports decimal_digits and columns_size as. This value is always 10, because it refers to a number of decimal digits, rather than a number of bits.
NULLABLE BOOLEAN Whether the column can contain null values.
REMARKS VARCHAR Textual remarks for the column.
COLUMN_DEFAULT VARCHAR Default value of the column.
SQL_TYPE_ID INTEGER SQL data type of the column.
SQL_DATETIME_SUB VARCHAR Subtype for a datetime data type. This value has no meaning for non-datetime data types.
CHAR_OCTET_LENGTH INTEGER Maximum length of a string or binary data column.
ORDINAL_POSITION INTEGER Position of the column in the table definition.
IS_NULLABLE VARCHAR Values can be YES or NO, determined by the value of the NULLABLE column.
IS_IDENTITY BOOLEAN Whether the column is an IDENTITY column.

49 - PASSWORD_AUDITOR

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

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

Column Name Data Type Description
USER_ID INTEGER Unique ID for the user.
USER_NAME VARCHAR Name of the user.
ACCTEXPIRED BOOLEAN Indicates if the user's password expires. 'f' indicates that it does not expire. 't' indicates that it does expire.
SECURITY_ALGORITHM VARCHAR

User-level security algorithm for hash authentication.

Valid values:

  • 'NONE' (Default. Algorithm specified by SYSTEM_SECURITY_ALGORITHM is used.)

  • 'SHA512'

  • 'MD5'

SYSTEM_SECURITY_ALGORITHM VARCHAR

System-level security algorithm for hash authentication.

Valid values:

  • 'SHA512' (Default)

  • 'MD5'

EFFECTIVE_SECURITY_ALGORITHM VARCHAR

The resulting security algorithm, depending on the values of SECURTY_ALGORITHM and SYSTEM_SECURITY_ALGORITHM.

For details, see Password hashing algorithm.

CURRENT_SECURITY_ALGORITHM VARCHAR

The security algorithm used to hash the user's current password. This can differ from the EFFECTIVE_SECURITY_ALGORITHM if a user hasn't reset their password since a change in the EFFECTIVE_SECURITY_ALGORITHM.

Valid values:

  • 'NONE' (Default. Algorithm specified by SYSTEM_SECURITY_ALGORITHM is used.)

  • 'SHA512'

  • 'MD5'

50 - PASSWORDS

Contains information on current user 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.

Column Name Data Type Description
USER_ID INTEGER The ID of the user who owns the password.
USER_NAME VARCHAR The name of the user who owns the password.
PASSWORD VARCHAR The hashed password.
PASSWORD_CREATE_TIME DATETIME The date and time when the password was created.
IS_CURRENT_PASSWORD BOOLEAN Denotes whether this is the user's current password. Non-current passwords are retained to enforce password reuse limitations.
PROFILE_ID INTEGER The ID number of the profile to which the user is assigned.
PROFILE_NAME VARCHAR The name of the profile to which the user is assigned.
PASSWORD_REUSE_MAX VARCHAR The number password changes that must take place before an old password can be reused.
PASSWORD_REUSE_TIME VARCHAR The amount of time that must pass before an old password can be reused.
SALT VARCHAR A hex string used to hash the password.

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)

51 - PRIMARY_KEYS

Provides primary key information.

Provides primary key information.

Column Name Data Type Description
CONSTRAINT_ID INTEGER A unique numeric ID assigned by the Vertica catalog, which identifies the constraint.
CONSTRAINT_NAME VARCHAR The constraint name for which information is listed.
COLUMN_NAME VARCHAR The column name for which information is listed.
ORDINAL_POSITION VARCHAR The position of the column within the key. The numbering of columns starts at 1.
TABLE_NAME VARCHAR The table name for which information is listed.
CONSTRAINT_TYPE VARCHAR The constraint type, p, for primary key.
IS_ENABLED` BOOLEAN Indicates if a table column constraint for a PRIMARY KEY is enabled by default. Can be t (True) or f (False).
TABLE_NAMESPACE_ID VARCHAR For Eon Mode databases, unique numeric identifier of the namespace that contains the table.
TABLE_NAMESPACE VARCHAR For Eon Mode databases, name of the namespace that contains the table.
TABLE_SCHEMA VARCHAR

The schema name for which information is listed.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

52 - PROFILE_PARAMETERS

Defines what information is stored in profiles.

Defines what information is stored in profiles.

Column Name Data Type Description
PROFILE_ID INTEGER The ID of the profile to which this parameter belongs.
PROFILE_NAME VARCHAR The name of the profile to which this parameter belongs.
PARAMETER_TYPE VARCHAR The policy type of this parameter (password_complexity, password_security, etc.)
PARAMETER_NAME VARCHAR The name of the parameter.
PARAMETER_LIMIT VARCHAR The parameter's value.

53 - PROFILES

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

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

Column Name Data Type Description
PROFILE_ID INTEGER Unique identifier for the profile.
PROFILE_NAME VARCHAR Profile name.
PASSWORD_LIFE_TIME VARCHAR Number of days before the user's password expires. After expiration, the user is forced to change passwords during login or warned that their password has expired if password_grace_time is set to a value other than zero or unlimited.
PASSWORD_MIN_LIFE_TIME VARCHAR The number of days a password must be set before it can be reset.
PASSWORD_MIN_CHAR_CHANGE VARCHAR The minimum number of characters that must be different from the previous password when performing a password reset.
PASSWORD_GRACE_TIME VARCHAR Number of days users are allowed to log in after their passwords expire. During the grace time, users are warned about their expired passwords when they log in. After the grace period, the user is forced to change passwords if he or she hasn't already.
PASSWORD_REUSE_MAX VARCHAR Number of password changes that must occur before the current password can be reused.
PASSWORD_REUSE_TIME VARCHAR Number of days that must pass after setting a password before it can be used again.
FAILED_LOGIN_ATTEMPTS VARCHAR Number of consecutive failed login attempts that triggers Vertica to lock the account.
PASSWORD_LOCK_TIME VARCHAR Number of days an account is locked after being locked due to too many failed login attempts.
PASSWORD_MAX_LENGTH VARCHAR Maximum number of characters allowed in a password.
PASSWORD_MIN_LENGTH VARCHAR Minimum number of characters required in a password.
PASSWORD_MIN_LETTERS VARCHAR The minimum number of letters (either uppercase or lowercase) required in a password.
PASSWORD_MIN_LOWERCASE_LETTERS VARCHAR The minimum number of lowercase.
PASSWORD_MIN_UPPERCASE_LETTERS VARCHAR The minimum number of uppercase letters required in a password.
PASSWORD_MIN_DIGITS VARCHAR The minimum number of digits required in a password.
PASSWORD_MIN_SYMBOLS VARCHAR The minimum of symbols (for example, !, #, $, etc.) required in a password.

Notes

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

See also

54 - PROJECTION_CHECKPOINT_EPOCHS

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

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

Column Name Data Type Description
NODE_ID INTEGER Unique numeric identifier of this projection's node.
NODE_NAME VARCHAR Name of this projection's node.
PROJECTION_SCHEMA_ID INTEGER Unique numeric identifier of the projection schema.
PROJECTION_SCHEMA VARCHAR Name of the projection schema.
PROJECTION_ID INTEGER Unique numeric identifier of this projection.
PROJECTION_NAME VARCHAR Name of this projection.
IS_UP_TO_DATE BOOLEAN Specifies whether the projection is up to date and available to participate in query execution.
CHECKPOINT_EPOCH INTEGER Checkpoint epoch of the projection on the corresponding node. Data up to and including this epoch is in persistent storage, and can be recovered in the event of node failure.
WOULD_RECOVER BOOLEAN

Determines whether data up to and including CHECKPOINT_EPOCH can be used to recover from an unclean shutdown:

  • t: CHECKPOINT_EPOCH is less than the cluster's Last Good Epoch, so data up to and including this epoch can be used during recovery.

  • f: Vertica must use Last Good Epoch to recover data for this projection.

See also: GET_LAST_GOOD_EPOCH

IS_BEHIND_AHM BOOLEAN

Specifies whether CHECKPOINT_EPOCH is less than the AHM (ancient history mark). If set to t (true), data for this projection cannot rolled back.

See also: GET_AHM_EPOCH

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)

55 - 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.

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

Column Name Data Type Description
PROJECTION_ID INTEGER Catalog-assigned numeric value that uniquely identifies the projection.
PROJECTION_NAME VARCHAR The projection name for which information is listed.
PROJECTION_COLUMN_NAME VARCHAR The projection column name.
COLUMN_POSITION INTEGER The ordinal position of a projection's column used in the CREATE PROJECTION statement.
SORT_POSITION INTEGER The projection's column sort specification, as specified in CREATE PROJECTION .. ORDER BY clause. If the column is not included in the projection's sort order, SORT_POSITION output is NULL.
COLUMN_ID INTEGER A unique numeric object ID (OID) that identifies the associated projection column object and is assigned by the Vertica catalog. This field is helpful as a key to other system tables.
DATA_TYPE VARCHAR Matches the corresponding table column data type (see V_CATALOG.COLUMNS). DATA_TYPE is provided as a complement to ENCODING_TYPE.
ENCODING_TYPE VARCHAR The encoding type defined on the projection column.
ACCESS_RANK INTEGER The access rank of the projection column. See the ACCESSRANK parameter in the CREATE PROJECTION statement for more information.
GROUP_ID INTEGER A unique numeric ID (OID) that identifies the group and is assigned by the Vertica catalog.
TABLE_NAMESPACE VARCHAR For Eon Mode databases, name of the namespace that contains the projection.
TABLE_SCHEMA VARCHAR

The name of the schema in which the projection is stored.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

TABLE_ID INTEGER Catalog-assigned numeric value that uniquely identifies the table.
TABLE_NAME VARCHAR The table name that contains the projection.
TABLE_COLUMN_ID VARCHAR Catalog-assigned VARCHAR value that uniquely identifies a table column.
TABLE_COLUMN_NAME VARCHAR The projection's corresponding table column name.
STATISTICS_TYPE VARCHAR

The type of statistics the column contains:

STATISTICS_UPDATED_TIMESTAMP TIMESTAMPTZ The time at which the columns statistics were last updated by ANALYZE_STATISTICS. By querying this column, along with STATISTICS_TYPE and PROJECTION_COLUMN_NAME, you can identify projection columns whose statistics need updating. See also system table PROJECTIONS.
IS_EXPRESSION BOOLEAN Indicates whether this projection column is calculated with an expression. For aggregate columns, IS_EXPRESSION is always true.
IS_AGGREGATE BOOLEAN Indicates whether the column is an aggregated column in a live aggregate projection. IS_AGGREGATE is always false for Top-K projection columns.
PARTITION_BY_POSITION INTEGER Position of that column in the PARTITION BY and GROUP BY clauses, if applicable.
ORDER_BY_POSITION INTEGER Set only for Top-K projections, specifies the column's position in the ORDER BY clause, as defined in the projection definition's window partition clause. If the column is omitted from the ORDER BY clause, ORDER_BY_POSITION output is NULL.
ORDER_BY_TYPE INTEGER

Type of sort order:

  • ASC NULLS FIRST

  • ASC NULLS LAST

  • DESC NULLS FIRST

  • DESC NULLS LAST

COLUMN_EXPRESSION VARCHAR Expression that calculates the column value.

Examples

See Statistics Data in PROJECTION_COLUMNS

See also

56 - PROJECTION_DELETE_CONCERNS

Lists projections whose design are liable to cause performance issues when deleting data.

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.

Column Name Data Type Description
PROJECTION_ID INTEGER The ID number of the projection
PROJECTION_SCHEMA VARCHAR The schema containing the projection
PROJECTION_NAME VARCHAR The projection's name
CREATION_TIME TIMESTAMPTZ When the projection was created
LAST_MODIFIED_TIME TIMESTAMPTZ When the projection was last modified
COMMENT VARCHAR A comment describing the potential delete performance issue.

57 - PROJECTIONS

Provides information about projections.

Provides information about projections.

Column Name Data Type Description
PROJECTION_SCHEMA_ID INTEGER A unique numeric ID that identifies the specific schema that contains the projection and is assigned by the Vertica catalog.
PROJECTION_SCHEMA VARCHAR

The name of the schema that contains the projection.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

PROJECTION_NAMESPACE_NAME VARCHAR For Eon Mode databases, name of the namespace that contains the projection.
PROJECTION_ID INTEGER A unique numeric ID that identifies the projection and is assigned by the Vertica catalog.
PROJECTION_NAME VARCHAR The projection name for which information is listed.
PROJECTION_BASENAME VARCHAR

The base name used for other projections:

  • For auto-created projections, identical to ANCHOR_TABLE_NAME.

  • For a manually-created projection, the name specified in the CREATE PROJECTION statement.

OWNER_ID INTEGER A unique numeric ID that identifies the projection owner and is assigned by the Vertica catalog.
OWNER_NAME VARCHAR The name of the projection's owner.
ANCHOR_TABLE_ID INTEGER The unique numeric identification (OID) of the projection's anchor table.
ANCHOR_TABLE_NAME VARCHAR The name of the projection's anchor table.
NODE_ID INTEGER A unique numeric ID (OID) for any nodes that contain any unsegmented projections.
NODE_NAME VARCHAR The names of any nodes that contain the projection. This column returns information for unsegmented projections only.
IS_PREJOIN BOOLEAN Deprecated, always set to f (false).
CREATED_EPOCH INTEGER The epoch in which the projection was created.
CREATE_TYPE VARCHAR

The method in which the projection was created:

  • CREATE PROJECTION: A custom projection created using CREATE PROJECTION.

  • CREATE TABLE: A superprojection that was automatically created when its associated table was created using CREATE TABLE.

  • ALTER TABLE: The system automatically created the key projection in response to a non-empty table.

  • CREATE TABLE WITH PROJ CLAUSE: A superprojection that was automatically created using CREATE TABLE.

  • DELAYED_CREATION: A superprojection that was automatically created when data was loaded for the first time into a new table.

  • DESIGNER: A projection created by Database Designer.

  • SYSTEM TABLE: A projection that was automatically created for a system table.

Rebalancing does not change the CREATE_TYPE value for a projection.

VERIFIED_FAULT_TOLERANCE INTEGER The projection K-safe value. This value can be greater than the database K-safety value (if more replications of a projection exist than are required to meet the database K-safety). This value cannot be less than the database K-safe setting.
IS_UP_TO_DATE BOOLEAN Specifies whether projection data is up to date. Only up-to-date projections are available to participate in query execution.
HAS_STATISTICS BOOLEAN

Specifies whether there are statistics for any column in the projection. HAS_STATISTICS returns true only when all non-epoch columns for a table or table partition have full statistics. For details, see Collecting table statistics and Collecting partition statistics.

IS_SEGMENTED BOOLEAN Specifies whether the projection is segmented.
SEGMENT_EXRESSION VARCHAR

The segmentation expression used for the projection. In the following example for the clicks_agg projection, the following values:

hash(clicks.user_id, (clicks.click_time)::date)

indicate that the projection was created with the following expression:

SEGMENTED BY HASH(clicks.user_id, (clicks.click_time)::date)

SEGMENT_RANGE VARCHAR

The percentage of projection data stored on each node, according to the segmentation expression. For example, segmenting a projection by the HASH function on all nodes results in a SEGMENT_RANGE value such as the following:

implicit range: node01[33.3%] node02[33.3%] node03[33.3%]

IS_SUPER_PROJECTION BOOLEAN Specifies whether a projection is a superprojection.
IS_KEY_CONSTRAINT_PROJECTION BOOLEAN

Indicates whether a projection is a key constraint projection:

  • t: A key constraint projection that validates a key constraint. Vertica uses the projection to efficiently enforce at least one enabled key constraint.

  • f: Not a projection that validates a key constraint.

HAS_EXPRESSIONS BOOLEAN Specifies whether this projection has expressions that define the column values. HAS_EXPRESSIONS is always true for live aggregate projections.
IS_AGGREGATE_PROJECTION BOOLEAN Specifies whether this projection is a live aggregate projection.
AGGREGATE_TYPE VARCHAR

Specifies the type of live aggregate projection:

  • GROUPBY

  • TOPK

IS_SHARED BOOLEAN Indicates whether the projection is located on shared storage.
PARTITION_RANGE_MIN VARCHAR Populated only if a projection specifies a partition range, the lowest and highest partition keys of the range.
PARTITION_RANGE_MAX
PARTITION_RANGE_MIN_EXPRESSION VARCHAR Populated only if a projection specifies partition range, the minimum and maximum range expressions as defined in the projection DDL.
PARTITION_RANGE_MAX_EXPRESSION

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

PROJECTION_COLUMNS

58 - REGISTERED_MODELS

Lists details about registered machine learning models in the database.

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.

Column Name Data Type Description
REGISTERED_NAME VARCHAR The abstract name to which the model is registered. This REGISTERED_NAME can represent a group of models for a higher-level application, where each model in the group has a unique version number.
REGISTERED_VERSION INTEGER The unique version number of the model under its specified REGISTERED_NAME.
STATUS INTEGER

The status of the registered model, one of the following:

  • under_review: Status assigned to newly registered models.

  • staging: Model is targeted for A/B testing against the model currently in production.

  • production: Model is in production for its specified application. Only one model can be in production for a given registered_name at one time.

  • archived: Status of models that were previously in production. Archived models can be returned to production at any time.

  • declined: Model is no longer in consideration for production.

  • unregistered: Model is removed from the versioning environment. The model does not appear in the REGISTERED_MODELS system table.

REGISTERED_TIME VARCHAR The time at which the model was registered.
MODEL_ID INTEGER The model's internal ID.
SCHEMA_NAME VARCHAR The name of the schema that contains the model.
MODEL_NAME VARCHAR The name of the model. [schema_name.]model_name can be used to uniquely identify a model, as can the combination of its REGISTERED_NAME and REGISTERED_VERSION.
MODEL_TYPE VARCHAR The type of algorithm used to create the model.
CATEGORY VARCHAR

The category of the model, one of the following:

  • VERTICA_MODELS

  • PMML

  • TENSORFLOW

By default, models created in Vertica are assigned to the VERTICA_MODELS category.

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

59 - RESOURCE_POOL_DEFAULTS

Returns default parameter settings for built-in and user-defined resource pools.

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

60 - RESOURCE_POOLS

Displays settings for built-in and user-defined 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.

Column Name Data Type Description
POOL_ID INTEGER Unique identifier for the resource pool
NAME VARCHAR The name of the resource pool.
SUBCLUSTER_OID INTEGER Unique identifier for a subcluster-specific resource pool. For global resource pools, 0 is returned.
SUBCLUSTER_NAME VARCHAR Specifies the subcluster that the subcluster-specific resource pool belongs to.If there are subcluster-specific resource pools with the same name on separate subclusters, multiple entries are returned. For global resource pools, this column is blank.
IS_INTERNAL BOOLEAN Specifies whether this pool is a built-in pool.
MEMORYSIZE VARCHAR The amount of memory allocated to this resource pool.
MAXMEMORYSIZE VARCHAR Value assigned as the maximum size this resource pool can grow by borrowing memory from the GENERAL pool.
MAXQUERYMEMORYSIZE VARCHAR The maximum amount of memory allocated by this pool to process any query.
EXECUTIONPARALLELISM INTEGER Limits the number of threads used to process any single query issued in this resource pool.
PRIORITY INTEGER Specifies priority of queries in this pool when they compete for resources in the GENERAL pool.
RUNTIMEPRIORITY VARCHAR

The run-time priority defined for this pool, indicates how many run-time resources (CPU, I/O bandwidth) the Resource Manager should dedicate to running queries in the resource pool. Valid values are:

  • HIGH

  • MEDIUM (default)

  • LOW

These values are relative to each other. Queries with a HIGH run-time priority are given more CPU and I/O resources than those with a MEDIUM or LOW run-time priority.

RUNTIMEPRIORITYTHRESHOLD INTEGER Limits in seconds how soon a query must finish before the Resource Manager assigns to it the resource pool's RUNTIMEPRIORITY setting.
QUEUETIMEOUT INTEGER INTERVAL The maximum length of time requests can wait for resources to become available before being rejected, specified in seconds or as an interval. This value is set by the pool's QUEUETIMEOUT parameter.
PLANNEDCONCURRENCY INTEGER The preferred number of queries that execute concurrently in this resource pool, specified by the pool's PLANNEDCONCURRENCY parameter.
MAXCONCURRENCY INTEGER The maximum number of concurrent execution slots available to the resource pool, specified by the poolMAXCONCURRENCY parameter.
RUNTIMECAP INTERVAL The maximum time a query in the pool can execute.
SINGLEINITIATOR BOOLEAN Set for backward compatibility.
CPUAFFINITYSET VARCHAR

The set of CPUs on which queries associated with this pool are executed. For example:

  • 0, 2-4 : Specifies CPUs 0, 2, 3, and 4

  • 25%: A percentage of available CPUs, rounded down to whole CPUs.

CPUAFFINITYMODE VARCHAR

Specifies whether to share usage of the CPUs assigned to this resource pool by CPUAFFINITYSET, one of the following:

  • SHARED: Queries that run in this pool share its CPUAFFINITYSET CPUs with other Vertica resource pools.

  • EXCLUSIVE: Dedicates CPUAFFINITYSET CPUs to this resource pool only, and excludes other Vertica resource pools. If CPUAFFINITYSET is set as a percentage, then that percentage of CPU resources available to Vertica is assigned solely for this resource pool.

  • ANY: Queries in this resource pool can run on any CPU.

CASCADETO VARCHAR A secondary resource pool for executing queries that exceed the RUNTIMECAP setting of this resource pool.
CASCADETOSUBCLUSTERPOOL BOOLEAN Specifies whether this resource pool cascades to a subcluster-level resource pool.

61 - ROLES

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

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

Column Name Data Type Description
ASSIGNED_ROLES VARCHAR

The names of any roles that have been granted to this role. By enabling the role, the user also has access to the privileges of these additional roles.

NAME VARCHAR The name of a role that the user can access.
ROLE_ID INTEGER Catalog-assigned integer value that uniquely identifies therole.
LDAP_DN VARCHAR Indicates whether or not the Vertica Analytic Database role maps to an LDAP Link group. When the column is set to dn, the Vertica role maps to LDAP Link.
LDAP_URI_HASH VARCHAR The URI hash number for the LDAP role.
IS_ORPHANED_FROM_LDAP VARCHAR

Indicates if the role is disconnected (orphaned) from LDAP, valid values are:

t - role is orphaned

f - role is not orphaned

For more information see Troubleshooting LDAP link issues

See also

62 - ROUTING_RULES

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

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

Column Name Data Type Description
NAME VARCHAR The name of the routing rule.
SOURCE_ADDRESS VARCHAR The IP address range in CIDR format that this rule applies to.
DESTINATION_NAME VARCHAR The load balance group that handles connections for this rule.

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

63 - SCHEDULER_TIME_TABLE

Contains information about scheduled tasks.

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)
Column Name Data Type Description
SCHEDULE_NAME VARCHAR The name of the schedule.
SCHEMA_NAME VARCHAR The schedule's schema.
OWNER VARCHAR The owner of the schedule.
ATTACHED_TRIGGER VARCHAR The trigger attached to the schedule. For details, see Scheduled execution.
ENABLED BOOLEAN Whether the schedule is enabled.
DATE_TIME_TYPE VARCHAR

The format for the scheduled event, one of the following:

  • CRON

  • DATE_TIME_LIST

DATE_TIME_STRING VARCHAR

The string used to schedule the event, one of the following:

  • A cron expression

  • A comma-separated list of timestamps

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

64 - SCHEMATA

Provides information about schemas in the database.

Provides information about schemas in the database.

Column Name Data Type Description
SCHEMA_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the specific schema.
SCHEMA_NAME VARCHAR

Schema name for which information is listed.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

SCHEMA_NAMESPACE_NAME VARCHAR For Eon Mode databases, name of the namespace that contains the schema.
SCHEMA_OWNER_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the owner who created the schema.
SCHEMA_OWNER VARCHAR Name of the owner who created the schema.
SYSTEM_SCHEMA_CREATOR VARCHAR Creator information for system schema or NULL for non-system schema
CREATE_TIME TIMESTAMPTZ Time when the schema was created.
IS_SYSTEM_SCHEMA BOOLEAN Indicates whether the schema was created for system use, where t is true and f is false.

Privileges

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

65 - SEQUENCES

Displays information about sequences.

Displays information about sequences.

Column Name Data Type Description
SEQUENCE_NAMESPACE VARCHAR For Eon Mode databases, name of the namespace that contains the sequence.
SEQUENCE_SCHEMA VARCHAR

The name of the schema that contains the sequence.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

SEQUENCE_NAME VARCHAR

One of the following:

  • User-assigned name of a sequence created with CREATE SEQUENCE

  • Auto-generated name assigned by Vertica for the sequence of an IDENTITY table column

OWNER_NAME VARCHAR

One of the following:

  • Owner of the named sequence

  • Owner of the table where an IDENTITY column is defined

IDENTITY_TABLE_NAME VARCHAR Set only for IDENTITY column sequences, name of the column table.
SESSION_CACHE_COUNT INTEGER Count of values cached in a session.
ALLOW_CYCLE BOOLEAN Whether values cycle when a sequence reaches its minimum or maximum value, as set by CREATE SEQUENCE parameter CYCLE|NO CYCLE.
OUTPUT_ORDERED BOOLEAN Values guaranteed to be ordered, always false.
INCREMENT_BY INTEGER Value by which sequences are incremented or decremented.
MINIMUM INTEGER Minimum value the sequence can generate.
MAXIMUM INTEGER Maximum value the sequence can generate.
CURRENT_VALUE INTEGER How many sequence numbers are distributed among all cluster nodes.
SEQUENCE_SCHEMA_ID INTEGER Unique numeric catalog ID of the sequence schema.
SEQUENCE_ID INTEGER Unique numeric catalog ID of the sequence.
OWNER_ID INTEGER Unique numeric catalog ID of the user who created the sequence.
IDENTITY_TABLE_ID INTEGER Set only for IDENTITY column sequences, unique numeric catalog ID of the column table.

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)

66 - 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.

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.

Column Name Data Type Description
NODE_OID INTEGER The OID of the subscribing node.
NODE_NAME VARCHAR The name of the subscribing node.
SHARD_OID INTEGER The OID of the shard the node subscribes to.
SHARD_NAME VARCHAR The name of the shard the node subscribes to.
IS_PARTICIPATING BOOLEAN Whether this subscription is used when resolving queries in this session.
IS_COLLABORATING BOOLEAN Whether this subscription is used to collaborate with a participating node when executing queries . This value is only true when queries are using elastic crunch scaling.

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)

67 - SHARDS

Lists the shards in your database.

Lists the shards in your database.

Column Name Data Type Description
SHARD_OID INTEGER The OID of the shard.
SHARD_NAME VARCHAR The name of the shard.
SHARD_TYPE VARCHAR The type of the shard.
LOWER_HASH_BOUND VARCHAR The lower hash bound of the shard.
UPPER_HASH_BOUND VARCHAR The upper hash bound of the shard.
IS_REPLICATED BOOLEAN Defines if the shard is replicated.
HAS_OBJECTS BOOLEAN Defines if the shard contains objects.

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
...

68 - STORAGE_LOCATIONS

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

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

Column Name Data Type Description
LOCATION_ID INTEGER Catalog-assigned integer value that uniquely identifies thestorage location.
NODE_NAME VARCHAR The node name on which the storage location exists.
LOCATION_PATH VARCHAR The path where the storage location is mounted.
LOCATION_USAGE VARCHAR

The type of information stored in the location:

  • DATA: Only data is stored in the location.

  • TEMP: Only temporary files that are created during loads or queries are stored in the location.

  • DATA,TEMP: Both types of files are stored in the location.

  • USER: The storage location can be used by users without their own credentials or dbadmin access. Users gain access to data by being granted access to the user storage location.

  • CATALOG: The area is used for the Vertica catalog. This usage is set internally and cannot be removed or changed.

SHARING_TYPE VARCHAR

How this location is shared among database nodes, if it is:

  • SHARED: The path used by the storage location is used by all nodes. See the SHARED parameter to CREATE LOCATION.

  • COMMUNAL: the location is used for communal storage in Eon Mode.

  • NONE: The location is not shared among nodes.

IS_RETIRED BOOLEAN Whether the storage location has been retired. This column has a value of t (true) if the location is retired, or f (false) if it is not.
LOCATION_LABEL VARCHAR The label associated with a specific storage location, added with the ALTER_LOCATION_LABEL function.
RANK INTEGER The Access rank value either assigned or supplied to the storage location, as described in Prioritizing column access speed.
THROUGHPUT INTEGER The throughput performance of the storage location, measured in MB/sec. You can get location performance values using MEASURE_LOCATION_PERFORMANCE, and set them with the SET_LOCATION_PERFORMANCE function.
LATENCY INTEGER The measured latency of the storage location as number of data seeks per second. You can get location performance values using MEASURE_LOCATION_PERFORMANCE, and set them with the SET_LOCATION_PERFORMANCE function.
MAX_SIZE INTEGER Maximum size of the storage location in bytes.
DISK_PERCENT VARCHAR Maximum percentage of available node disk space that this storage location can use, set only if depot size is defined as a percentage, otherwise blank.

Privileges

Superuser

See also

69 - STORED_PROC_TRIGGERS

Contains information about Triggers.

Contains information about Triggers.

Column Name Data Type Description
TRIGGER_NAME VARCHAR The name of the trigger.
NAMESPACE_NAME VARCHAR For Eon Mode databases, the trigger's namespace.
SCHEMA_NAME VARCHAR

The trigger's schema.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

OWNER VARCHAR The owner of the trigger.
PROCEDURE_NAME VARCHAR The name of the stored procedure.
PROCEDURE_ARGS INTEGER The number of formal parameters in the stored procedure.
ENABLED BOOLEAN Whether the trigger is enabled.

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

70 - SUBCLUSTER_RESOURCE_POOL_OVERRIDES

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

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

Column Name Data Type Description
POOL_OID INTEGER Unique identifier for the resource pool with settings overrides.
NAME VARCHAR The name of the built-in resource pool.
SUBCLUSTER_OID INTEGER Unique identifier for the subcluster with settings that override the global resource pool settings.
SUBCLUSTER_NAME VARCHAR The name of the subcluster with settings that overrides the global resource pool settings.
MEMORYSIZE VARCHAR The amount of memory allocated to the global resource pool.
MAXMEMORYSIZE VARCHAR Value assigned as the maximum size this resource pool can grow by borrowing memory from the GENERAL pool.
MAXQUERYMEMORYSIZE VARCHAR The maximum amount of memory allocated by this pool to process any query.

71 - SUBCLUSTERS

This table lists all of the subclusters defined in the database.

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.

Column Name Data Type Description
SUBCLUSTER_OID INTEGER Unique identifier for the subcluster.
SUBCLUSTER_NAME VARCHAR The name of the subcluster.
NODE_OID INTEGER The catalog-assigned ID of the node.
NODE_NAME VARCHAR The name of the node.
PARENT_OID INTEGER The unique ID of the parent of the node (the database).
PARENT_NAME VARCHAR The name of the parent of the node (the database name).
IS_DEFAULT BOOLEAN Whether the subcluster is the default cluster.
IS_PRIMARY BOOLEAN Whether the subcluster is a primary subcluster.
CONTROL_SET_SIZE INTEGER The number of control nodes defined for this subcluster. This value is -1 when the large cluster feature is not enabled, or when every node in the subcluster must be a control node. See Large cluster for more information.

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

72 - SYSTEM_COLUMNS

Provides table column information for SYSTEM_TABLES.

Provides table column information for SYSTEM_TABLES.

Column Name Data Type Description
TABLE_ID INTEGER A unique numeric ID assigned by the Vertica catalog, which identifies the table.
TABLE_SCHEMA VARCHAR The schema name for which information is listed.
TABLE_NAME VARCHAR The table name for which information is listed.
IS_SYSTEM_TABLE BOOLEAN Indicates whether the table is a system table, where t is true and f is false.
COLUMN_ID VARCHAR Catalog-assigned VARCHAR value that uniquely identifies a table column.
COLUMN_NAME VARCHAR The column name for which information is listed in the database.
DATA_TYPE VARCHAR The data type assigned to the column; for example VARCHAR(16).
DATA_TYPE_ID INTEGER A unique numeric ID assigned by the Vertica catalog, which identifies the data type.
DATA_TYPE_LENGTH INTEGER The maximum allowable length of the data type.
CHARACTER_MAXIMUM_LENGTH INTEGER The maximum allowable length of the column.
NUMERIC_PRECISION INTEGER The number of significant decimal digits.
NUMERIC_SCALE INTEGER The number of fractional digits.
DATETIME_PRECISION INTEGER For TIMESTAMP data type, returns the declared precision; returns null if no precision was declared.
INTERVAL_PRECISION INTEGER The number of fractional digits retained in the seconds field.
ORDINAL_POSITION INTEGER The position of the column respective to other columns in the table.
IS_NULLABLE BOOLEAN Indicates whether the column can contain null values, where t is true and f is false.
COLUMN_DEFAULT VARCHAR The default value of a column, such as empty or expression.

73 - SYSTEM_TABLES

Returns a list of all system table names.

Returns a list of all system table names.

Column Name Data Type Description
TABLE_SCHEMA_ID INTEGER Catalog-assigned integer value that uniquely identifies theschema.
TABLE_SCHEMA VARCHAR

The schema name in which the system table resides, one of the following:

TABLE_ID INTEGER Catalog-assigned integer value that uniquely identifies thetable.
TABLE_NAME VARCHAR The name of the system table.
TABLE_DESCRIPTION VARCHAR A description of the system table's purpose.
IS_SUPERUSER_ONLY BOOLEAN Specifies whether the table is accessible only by superusers by default. If false, then the PUBLIC role has the privileges to access the system table.
IS_MONITORABLE BOOLEAN Specifies whether the table is accessible by a user with the SYSMONITOR role enabled.
IS_ACCESSIBLE_DURING_LOCKDOWN BOOLEAN

Specifies whether RESTRICT_SYSTEM_TABLES_ACCESS revokes privileges from PUBLIC on the system table. These privileges can be restored with RELEASE_SYSTEM_TABLES_ACCESS.

In general, this field is set to t (true) for system tables that contain information that is typically needed by most users, such as TYPES. Conversely, this field is set to f (false) for tables with data that should be restricted during lockdown, such as database settings and user information.

74 - TABLE_CONSTRAINTS

Provides information about table constraints.

Provides information about table constraints.

Column Name Data Type Description
CONSTRAINT_ID INTEGER Catalog-assigned integer value that uniquely identifies theconstraint.
CONSTRAINT_NAME VARCHAR The name of the constraint, if specified as UNIQUE, FOREIGN KEY, NOT NULL, PRIMARY KEY, or CHECK.
CONSTRAINT_SCHEMA_ID INTEGER Catalog-assigned integer value that uniquely identifies theschema containing the constraint.
CONSTRAINT_KEY_COUNT INTEGER The number of constraint keys.
FOREIGN_KEY_COUNT INTEGER The number of foreign keys.
TABLE_ID INTEGER Catalog-assigned integer value that uniquely identifies thetable.
TABLE_NAME VARCHAR The name of the table that contains the UNIQUE, FOREIGN KEY, NOT NULL, or PRIMARY KEY constraint
FOREIGN_TABLE_ID INTEGER The unique object ID of the foreign table referenced in a foreign key constraint (zero if not a foreign key constraint).
CONSTRAINT_TYPE CHAR

Indicates the constraint type.

Valid Values:

  • c — check

  • f — foreign

  • p — primary

  • u — unique

IS_ENABLED` BOOLEAN Indicates if a constraint for a primary key, unique key, or check constraint is currently enabled. Can be t (True) or f (False).
PREDICATE VARCHAR For check constraints, the SQL expression.

See also

ANALYZE_CONSTRAINTS

75 - TABLES

Provides information about all tables in the database.

Provides information about all tables in the database.

The TABLE_SCHEMA and TABLE_NAME columns are case-sensitive. To restrict a query based on those columns, use the case-insensitive ILIKE predicate. For example:

=> SELECT table_schema, table_name FROM v_catalog.tables
   WHERE table_schema ILIKE 'Store%';
Column Name Data Type Description
TABLE_SCHEMA_ID INTEGER A unique numeric ID that identifies the schema and is assigned by the Vertica catalog.
TABLE_SCHEMA VARCHAR The schema name for which information is listed.
TABLE_NAMESPACE_NAME VARCHAR For Eon Mode databases, name of the namespace to which the table belongs.
TABLE_ID INTEGER A unique numeric ID that identifies the table and is assigned by the Vertica catalog.
TABLE_NAME VARCHAR The table name for which information is listed.
OWNER_ID INTEGER A unique numeric ID that identifies the owner and is assigned by the Vertica catalog.
OWNER_NAME VARCHAR The name of the user who created the table.
IS_TEMP_TABLE BOOLEAN Whether this table is a temporary table.
IS_SYSTEM_TABLE BOOLEAN Whether this table is a system table.
FORCE_OUTER INTEGER Whether this table is joined to another as an inner or outer input. For details, see Controlling join inputs.
IS_FLEXTABLE BOOLEAN Whether the table is a Flex table.
IS_SHARED BOOLEAN Whether the table is located on shared storage. Not used for temporary tables in Eon Mode.
HAS_AGGREGATE_PROJECTION BOOLEAN Whether the table has live aggregate projections.
SYSTEM_TABLE_CREATOR VARCHAR The name of the process that created the table, such as Designer.
PARTITION_EXPRESSION VARCHAR The table's partition expression.
CREATE_TIME TIMESTAMP When the table was created.
TABLE_DEFINITION VARCHAR For external tables, the COPY FROM portion of the table definition.
RECOVER_PRIORITY INTEGER The priority rank for the table for a Recovery By Table.
STORAGE_MODE INTEGER Deprecated, always set to DIRECT.
PARTITION_GROUP_EXPRESSION VARCHAR The expression of a GROUP BY clause that qualifies a table's partition clause.
ACTIVE_PARTITION_COUNT INTEGER The table's active partition count as set by CREATE TABLE or ALTER TABLE. If null, the table gets its active partition count from the ActivePartitionCount configuration parameter. For details, see Active and inactive partitions.
IS_MERGEOUT_ENABLED BOOLEAN Whether mergeout is enabled (t) or disabled (f) on ROS containers that consolidate projection data of this table. By default, mergeout is enabled on all tables. You can disable mergeout on a table with ALTER TABLE. For details, see Disabling mergeout on specific tables.
IMMUTABLE_ROWS_SINCE_TIMESTAMP TIMESTAMPTZ Set only for immutable tables, the server system time when immutability was applied to this table. This value can help with long-term timestamp retrieval and efficient comparison.
IMMUTABLE_ROWS_SINCE_EPOCH INTEGER Set only for immutable tables, the epoch that was current when immutability was applied. This setting can help protect the table from attempts to pre-insert records with a future timestamp, so that row's epoch is less than the table's immutability epoch.
IS_EXTERNAL_ICEBERG_TABLE BOOLEAN Whether this table is an Iceberg table.

Examples

Find when tables were created:

=> SELECT table_schema, table_name, create_time FROM tables;
 table_schema |      table_name       |          create_time
--------------+-----------------------+-------------------------------
 public       | customer_dimension    | 2011-08-15 11:18:25.784203-04
 public       | product_dimension     | 2011-08-15 11:18:25.815653-04
 public       | promotion_dimension   | 2011-08-15 11:18:25.850592-04
 public       | date_dimension        | 2011-08-15 11:18:25.892347-04
 public       | vendor_dimension      | 2011-08-15 11:18:25.942805-04
 public       | employee_dimension    | 2011-08-15 11:18:25.966985-04
 public       | shipping_dimension    | 2011-08-15 11:18:25.999394-04
 public       | warehouse_dimension   | 2011-08-15 11:18:26.461297-04
 public       | inventory_fact        | 2011-08-15 11:18:26.513525-04
 store        | store_dimension       | 2011-08-15 11:18:26.657409-04
 store        | store_sales_fact      | 2011-08-15 11:18:26.737535-04
 store        | store_orders_fact     | 2011-08-15 11:18:26.825801-04
 online_sales | online_page_dimension | 2011-08-15 11:18:27.007329-04
 online_sales | call_center_dimension | 2011-08-15 11:18:27.476844-04
 online_sales | online_sales_fact     | 2011-08-15 11:18:27.49749-04
(15 rows)

Find out whether certain tables are temporary and flex tables:

=> SELECT distinct table_name, table_schema, is_temp_table, is_flextable FROM v_catalog.tables
   WHERE table_name ILIKE 't%';
  table_name  | table_schema | is_temp_table | is_flextable
--------------+--------------+---------------+-----------------
 t2_temp      | public       | t             | t
 tt_keys      | public       | f             | f
 t2_temp_keys | public       | f             | f
 t3           | public       | t             | f
 t1           | public       | f             | f
 t9_keys      | public       | f             | f
 t2_keys      | public       | f             | t
 t6           | public       | t             | f
 t5           | public       | f             | f
 t2           | public       | f             | t
 t8           | public       | f             | f
 t7           | public       | t             | f
 tt           | public       | t             | t
 t2_keys_keys | public       | f             | f
 t9           | public       | t             | t
(15 rows)

76 - TEXT_INDICES

Provides summary information about the text indices in Vertica.

Provides summary information about the text indices in Vertica.

Column Name Data Type Description
INDEX_ID INTEGER A unique numeric ID that identifies the index and is assigned by the Vertica catalog.
INDEX_NAME VARCHAR The name of the text index.
INDEX_SCHEMA_NAME VARCHAR The schema name of the text index.
SOURCE_TABLE_ID INTEGER A unique numeric ID that identifies the table and is assigned by the Vertica catalog.
SOURCE_TABLE_NAME VARCHAR The name of the source table used to build the index.
SOURCE_TABLE_SCHEMA_NAME VARCHAR The schema name of the source table.
TOKENIZER_ID INTEGER A unique numeric ID that identifies the tokenizer and is assigned by the Vertica catalog.
TOKENIZER_NAME VARCHAR The name of the tokenizer used when building the index.
TOKENIZER_SCHEMA_NAME VARCHAR The schema name of the tokenizer.
STEMMER_ID INTEGER A unique numeric ID that identifies the stemmer and is assigned by the Vertica catalog.
STEMMER_NAME VARCHAR The name of the stemmer used when building the index.
STEMMER_SCHEMA_NAME VARCHAR The schema name of the stemmer.
TEXT_COL VARCHAR The text column used to build the index.

77 - TYPES

Provides information about supported data types.

Provides information about supported data types. This table does not include inlined complex types; see COMPLEX_TYPES instead. This table does include arrays and sets of primitive types.

Column Name Data Type Description
TYPE_ID INTEGER A unique numeric ID assigned by the Vertica catalog, which identifies the specific data type.
ODBC_TYPE INTEGER The numerical ODBC type.
ODBC_SUBTYPE INTEGER The numerical ODBC subtype, used to differentiate types such as time and interval that have multiple subtypes.
JDBC_TYPE INTEGER The numerical JDBC type.
JDBC_SUBTYPE INTEGER The numerical JDBC subtype, used to differentiate types such as time and interval that have multiple subtypes.
MIN_SCALE INTEGER The minimum number of digits supported to the right of the decimal point for the data type.
MAX_SCALE INTEGER The maximum number of digits supported to the right of the decimal point for the data type. A value of 0 is used for types that do not use decimal points.
COLUMN_SIZE INTEGER The number of characters required to display the type. See: http://msdn.microsoft.com/en-us/library/windows/desktop/ms711786%28v=VS.85%29.aspx for the details on COLUMN_SIZE for each type.
INTERVAL_MASK INTEGER For data types that are intervals, the bitmask to determine the range of the interval from the Vertica TYPE_ID. Details are available in the Vertica SDK.
TYPE_NAME VARCHAR The data type name associated with a particular data type ID.
CREATION_PARAMETERS VARCHAR A list of keywords, separated by commas, corresponding to each parameter that the application may specify in parentheses when using the name that is returned in the TYPE_NAME field. The keywords in the list can be any of the following: length, precision, or scale. They appear in the order that the syntax requires them to be used.

78 - USER_AUDITS

Lists the results of database and object size audits generated by users calling the AUDIT function.

Lists the results of database and object size audits generated by users calling the AUDIT function. See Monitoring database size for license compliance for more information.

Column Name Data Type Description
SIZE_BYTES INTEGER The estimated raw data size of the database
USER_ID INTEGER The ID of the user who generated the audit
USER_NAME VARCHAR The name of the user who generated the audit
OBJECT_ID INTEGER The ID of the object being audited
OBJECT_TYPE VARCHAR The type of object being audited (table, schema, etc.)
OBJECT_SCHEMA VARCHAR The schema containing the object being audited
OBJECT_NAME VARCHAR The name of the object being audited
AUDITED_SCHEMA_NAME VARCHAR

The name of the schema on which you want to query HISTORICAL data.

After running audit on a table, you can drop the table. In this case, object_schema becomes NULL.

AUDITED_OBJECT_NAME VARCHAR

The name of the object on which you want to query HISTORICAL data.

After running audit on a table, you can drop the table. In this case, object_name becomes NULL.

LICENSE_NAME VARCHAR The name of the license. After running a compliance audit, the value for this column is always vertica.
AUDIT_START_TIMESTAMP TIMESTAMPTZ When the audit started
AUDIT_END_TIMESTAMP TIMESTAMPTZ When the audit finished
CONFIDENCE_LEVEL_PERCENT FLOAT The confidence level of the size estimate
ERROR_TOLERANCE_PERCENT FLOAT The error tolerance used for the size estimate
USED_SAMPLING BOOLEAN Whether data was randomly sampled (if false, all of the data was analyzed)
CONFIDENCE_INTERVAL_LOWER_BOUND_BYTES INTEGER The lower bound of the data size estimate within the confidence level
CONFIDENCE_INTERVAL_UPPER_BOUND_BYTES INTEGER The upper bound of the data size estimate within the confidence level
SAMPLE_COUNT INTEGER The number of data samples used to generate the estimate
CELL_COUNT INTEGER The number of cells in the database

79 - USER_CLIENT_AUTH

Provides information about the client authentication methods that are associated with database users.

Provides information about the client authentication methods that are associated with database users. You associate an authentication method with a user using GRANT (Authentication).

Column Name Data Type Description
USER_OID INTEGER A unique identifier for that user.
USER_NAME VARCHAR Name of the user.
AUTH_OID INTEGER A unique identifier for the authentication method you are using.
AUTH_NAME VARCHAR Name that you gave to the authentication method.
GRANTED_TO BOOLEAN Name of the user with whom you have associated the authentication method using GRANT (Authentication).

80 - USER_CONFIGURATION_PARAMETERS

Provides information about user-level configuration parameters that are in effect for database users.

Provides information about user-level configuration parameters that are in effect for database users.

Column Name Data Type Description
USER_NAME VARCHAR Name of a database user with user-level settings.
PARAMETER_NAME VARCHAR The configuration parameter name.
CURRENT_ VALUE VARCHAR The parameter's current setting for this user.
DEFAULT_VALUE VARCHAR The parameter's default value.

Privileges

Superuser only

Examples

=> SELECT * FROM user_configuration_parameters;
 user_name |      parameter_name       | current_value | default_value
-----------+---------------------------+---------------+---------------
 Yvonne    | LoadSourceStatisticsLimit | 512           | 256
(1 row)

=> ALTER USER Ahmed SET DepotOperationsForQuery='FETCHES';
ALTER USER
=> ALTER USER Yvonne SET DepotOperationsForQuery='FETCHES';
ALTER USER
=> SELECT * FROM user_configuration_parameters;
 user_name |      parameter_name       | current_value | default_value
-----------+---------------------------+---------------+---------------
 Ahmed     | DepotOperationsForQuery   | FETCHES       | ALL
 Yvonne    | DepotOperationsForQuery   | FETCHES       | ALL
 Yvonne    | LoadSourceStatisticsLimit | 512           | 256
(3 rows)

81 - USER_FUNCTION_PARAMETERS

Provides information about the parameters of a C++ user-defined function (UDx).

Provides information about the parameters of a C++ user-defined function (UDx). You can only view parameters that have the Properties.visible parameter set to TRUE.

Column Name Data Type Description
NAMESPACE_NAME VARCHAR For Eon Mode databases, name of the namespace that contains the function.
SCHEMA_NAME VARCHAR(128)

The schema to which the function belongs.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

FUNCTION_NAME VARCHAR(128) The name assigned by the user to the user-defined function.
FUNCTION_TYPE VARCHAR(128) The type of user-defined function. For example, 'User Defined Function'.
FUNCTION_ARGUMENT_TYPE VARCHAR(8192) The number and data types of input arguments for the function.
PARAMETER_NAME VARCHAR(128) The name of the parameter for the user-defined function.
DATA_TYPE VARCHAR(128) The data type of the parameter.
DATA_TYPE_ID INTEGER A number specifying the ID for the parameter's data type.
DATA_TYPE_LENGTH INTEGER The maximum length of the parameter's data type.
IS_REQUIRED BOOLEAN

Indicates whether the parameter is required or not.

If set to TRUE, and you don't provide the parameter, Vertica throws an error.

CAN_BE_NULL BOOLEAN

Indicates whether the parameter can be passed as a NULL value.

If set to FALSE, you pass the parameter with a NULL value, Vertica throws an error.

COMMENT VARCHAR(128) A user-supplied description of the parameter.

Privileges

Any user can query the USER_FUNCTION_PARAMETERS table. However, users can only see table information about those UDx functions which the user has permission to use.

See also

82 - USER_FUNCTIONS

Returns metadata about user-defined SQL functions (which store commonly used SQL expressions as a function in the Vertica catalog) and user-defined functions.

Returns metadata about user-defined SQL functions (which store commonly used SQL expressions as a function in the Vertica catalog) and user-defined functions.

Column Name Data Type Description
SCHEMA_NAME VARCHAR The name of the schema in which this function exists.
FUNCTION_NAME VARCHAR The name assigned by the user to the SQL function or user-defined function.
PROCEDURE_TYPE VARCHAR The type of user-defined function. For example, 'User Defined Function'.
FUNCTION_RETURN_TYPE VARCHAR The data type name that the SQL function returns.
FUNCTION_ARGUMENT_TYPE VARCHAR The number and data types of parameters for the function.
FUNCTION_DEFINITION VARCHAR The SQL expression that the user defined in the SQL function's function body.
VOLATILITY VARCHAR The SQL function's volatility (whether a function returns the same output given the same input). Can be immutable, volatile, or stable.
IS_STRICT BOOLEAN Indicates whether the SQL function is strict, where t is true and f is false.
IS_FENCED BOOLEAN Indicates whether the function runs in Fenced and unfenced modes or not.
COMMENT VARCHAR A comment about this function provided by the function creator.

Notes

  • The volatility and strictness of a SQL function are automatically inferred from the function definition in order that Vertica determine the correctness of usage, such as where an immutable function is expected but a volatile function is provided.

  • The volatility and strictness of a UDx is defined by the UDx's developer.

Examples

Create a SQL function called myzeroifnull in the public schema:

=> CREATE FUNCTION myzeroifnull(x INT) RETURN INT
   AS BEGIN
     RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END);
   END;

Now query the USER_FUNCTIONS table. The query returns just the myzeroifnull macro because it is the only one created in this schema:

=> SELECT * FROM user_functions;
-[ RECORD 1 ]----------+---------------------------------------------------
schema_name            | public
function_name          | myzeroifnull
procedure_type         | User Defined Function
function_return_type   | Integer
function_argument_type | x Integer
function_definition    | RETURN CASE WHEN (x IS NOT NULL) THEN x ELSE 0 END
volatility             | immutable
is_strict              | f
is_fenced              | f
comment                |

See also

83 - USER_PROCEDURES

Provides information about stored procedures and external procedures.

Provides information about stored procedures and external procedures. Users can only view procedures that they can execute.

Column Name Data Type Description
PROCEDURE_NAME VARCHAR The name of the procedure.
OWNER VARCHAR The owner (definer) of the procedure.
LANGUAGE VARCHAR

The language in which the procedure is defined.

For external procedures, this will be EXTERNAL.

For stored procedures, this will be one of the supported languages.

SECURITY VARCHAR

The privileges to use when executing the procedure, one of the following:

  • DEFINER: Executes the procedure with the privileges of the owner (definer) of the procedure.

  • INVOKER: Executes the procedure with the privileges of the invoker.

For details, see Executing stored procedures.

PROCEDURE_ARGUMENTS VARCHAR The arguments of the procedure.
NAMESPACE_NAME VARCHAR For Eon Mode databases, name of the namespace that contains the procedure.
SCHEMA_NAME VARCHAR

The schema in which the procedure was defined.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

Privileges

Non-superusers can only view information on a procedure if they have:

  • USAGE privileges on the procedure's schema.

  • Ownership or EXECUTE privileges on the procedure.

Examples

=> SELECT * FROM user_procedures;
   procedure_name   |  owner  | language | security |                               procedure_arguments                                 | schema_name
--------------------+---------+----------+----------+-----------------------------------------------------------------------------------+-------------
 accurate_auc       | dbadmin | PL/vSQL  | INVOKER  | relation varchar, observation_col varchar, probability_col varchar, epsilon float | public
 conditionalTable   | dbadmin | PL/vSQL  | INVOKER  | b boolean                                                                         | public
 update_salary      | dbadmin | PL/vSQL  | INVOKER  | x int, y varchar                                                                  | public
(3 rows)

84 - USER_SCHEDULES

Contains information about schedules.

Contains information about schedules.

Column Name Data Type Description
SCHEDULE_NAME VARCHAR The name of the schedule.
NAMESPACE_NAME VARCHAR For Eon Mode databases, the schedule's namespace.
SCHEMA_NAME VARCHAR

The schedule's schema.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

OWNER VARCHAR The owner of the schedule.
ATTACHED_TRIGGER VARCHAR The trigger attached to the schedule, if any.
ATTACHED_TRIGGER BOOLEAN Whether the schedule is enabled.
DATE_TIME_TYPE VARCHAR

The format for the scheduled event, one of the following:

  • CRON

  • DATE_TIME_LIST

DATE_TIME_STRING VARCHAR

The string used to schedule the event, one of the following:

  • A cron expression

  • A comma-separated list of timestamps

Examples

To view schedules:

=> SELECT * FROM user_schedules;
     schedule_name     | schema_name |  owner  |        attached_trigger        | enabled | date_time_type |                date_time_string
-----------------------+-------------+---------+--------------------------------+---------+----------------+------------------------------------------------
 daily_1am             | management  | dbadmin | log_user_actions               | t       | CRON           | 0 1 * * *
 biannual_22_noon_gmt  | public      | dbadmin | refresh_logs                   | t       | DATE_TIME_LIST | 2022-01-01 12:00:00-00, 2022-06-01 12:00:00-00

85 - USER_TRANSFORMS

Lists the currently-defined user-defined transform functions (UDTFs).

Lists the currently-defined user-defined transform functions (UDTFs).

Column Name Data Type Description
SCHEMA_NAME VARCHAR(128) The name of the schema containing the UDTF.
FUNCTION_NAME VARCHAR(128) The SQL function name assigned by the user.
FUNCTION_RETURN_TYPE VARCHAR(128) The data types of the columns the UDTF returns.
FUNCTION_ARGUMENT_TYPE VARCHAR(8192) The data types of the columns that make up the input row.
FUNCTION_DEFINITION VARCHAR(128) A string containing the name of the factory class for the UDTF, and the name of the library that contains it.
IS_FENCED BOOLEAN Whether the UDTF runs in fenced mode.

Privileges

No explicit permissions are required; however, users see only UDTFs contained in schemas to which they have read access.

See also

86 - USERS

Provides information about all users in the database.

Provides information about all users in the database.

Column Name Data Type Description
USER_ID INTEGER A unique numeric ID assigned by the Vertica catalog, which identifies the user.
USER_NAME VARCHAR The user name for which information is listed.
IS_SUPER_USER BOOLEAN A system flag, where t (true) identifies the superuser created at the time of installation. All other users are denoted by f (false).
PROFILE_NAME VARCHAR The name of the profile to which the user is assigned. The profile controls the user's password policy.
IS_LOCKED BOOLEAN Whether the user's account is locked. A locked user cannot log into the system.
LOCK_TIME TIMESTAMPTZ When the user's account was locked. Used to determine when to automatically unlock the account, if the user's profile has a PASSWORD_LOCK_TIME parameter set.
RESOURCE_POOL VARCHAR The resource pool to which the user is assigned.
MANAGED_BY_OAUTH2_AUTH_ID INTEGER The ID of the OAuth authentication record used to authenticate and provision the user, if any.
LAST_LOGIN_TIME TIMESTAMPTZ

The last time the user logged in.

MEMORY_CAP_KB VARCHAR The maximum amount of memory a query run by the user can consume, in kilobytes.
TEMP_SPACE_CAP_KB VARCHAR The maximum amount of temporary disk space a query run by the user can consume, in kilobytes.
RUN_TIME_CAP VARCHAR The maximum amount of time any of the user's queries are allowed to run.
MAX_CONNECTIONS VARCHAR The maximum number of connections allowed for this user.
CONNECTION_LIMIT_MODE VARCHAR Indicates whether the user sets connection limits through the node or in database mode.
IDLE_SESSION_TIMEOUT VARCHAR

How the user handles idle session timeout limits, one of the following:

  • unlimited: There is no idle session time limit for the user.
  • default: The user's idle session time limit is the value of the DefaultIdleSessionTimeout database parameter, or if that parameter is not set or the user is a superuser, there is no timeout limit. To view the value of DefaultIdleSessionTimeout parameter, use the SHOW DATABASE statement:
    => SHOW DATABASE DEFAULT DEFAULTIDLESESSIONTIMEOUT;
    
  • Interval literal: Interval after which the user's idle session is disconnected.
GRACE_PERIOD VARCHAR Specifies how long a user query can block on any session socket, while awaiting client input or output. If the socket is blocked for a continuous period that exceeds the grace period setting, the server shuts down the socket and throws a fatal error. The session is then terminated.
ALL_ROLES VARCHAR Roles assigned to the user. An asterisk in ALL_ROLES output means role granted WITH ADMIN OPTION. See Database Roles.
DEFAULT_ROLES VARCHAR Default roles assigned to the user. An asterisk in DEFAULT_ROLES output means role granted WITH ADMIN OPTION. See Enabling roles automatically.
SEARCH_PATH VARCHAR Sets the default schema search path for the user. See Setting search paths.
LDAP_DN VARCHAR Indicates whether or not the Vertica Analytic Database user maps to an LDAP Link user. When the column is set to dn, the Vertica user maps to LDAP Link..
LDAP_URI_HASH INTEGER The URI hash number for the LDAP user.
IS_ORPHANED_FROM_LDAP BOOLEAN

Indicates if the user is disconnected (orphaned) from LDAP, set to one of the following:

  • t: User is orphaned

  • f : User is not orphaned

For more information see Troubleshooting LDAP link issues

See also

87 - VIEW_COLUMNS

Provides view attribute information.

Provides view attribute information.

Column Name Data Type Description
TABLE_ID INTEGER A unique numeric ID assigned by the Vertica catalog that identifies this view.
TABLE_NAMESPACE VARCHAR For Eon Mode databases, name of the namespace that contains the view.
TABLE_SCHEMA VARCHAR

The name of this view's schema.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

TABLE_NAME VARCHAR The view name.
COLUMN_ID VARCHAR A unique VARCHAR ID, assigned by the Vertica catalog, that identifies a column in this view.
COLUMN_NAME VARCHAR The name of a column in this view.
DATA_TYPE VARCHAR The data type of a view column.
DATA_TYPE_ID INTEGER A unique numeric ID assigned by the Vertica catalog, which identifies a view column's data type.
DATA_TYPE_LENGTH INTEGER The data type's maximum length.
CHARACTER_MAXIMUM_LENGTH INTEGER The column's maximum length, valid only for character types.
NUMERIC_PRECISION INTEGER The column's number of significant decimal digits.
NUMERIC_SCALE INTEGER The column's number of fractional digits.
DATETIME_PRECISION INTEGER For TIMESTAMP data type, returns the declared precision; returns null if no precision was declared.
INTERVAL_PRECISION INTEGER The number of fractional digits retained in the seconds field.
ORDINAL_POSITION INTEGER The position of the column relative to other columns in the view.

See also

VIEWS

88 - VIEW_TABLES

Shows details about view-related dependencies, including the table that reference a view, its schema, and owner.

Shows details about view-related dependencies, including the table that reference a view, its schema, and owner.

Column Name Data Type Description
TABLE_ID INTEGER Catalog-assigned integer value that uniquely identifies theview.
TABLE_SCHEMA VARCHAR Name of the view schema.
TABLE_NAME VARCHAR Name of the view.
REFERENCE_TABLE_ID INTEGER Catalog-assigned integer value that uniquely identifies theview's source table.
REFERENCE_TABLE_SCHEMA VARCHAR Name of the view's source table schema.
REFERENCE_TABLE_NAME VARCHAR Name of the view's source table.
REFERENCE_TABLE_OWNER_ID INTEGER Catalog-assigned integer value that uniquely identifies theview owner.

89 - VIEWS

Provides information about all within the system.

Provides information about all views within the system. See Views for more information.

Column Name Data Type Description
TABLE_SCHEMA_ID INTEGER Catalog-assigned integer value that uniquely identifies theview schema.
TABLE_NAMESPACE VARCHAR For Eon Mode databases, namespace that contains the view.
TABLE_SCHEMA VARCHAR

The name of the view schema.

If the schema belongs to a non-default namespace in an Eon Mode database, the schema name is front-qualified with the name of the schema's namespace. For example, n1.s refers to schema s in namespace n1.

TABLE_ID INTEGER Catalog-assigned integer value that uniquely identifies the view.
TABLE_NAME VARCHAR The view name.
OWNER_ID INTEGER Catalog-assigned integer value that uniquely identifies theview owner.
OWNER_NAME VARCHAR View owner's user name
VIEW_DEFINITION VARCHAR The query that defines the view.
IS_SYSTEM_VIEW BOOLEAN Indicates whether the view is a system view.
SYSTEM_VIEW_CREATOR VARCHAR View creator's user name.
CREATE_TIME TIMESTAMP Specifies when this view was created.
IS_LOCAL_TEMP_VIEW BOOLEAN Indicates whether this view is a temporary view stored locally.
INHERIT_PRIVILEGES BOOLEAN Indicates whether inherited privileges are enabled for this view. For details, see Setting privilege inheritance on tables and views.

See also

VIEW_COLUMNS

90 - WORKLOAD_ROUTING_RULES

Lists the routing rules that map workloads to a subcluster.

Lists the routing rules that map workloads to subclusters.

Column Name Data Type Description
OID INTEGER The object identifier for the routing rule.
WORKLOAD VARCHAR The name of the workload. When specified by a client (either as a connection property or as a session parameter with SET SESSION WORKLOAD), the client's queries are executed by the subcluster specified by the workload routing rule.
SUBCLUSTER_OID INTEGER The object identifier for the Subcluster.
SUBCLUSTER_NAME VARCHAR The subcluster that contains the Execution node that executes client queries for its associated workload.
PRIORITY INTEGER A non-negative INTEGER, the priority of the workload. The greater the value, the greater the priority. If a user or their role is granted multiple routing rules, the one with the greatest priority applies.

Privileges

Superuser

Examples

The following query shows two rules that define the following behavior:

  • Clients with workload reporting are routed to subcluster default_subcluster.
  • Clients with workload analytics are randomly routed to sc_01 or sc_02.
=> SELECT * FROM v_catalog.workload_routing_rules;

        oid        | workload  |  subcluster_oid   |  subcluster_name   | priority 
-------------------+-----------+-------------------+--------------------+----------
 45035996273850122 | reporting | 45035996273704962 | default_subcluster | 5
 45035996273849658 | analytics | 45035996273849568 | sc_01              | 4
 45035996273849658 | analytics | 45035996273849568 | sc_02              | 0

See also