Functions

Functions return information from the database.

Functions return information from the database. This section describes functions that Vertica supports. Except for meta-functions, you can use a function anywhere an expression is allowed.

Meta-functions usually access the internal state of Vertica. They can be used in a top-level SELECT statement only, and the statement cannot contain other clauses such as FROM or WHERE. Meta-functions are labeled on their reference pages.

The Behavior Type section on each reference page categorizes the function's return behavior as one or more of the following:

Immutable (invariant): When run with a given set of arguments, immutable functions always produce the same result, regardless of environment or session settings such as locale.
Stable: When run with a given set of arguments, stable functions produce the same result within a single query or scan operation. However, a stable function can produce different results when issued under different environments or at different times, such as change of locale and time zone—for example, SYSDATE.
Volatile: Regardless of their arguments or environment, volatile functions can return a different result with each invocation—for example, UUID_GENERATE.

List of all functions

The following list contains all Vertica SQL functions.

Jump to letter: A - B - C - D - E - F - G - H - I - J - K - L - M - N - O - P - Q - R - S - T - U - V - W - X - Y - Z

A

ABS: Returns the absolute value of the argument. [Mathematical functions]
ACOS: Returns a DOUBLE PRECISION value representing the trigonometric inverse cosine of the argument. [Mathematical functions]
ACOSH: Returns a DOUBLE PRECISION value that represents the inverse (arc) hyperbolic cosine of the function argument. [Mathematical functions]
ACTIVE_SCHEDULER_NODE: Returns the active scheduler node. [Stored procedure functions]
ADD_MONTHS: Adds the specified number of months to a date and returns the sum as a DATE. [Date/time functions]
ADVANCE_EPOCH: Manually closes the current epoch and begins a new epoch. [Epoch functions]
AGE_IN_MONTHS: Returns the difference in months between two dates, expressed as an integer. [Date/time functions]
AGE_IN_YEARS: Returns the difference in years between two dates, expressed as an integer. [Date/time functions]
ALTER_LOCATION_LABEL: Adds a label to a storage location, or changes or removes an existing label. [Storage functions]
ALTER_LOCATION_SIZE: Resizes on one node, all nodes in a subcluster, or all nodes in the database. [Eon Mode functions]
ALTER_LOCATION_USE: Alters the type of data that a storage location holds. [Storage functions]
ANALYZE_CONSTRAINTS: Analyzes and reports on constraint violations within the specified scope. [Table functions]
ANALYZE_CORRELATIONS: This function is deprecated and will be removed in a future release. [Table functions]
ANALYZE_EXTERNAL_ROW_COUNT: Calculates the exact number of rows in an external table. [Statistics management functions]
ANALYZE_STATISTICS: Collects and aggregates data samples and storage information from all nodes that store projections associated with the specified table. [Statistics management functions]
ANALYZE_STATISTICS_PARTITION: Collects and aggregates data samples and storage information for a range of partitions in the specified table. [Statistics management functions]
ANALYZE_WORKLOAD: Runs Workload Analyzer, a utility that analyzes system information held in system tables. [Workload management functions]
APPLY_AVG: Returns the average of all elements in a with numeric values. [Collection functions]
APPLY_BISECTING_KMEANS: Applies a trained bisecting k-means model to an input relation, and assigns each new data point to the closest matching cluster in the trained model. [Transformation functions]
APPLY_COUNT (ARRAY_COUNT): Returns the total number of non-null elements in a. [Collection functions]
APPLY_COUNT_ELEMENTS (ARRAY_LENGTH): Returns the total number of elements in a , including NULLs. [Collection functions]
APPLY_IFOREST: Applies an isolation forest (iForest) model to an input relation. [Transformation functions]
APPLY_INVERSE_PCA: Inverts the APPLY_PCA-generated transform back to the original coordinate system. [Transformation functions]
APPLY_INVERSE_SVD: Transforms the data back to the original domain. [Transformation functions]
APPLY_KMEANS: Assigns each row of an input relation to a cluster center from an existing k-means model. [Transformation functions]
APPLY_KPROTOTYPES: Assigns each row of an input relation to a cluster center from an existing k-prototypes model. [Transformation functions]
APPLY_MAX: Returns the largest non-null element in a. [Collection functions]
APPLY_MIN: Returns the smallest non-null element in a. [Collection functions]
APPLY_NORMALIZE: A UDTF function that applies the normalization parameters saved in a model to a set of specified input columns. [Transformation functions]
APPLY_ONE_HOT_ENCODER: A user-defined transform function (UDTF) that loads the one hot encoder model and writes out a table that contains the encoded columns. [Transformation functions]
APPLY_PCA: Transforms the data using a PCA model. [Transformation functions]
APPLY_SUM: Computes the sum of all elements in a of numeric values (INTEGER, FLOAT, NUMERIC, or INTERVAL). [Collection functions]
APPLY_SVD: Transforms the data using an SVD model. [Transformation functions]
APPROXIMATE_COUNT_DISTINCT: Returns the number of distinct non-NULL values in a data set. [Aggregate functions]
APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS: Calculates the number of distinct non-NULL values from the synopsis objects created by APPROXIMATE_COUNT_DISTINCT_SYNOPSIS. [Aggregate functions]
APPROXIMATE_COUNT_DISTINCT_SYNOPSIS: Summarizes the information of distinct non-NULL values and materializes the result set in a VARBINARY or LONG VARBINARY synopsis object. [Aggregate functions]
APPROXIMATE_COUNT_DISTINCT_SYNOPSIS_MERGE: Aggregates multiple synopses into one new synopsis. [Aggregate functions]
APPROXIMATE_MEDIAN [aggregate]: Computes the approximate median of an expression over a group of rows. [Aggregate functions]
APPROXIMATE_PERCENTILE [aggregate]: Computes the approximate percentile of an expression over a group of rows. [Aggregate functions]
APPROXIMATE_QUANTILES: Computes an array of weighted, approximate percentiles of a column within some user-specified error. [Aggregate functions]
ARGMAX [analytic]: This function is patterned after the mathematical function argmax(f(x)), which returns the value of x that maximizes f(x). [Analytic functions]
ARGMAX_AGG: Takes two arguments target and arg, where both are columns or column expressions in the queried dataset. [Aggregate functions]
ARGMIN [analytic]: This function is patterned after the mathematical function argmin(f(x)), which returns the value of x that minimizes f(x). [Analytic functions]
ARGMIN_AGG: Takes two arguments target and arg, where both are columns or column expressions in the queried dataset. [Aggregate functions]
ARIMA: Creates and trains an autoregressive integrated moving average (ARIMA) model from a time series with consistent timesteps. [Machine learning algorithms]
ARRAY_CAT: Concatenates two arrays of the same element type and dimensionality. [Collection functions]
ARRAY_CONTAINS: Returns true if the specified element is found in the array and false if not. [Collection functions]
ARRAY_DIMS: Returns the dimensionality of the input array. [Collection functions]
ARRAY_FIND: Returns the ordinal position of a specified element in an array, or -1 if not found. [Collection functions]
ASCII: Converts the first character of a VARCHAR datatype to an INTEGER. [String functions]
ASIN: Returns a DOUBLE PRECISION value representing the trigonometric inverse sine of the argument. [Mathematical functions]
ASINH: Returns a DOUBLE PRECISION value that represents the inverse (arc) hyperbolic sine of the function argument. [Mathematical functions]
ATAN: Returns a DOUBLE PRECISION value representing the trigonometric inverse tangent of the argument. [Mathematical functions]
ATAN2: Returns a DOUBLE PRECISION value representing the trigonometric inverse tangent of the arithmetic dividend of the arguments. [Mathematical functions]
ATANH: Returns a DOUBLE PRECISION value that represents the inverse hyperbolic tangent of the function argument. [Mathematical functions]
AUDIT: Returns the raw data size (in bytes) of a database, schema, or table as it is counted in an audit of the database size. [License functions]
AUDIT_FLEX: Returns the estimated ROS size of __raw__ columns, equivalent to the export size of the flex data in the audited objects. [License functions]
AUDIT_LICENSE_SIZE: Triggers an immediate audit of the database size to determine if it is in compliance with the raw data storage allowance included in your Vertica licenses. [License functions]
AUDIT_LICENSE_TERM: Triggers an immediate audit to determine if the Vertica license has expired. [License functions]
AUTOREGRESSOR: Creates an autoregressive (AR) model from a stationary time series with consistent timesteps that can then be used for prediction via PREDICT_AR. [Machine learning algorithms]
AVG [aggregate]: Computes the average (arithmetic mean) of an expression over a group of rows. [Aggregate functions]
AVG [analytic]: Computes an average of an expression in a group within a. [Analytic functions]
AZURE_TOKEN_CACHE_CLEAR: Clears the cached access token for Azure. [Cloud functions]

B

BACKGROUND_DEPOT_WARMING: Vertica version 10.0.0 removes support for foreground depot warming. [Eon Mode functions]
BALANCE: Returns a view with an equal distribution of the input data based on the response_column. [Data preparation]
BISECTING_KMEANS: Executes the bisecting k-means algorithm on an input relation. [Machine learning algorithms]
BIT_AND: Takes the bitwise AND of all non-null input values. [Aggregate functions]
BIT_LENGTH: Returns the length of the string expression in bits (bytes * 8) as an INTEGER. [String functions]
BIT_OR: Takes the bitwise OR of all non-null input values. [Aggregate functions]
BIT_XOR: Takes the bitwise XOR of all non-null input values. [Aggregate functions]
BITCOUNT: Returns the number of one-bits (sometimes referred to as set-bits) in the given VARBINARY value. [String functions]
BITSTRING_TO_BINARY: Translates the given VARCHAR bitstring representation into a VARBINARY value. [String functions]
BOOL_AND [aggregate]: Processes Boolean values and returns a Boolean value result. [Aggregate functions]
BOOL_AND [analytic]: Returns the Boolean value of an expression within a. [Analytic functions]
BOOL_OR [aggregate]: Processes Boolean values and returns a Boolean value result. [Aggregate functions]
BOOL_OR [analytic]: Returns the Boolean value of an expression within a. [Analytic functions]
BOOL_XOR [aggregate]: Processes Boolean values and returns a Boolean value result. [Aggregate functions]
BOOL_XOR [analytic]: Returns the Boolean value of an expression within a. [Analytic functions]
BTRIM: Removes the longest string consisting only of specified characters from the start and end of a string. [String functions]
BUILD_FLEXTABLE_VIEW: Creates, or re-creates, a view for a default or user-defined keys table, ignoring any empty keys. [Flex data functions]

C

CALENDAR_HIERARCHY_DAY: Groups DATE partition keys into a hierarchy of years, months, and days. [Partition functions]
CANCEL_DEPOT_WARMING: Cancels depot warming on a node. [Eon Mode functions]
CANCEL_DRAIN_SUBCLUSTER: Cancels the draining of a subcluster or subclusters. [Eon Mode functions]
CANCEL_REBALANCE_CLUSTER: Stops any rebalance task that is currently in progress or is waiting to execute. [Cluster functions]
CANCEL_REFRESH: Cancels refresh-related internal operations initiated by START_REFRESH and REFRESH. [Session functions]
CBRT: Returns the cube root of the argument. [Mathematical functions]
CEILING: Rounds up the returned value up to the next whole number. [Mathematical functions]
CHANGE_CURRENT_STATEMENT_RUNTIME_PRIORITY: Changes the run-time priority of an active query. [Workload management functions]
CHANGE_MODEL_STATUS: Changes the status of a registered model. [Model management]
CHANGE_RUNTIME_PRIORITY: Changes the run-time priority of a query that is actively running. [Workload management functions]
CHARACTER_LENGTH: The CHARACTER_LENGTH() function:. [String functions]
CHI_SQUARED: Computes the conditional chi-Square independence test on two categorical variables to find the likelihood that the two variables are independent. [Data preparation]
CHR: Converts the first character of an INTEGER datatype to a VARCHAR. [String functions]
CLEAN_COMMUNAL_STORAGE: Marks for deletion invalid data in communal storage, often data that leaked due to an event where Vertica cleanup mechanisms failed. [Eon Mode functions]
CLEAR_CACHES: Clears the Vertica internal cache files. [Storage functions]
CLEAR_DATA_COLLECTOR: Clears all memory and disk records from Data Collector tables and logs, and resets collection statistics in system table DATA_COLLECTOR. [Data Collector functions]
CLEAR_DATA_DEPOT: Deletes the specified depot data. [Eon Mode functions]
CLEAR_DEPOT_ANTI_PIN_POLICY_PARTITION: Removes an anti-pinning policy from the specified partition. [Eon Mode functions]
CLEAR_DEPOT_ANTI_PIN_POLICY_PROJECTION: Removes an anti-pinning policy from the specified projection. [Eon Mode functions]
CLEAR_DEPOT_ANTI_PIN_POLICY_TABLE: Removes an anti-pinning policy from the specified table. [Eon Mode functions]
CLEAR_DEPOT_PIN_POLICY_PARTITION: Clears a depot pinning policy from the specified table or projection partitions. [Eon Mode functions]
CLEAR_DEPOT_PIN_POLICY_PROJECTION: Clears a depot pinning policy from the specified projection. [Eon Mode functions]
CLEAR_DEPOT_PIN_POLICY_TABLE: Clears a depot pinning policy from the specified table. [Eon Mode functions]
CLEAR_DIRECTED_QUERY_USAGE: Resets the counter in the DIRECTED_QUERY_STATUS table. [Directed queries functions]
CLEAR_FETCH_QUEUE: Removes all entries or entries for a specific transaction from the queue of fetch requests of data from the communal storage. [Eon Mode functions]
CLEAR_HDFS_CACHES: Clears the configuration information copied from HDFS and any cached connections. [Hadoop functions]
CLEAR_OBJECT_STORAGE_POLICY: Removes a user-defined storage policy from the specified database, schema or table. [Storage functions]
CLEAR_PROFILING: Clears from memory data for the specified profiling type. [Profiling functions]
CLEAR_PROJECTION_REFRESHES: Clears information projection refresh history from system table PROJECTION_REFRESHES. [Projection functions]
CLEAR_RESOURCE_REJECTIONS: Clears the content of the RESOURCE_REJECTIONS and DISK_RESOURCE_REJECTIONS system tables. [Database functions]
CLOCK_TIMESTAMP: Returns a value of type TIMESTAMP WITH TIMEZONE that represents the current system-clock time. [Date/time functions]
CLOSE_ALL_RESULTSETS: Closes all result set sessions within Multiple Active Result Sets (MARS) and frees the MARS storage for other result sets. [Client connection functions]
CLOSE_ALL_SESSIONS: Closes all external sessions except the one that issues this function. [Session functions]
CLOSE_RESULTSET: Closes a specific result set within Multiple Active Result Sets (MARS) and frees the MARS storage for other result sets. [Client connection functions]
CLOSE_SESSION: Interrupts the specified external session, rolls back the current transaction if any, and closes the socket. [Session functions]
CLOSE_USER_SESSIONS: Stops the session for a user, rolls back any transaction currently running, and closes the connection. [Session functions]
COALESCE: Returns the value of the first non-null expression in the list. [NULL-handling functions]
COLLATION: Applies a collation to two or more strings. [String functions]
COMPACT_STORAGE: Bundles existing data (.fdb) and index (.pidx) files into the .gt file format. [Database functions]
COMPUTE_FLEXTABLE_KEYS: Computes the virtual columns (keys and values) from flex table VMap data. [Flex data functions]
COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW: Combines the functionality of BUILD_FLEXTABLE_VIEW and COMPUTE_FLEXTABLE_KEYS to compute virtual columns (keys) from the VMap data of a flex table and construct a view. [Flex data functions]
CONCAT: Concatenates two strings and returns a varchar data type. [String functions]
CONDITIONAL_CHANGE_EVENT [analytic]: Assigns an event window number to each row, starting from 0, and increments by 1 when the result of evaluating the argument expression on the current row differs from that on the previous row. [Analytic functions]
CONDITIONAL_TRUE_EVENT [analytic]: Assigns an event window number to each row, starting from 0, and increments the number by 1 when the result of the boolean argument expression evaluates true. [Analytic functions]
CONFUSION_MATRIX: Computes the confusion matrix of a table with observed and predicted values of a response variable. [Model evaluation]
CONTAINS: Returns true if the specified element is found in the collection and false if not. [Collection functions]
COPY_PARTITIONS_TO_TABLE: Copies partitions from one table to another. [Partition functions]
COPY_TABLE: Copies one table to another. [Table functions]
CORR: Returns the DOUBLE PRECISION coefficient of correlation of a set of expression pairs, as per the Pearson correlation coefficient. [Aggregate functions]
CORR_MATRIX: Takes an input relation with numeric columns, and calculates the Pearson Correlation Coefficient between each pair of its input columns. [Data preparation]
COS: Returns a DOUBLE PRECISION value tat represents the trigonometric cosine of the passed parameter. [Mathematical functions]
COSH: Returns a DOUBLE PRECISION value that represents the hyperbolic cosine of the passed parameter. [Mathematical functions]
COT: Returns a DOUBLE PRECISION value representing the trigonometric cotangent of the argument. [Mathematical functions]
COUNT [aggregate]: Returns as a BIGINT the number of rows in each group where the expression is not NULL. [Aggregate functions]
COUNT [analytic]: Counts occurrences within a group within a. [Analytic functions]
COVAR_POP: Returns the population covariance for a set of expression pairs. [Aggregate functions]
COVAR_SAMP: Returns the sample covariance for a set of expression pairs. [Aggregate functions]
CROSS_VALIDATE: Performs k-fold cross validation on a learning algorithm using an input relation, and grid search for hyper parameters. [Model evaluation]
CUME_DIST [analytic]: Calculates the cumulative distribution, or relative rank, of the current row with regard to other rows in the same partition within a . [Analytic functions]
CURRENT_DATABASE: Returns the name of the current database, equivalent to DBNAME. [System information functions]
CURRENT_DATE: Returns the date (date-type value) on which the current transaction started. [Date/time functions]
CURRENT_LOAD_SOURCE: When called within the scope of a COPY statement, returns the file name or path part used for the load. [System information functions]
CURRENT_SCHEMA: Returns the name of the current schema. [System information functions]
CURRENT_SESSION: Returns the ID of the current client session. [System information functions]
CURRENT_TIME: Returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction. [Date/time functions]
CURRENT_TIMESTAMP: Returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction. [Date/time functions]
CURRENT_TRANS_ID: Returns the ID of the transaction currently in progress. [System information functions]
CURRENT_USER: Returns a VARCHAR containing the name of the user who initiated the current database connection. [System information functions]
CURRVAL: Returns the last value across all nodes that was set by NEXTVAL on this sequence in the current session. [Sequence functions]

D

DATA_COLLECTOR_HELP: Returns online usage instructions about the Data Collector, the V_MONITOR.DATA_COLLECTOR system table, and the Data Collector control functions. [Data Collector functions]
DATE: Converts the input value to a DATE data type. [Date/time functions]
DATE_PART: Extracts a sub-field such as year or hour from a date/time expression, equivalent to the the SQL-standard function EXTRACT. [Date/time functions]
DATE_TRUNC: Truncates date and time values to the specified precision. [Date/time functions]
DATEDIFF: Returns the time span between two dates, in the intervals specified. [Date/time functions]
DAY: Returns as an integer the day of the month from the input value. [Date/time functions]
DAYOFMONTH: Returns the day of the month as an integer. [Date/time functions]
DAYOFWEEK: Returns the day of the week as an integer, where Sunday is day 1. [Date/time functions]
DAYOFWEEK_ISO: Returns the ISO 8061 day of the week as an integer, where Monday is day 1. [Date/time functions]
DAYOFYEAR: Returns the day of the year as an integer, where January 1 is day 1. [Date/time functions]
DAYS: Returns the integer value of the specified date, where 1 AD is 1. [Date/time functions]
DBNAME (function): Returns the name of the current database, equivalent to CURRENT_DATABASE. [System information functions]
DECODE: Compares expression to each search value one by one. [String functions]
DEGREES: Converts an expression from radians to fractional degrees, or from degrees, minutes, and seconds to fractional degrees. [Mathematical functions]
DELETE_TOKENIZER_CONFIG_FILE: Deletes a tokenizer configuration file. [Text search functions]
DEMOTE_SUBCLUSTER_TO_SECONDARY: Converts a to a . [Eon Mode functions]
DENSE_RANK [analytic]: Within each window partition, ranks all rows in the query results set according to the order specified by the window's ORDER BY clause. [Analytic functions]
DESCRIBE_LOAD_BALANCE_DECISION: Evaluates if any load balancing routing rules apply to a given IP address and This function is useful when you are evaluating connection load balancing policies you have created, to ensure they work the way you expect them to. [Client connection functions]
DESIGNER_ADD_DESIGN_QUERIES: Reads and evaluates queries from an input file, and adds the queries that it accepts to the specified design. [Database Designer functions]
DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS: Executes the specified query and evaluates results in the following columns:. [Database Designer functions]
DESIGNER_ADD_DESIGN_QUERY: Reads and parses the specified query, and if accepted, adds it to the design. [Database Designer functions]
DESIGNER_ADD_DESIGN_TABLES: Adds the specified tables to a design. [Database Designer functions]
DESIGNER_CANCEL_POPULATE_DESIGN: Cancels population or deployment operation for the specified design if it is currently running. [Database Designer functions]
DESIGNER_CREATE_DESIGN: Creates a design with the specified name. [Database Designer functions]
DESIGNER_DESIGN_PROJECTION_ENCODINGS: Analyzes encoding in the specified projections, creates a script to implement encoding recommendations, and optionally deploys the recommendations. [Database Designer functions]
DESIGNER_DROP_ALL_DESIGNS: Removes all Database Designer-related schemas associated with the current user. [Database Designer functions]
DESIGNER_DROP_DESIGN: Removes the schema associated with the specified design and all its contents. [Database Designer functions]
DESIGNER_OUTPUT_ALL_DESIGN_PROJECTIONS: Displays the DDL statements that define the design projections to standard output. [Database Designer functions]
DESIGNER_OUTPUT_DEPLOYMENT_SCRIPT: Displays the deployment script for the specified design to standard output. [Database Designer functions]
DESIGNER_RESET_DESIGN: Discards all run-specific information of the previous Database Designer build or deployment of the specified design but keeps its configuration. [Database Designer functions]
DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY: Populates the design and creates the design and deployment scripts. [Database Designer functions]
DESIGNER_SET_DESIGN_KSAFETY: Sets K-safety for a comprehensive design and stores the K-safety value in the DESIGNS table. [Database Designer functions]
DESIGNER_SET_DESIGN_TYPE: Specifies whether Database Designer creates a comprehensive or incremental design. [Database Designer functions]
DESIGNER_SET_OPTIMIZATION_OBJECTIVE: Valid only for comprehensive database designs, specifies the optimization objective Database Designer uses. [Database Designer functions]
DESIGNER_SET_PROPOSE_UNSEGMENTED_PROJECTIONS: Specifies whether a design can include unsegmented projections. [Database Designer functions]
DESIGNER_SINGLE_RUN: Evaluates all queries that completed execution within the specified timespan, and returns with a design that is ready for deployment. [Database Designer functions]
DESIGNER_WAIT_FOR_DESIGN: Waits for completion of operations that are populating and deploying the design. [Database Designer functions]
DETECT_OUTLIERS: Returns the outliers in a data set based on the outlier threshold. [Data preparation]
DISABLE_DUPLICATE_KEY_ERROR: Disables error messaging when Vertica finds duplicate primary or unique key values at run time (for use with key constraints that are not automatically enabled). [Table functions]
DISABLE_LOCAL_SEGMENTS: Disables local data segmentation, which breaks projections segments on nodes into containers that can be easily moved to other nodes. [Cluster functions]
DISABLE_PROFILING: Disables for the current session collection of profiling data of the specified type. [Profiling functions]
DISPLAY_LICENSE: Returns the terms of your Vertica license. [License functions]
DISTANCE: Returns the distance (in kilometers) between two points. [Mathematical functions]
DISTANCEV: Returns the distance (in kilometers) between two points using the Vincenty formula. [Mathematical functions]
DO_LOGROTATE_LOCAL: Rotates logs and removes rotated logs on the current node. [Database functions]
DO_TM_TASK: Runs a (TM) operation and commits current transactions. [Storage functions]
DROP_EXTERNAL_ROW_COUNT: Removes external table row count statistics compiled by ANALYZE_EXTERNAL_ROW_COUNT. [Statistics management functions]
DROP_LICENSE: Drops a license key from the global catalog. [Catalog functions]
DROP_LOCATION: Permanently removes a retired storage location. [Storage functions]
DROP_PARTITIONS: Drops the specified table partition keys. [Partition functions]
DROP_STATISTICS: Removes statistical data on database projections previously generated by ANALYZE_STATISTICS. [Statistics management functions]
DROP_STATISTICS_PARTITION: Removes statistical data on database projections previously generated by ANALYZE_STATISTICS_PARTITION. [Statistics management functions]
DUMP_CATALOG: Returns an internal representation of the Vertica catalog. [Catalog functions]
DUMP_LOCKTABLE: Returns information about deadlocked clients and the resources they are waiting for. [Database functions]
DUMP_PARTITION_KEYS: Dumps the partition keys of all projections in the system. [Database functions]
DUMP_PROJECTION_PARTITION_KEYS: Dumps the partition keys of the specified projection. [Partition functions]
DUMP_TABLE_PARTITION_KEYS: Dumps the partition keys of all projections for the specified table. [Partition functions]

E

EDIT_DISTANCE: Calculates and returns the Levenshtein distance between two strings. [String functions]
EMPTYMAP: Constructs a new VMap with one row but without keys or data. [Flex map functions]
ENABLE_ELASTIC_CLUSTER: Enables elastic cluster scaling, which makes enlarging or reducing the size of your database cluster more efficient by segmenting a node's data into chunks that can be easily moved to other hosts. [Cluster functions]
ENABLE_LOCAL_SEGMENTS: Enables local storage segmentation, which breaks projections segments on nodes into containers that can be easily moved to other nodes. [Cluster functions]
ENABLE_PROFILING: Enables collection of profiling data of the specified type for the current session. [Profiling functions]
ENABLE_SCHEDULE: Enables or disables a schedule. [Stored procedure functions]
ENABLE_TRIGGER: Enables or disables a trigger. [Stored procedure functions]
ENABLED_ROLE: Checks whether a Vertica user role is enabled, and returns true or false. [Privileges and access functions]
ENFORCE_OBJECT_STORAGE_POLICY: Applies storage policies of the specified object immediately. [Storage functions]
ERROR_RATE: Using an input table, returns a table that calculates the rate of incorrect classifications and displays them as FLOAT values. [Model evaluation]
EVALUATE_DELETE_PERFORMANCE: Evaluates projections for potential DELETE and UPDATE performance issues. [Projection functions]
EVENT_NAME: Returns a VARCHAR value representing the name of the event that matched the row. [MATCH clause functions]
EXECUTE_TRIGGER: Manually executes the stored procedure attached to a trigger. [Stored procedure functions]
EXP: Returns the exponential function, e to the power of a number. [Mathematical functions]
EXPLODE: Expands the elements of one or more collection columns (ARRAY or SET) into individual table rows, one row per element. [Collection functions]
EXPONENTIAL_MOVING_AVERAGE [analytic]: Calculates the exponential moving average (EMA) of expression E with smoothing factor X. [Analytic functions]
EXPORT_CATALOG: This function and EXPORT_OBJECTS return equivalent output. [Catalog functions]
EXPORT_DIRECTED_QUERIES: Generates SQL for creating directed queries from a set of input queries. [Directed queries functions]
EXPORT_MODELS: Exports machine learning models. [Model management]
EXPORT_OBJECTS: This function and EXPORT_CATALOG return equivalent output. [Catalog functions]
EXPORT_STATISTICS: Generates statistics in XML format from data previously collected by ANALYZE_STATISTICS. [Statistics management functions]
EXPORT_STATISTICS_PARTITION: Generates partition-level statistics in XML format from data previously collected by ANALYZE_STATISTICS_PARTITION. [Statistics management functions]
EXPORT_TABLES: Generates a SQL script that can be used to recreate a logical schema—schemas, tables, constraints, and views—on another cluster. [Catalog functions]
EXTERNAL_CONFIG_CHECK: Tests the Hadoop configuration of a Vertica cluster. [Hadoop functions]
EXTRACT: Retrieves sub-fields such as year or hour from date/time values and returns values of type NUMERIC. [Date/time functions]

F

FILTER: Takes an input array and returns an array containing only elements that meet a specified condition. [Collection functions]
FINISH_FETCHING_FILES: Fetches to the depot all files that are queued for download from communal storage. [Eon Mode functions]
FIRST_VALUE [analytic]: Lets you select the first value of a table or partition (determined by the window-order-clause) without having to use a self join. [Analytic functions]
FLOOR: Rounds down the returned value to the previous whole number. [Mathematical functions]
FLUSH_DATA_COLLECTOR: Waits until memory logs are moved to disk and then flushes the Data Collector, synchronizing the log with disk storage. [Data Collector functions]
FLUSH_REAPER_QUEUE: Deletes all data marked for deletion in the database. [Eon Mode functions]

G

GET_AHM_EPOCH: Returns the number of the in which the is located. [Epoch functions]
GET_AHM_TIME: Returns a TIMESTAMP value representing the. [Epoch functions]
GET_AUDIT_TIME: Reports the time when the automatic audit of database size occurs. [License functions]
GET_CLIENT_LABEL: Returns the client connection label for the current session. [Client connection functions]
GET_COMPLIANCE_STATUS: Displays whether your database is in compliance with your Vertica license agreement. [License functions]
GET_CONFIG_PARAMETER: Gets the value of a configuration parameter at the specified level. [Database functions]
GET_CURRENT_EPOCH: Returns the number of the current epoch. [Epoch functions]
GET_DATA_COLLECTOR_NOTIFY_POLICY: Lists any notification policies set on a component. [Notifier functions]
GET_DATA_COLLECTOR_POLICY: Retrieves a brief statement about the retention policy for the specified component. [Data Collector functions]
GET_LAST_GOOD_EPOCH: Returns the number. [Epoch functions]
GET_METADATA: Returns the metadata of a Parquet file. [Hadoop functions]
GET_MODEL_ATTRIBUTE: Extracts either a specific attribute from a model or all attributes from a model. [Model management]
GET_MODEL_SUMMARY: Returns summary information of a model. [Model management]
GET_NUM_ACCEPTED_ROWS: Returns the number of rows loaded into the database for the last completed load for the current session. [Session functions]
GET_NUM_REJECTED_ROWS: Returns the number of rows that were rejected during the last completed load for the current session. [Session functions]
GET_PRIVILEGES_DESCRIPTION: Returns the effective privileges the current user has on an object, including explicit, implicit, inherited, and role-based privileges. [Privileges and access functions]
GET_PROJECTION_SORT_ORDER: Returns the order of columns in a projection's ORDER BY clause. [Projection functions]
GET_PROJECTION_STATUS: Returns information relevant to the status of a :. [Projection functions]
GET_PROJECTIONS: Returns contextual and projection information about projections of the specified anchor table. [Projection functions]
GET_TOKENIZER_PARAMETER: Returns the configuration parameter for a given tokenizer. [Text search functions]
GETDATE: Returns the current statement's start date and time as a TIMESTAMP value. [Date/time functions]
GETUTCDATE: Returns the current statement's start date and time as a TIMESTAMP value. [Date/time functions]
GREATEST: Returns the largest value in a list of expressions of any data type. [String functions]
GREATESTB: Returns the largest value in a list of expressions of any data type, using binary ordering. [String functions]
GROUP_ID: Uniquely identifies duplicate sets for GROUP BY queries that return duplicate grouping sets. [Aggregate functions]
GROUPING: Disambiguates the use of NULL values when GROUP BY queries with multilevel aggregates generate NULL values to identify subtotals in grouping columns. [Aggregate functions]
GROUPING_ID: Concatenates the set of Boolean values generated by the GROUPING function into a bit vector. [Aggregate functions]

H

HADOOP_IMPERSONATION_CONFIG_CHECK: Reports the delegation tokens Vertica will use when accessing Kerberized data in HDFS. [Hadoop functions]
HAS_ROLE: Checks whether a Vertica user role is granted to the specified user or role, and returns true or false. [Privileges and access functions]
HAS_TABLE_PRIVILEGE: Returns true or false to verify whether a user has the specified privilege on a table. [System information functions]
HASH: Calculates a hash value over the function arguments, producing a value in the range 0 <= x < 263. [Mathematical functions]
HASH_EXTERNAL_TOKEN: Returns a hash of a string token, for use with HADOOP_IMPERSONATION_CONFIG_CHECK. [Hadoop functions]
HCATALOGCONNECTOR_CONFIG_CHECK: Tests the configuration of a Vertica cluster that uses the HCatalog Connector to access Hive data. [Hadoop functions]
HDFS_CLUSTER_CONFIG_CHECK: Tests the configuration of a Vertica cluster that uses HDFS. [Hadoop functions]
HEX_TO_BINARY: Translates the given VARCHAR hexadecimal representation into a VARBINARY value. [String functions]
HEX_TO_INTEGER: Translates the given VARCHAR hexadecimal representation into an INTEGER value. [String functions]
HOUR: Returns the hour portion of the specified date as an integer, where 0 is 00:00 to 00:59. [Date/time functions]

I

IFNULL: Returns the value of the first non-null expression in the list. [NULL-handling functions]
IFOREST: Trains and returns an isolation forest (iForest) model. [Data preparation]
IMPLODE: Takes a column of any scalar type and returns an unbounded array. [Collection functions]
IMPORT_DIRECTED_QUERIES: Imports to the database catalog directed queries from a SQL file that was generated by EXPORT_DIRECTED_QUERIES. [Directed queries functions]
IMPORT_MODELS: Imports models into Vertica, either Vertica models that were exported with EXPORT_MODELS, or models in Predictive Model Markup Language (PMML) or TensorFlow format. [Model management]
IMPORT_STATISTICS: Imports statistics from the XML file that was generated by EXPORT_STATISTICS. [Statistics management functions]
IMPUTE: Imputes missing values in a data set with either the mean or the mode, based on observed values for a variable in each column. [Data preparation]
INET_ATON: Converts a string that contains a dotted-quad representation of an IPv4 network address to an INTEGER. [IP address functions]
INET_NTOA: Converts an INTEGER value into a VARCHAR dotted-quad representation of an IPv4 network address. [IP address functions]
INFER_EXTERNAL_TABLE_DDL: This function is deprecated and will be removed in a future release. [Table functions]
INFER_TABLE_DDL: Inspects a file in Parquet, ORC, JSON, or Avro format and returns a CREATE TABLE or CREATE EXTERNAL TABLE statement based on its contents. [Table functions]
INITCAP: Capitalizes first letter of each alphanumeric word and puts the rest in lowercase. [String functions]
INITCAPB: Capitalizes first letter of each alphanumeric word and puts the rest in lowercase. [String functions]
INSERT: Inserts a character string into a specified location in another character string. [String functions]
INSTALL_LICENSE: Installs the license key in the global catalog. [Catalog functions]
INSTR: Searches string for substring and returns an integer indicating the position of the character in string that is the first character of this occurrence. [String functions]
INSTRB: Searches string for substring and returns an integer indicating the octet position within string that is the first occurrence. [String functions]
INTERRUPT_STATEMENT: Interrupts the specified statement in a user session, rolls back the current transaction, and writes a success or failure message to the log file. [Session functions]
ISFINITE: Tests for the special TIMESTAMP constant INFINITY and returns a value of type BOOLEAN. [Date/time functions]
ISNULL: Returns the value of the first non-null expression in the list. [NULL-handling functions]
ISUTF8: Tests whether a string is a valid UTF-8 string. [String functions]

J

JARO_DISTANCE: Calculates and returns the Jaro similarity, an edit distance between two sequences. [String functions]
JARO_WINKLER_DISTANCE: Calculates and returns the Jaro-Winkler similarity, an edit distance between two sequences. [String functions]
JULIAN_DAY: Returns the integer value of the specified day according to the Julian calendar, where day 1 is the first day of the Julian period, January 1, 4713 BC (on the Gregorian calendar, November 24, 4714 BC). [Date/time functions]

K

KERBEROS_CONFIG_CHECK: Tests the Kerberos configuration of a Vertica cluster. [Database functions]
KERBEROS_HDFS_CONFIG_CHECK: This function is deprecated and will be removed in a future release. [Hadoop functions]
KMEANS: Executes the k-means algorithm on an input relation. [Machine learning algorithms]
KPROTOTYPES: Executes the k-prototypes algorithm on an input relation. [Machine learning algorithms]

L

LAG [analytic]: Returns the value of the input expression at the given offset before the current row within a. [Analytic functions]
LAST_DAY: Returns the last day of the month in the specified date. [Date/time functions]
LAST_INSERT_ID: Returns the last value of an IDENTITY column. [Table functions]
LAST_VALUE [analytic]: Lets you select the last value of a table or partition (determined by the window-order-clause) without having to use a self join. [Analytic functions]
LDAP_LINK_DRYRUN_CONNECT: Takes a set of LDAP Link connection parameters as arguments and begins a dry run connection between the LDAP server and Vertica. [LDAP link functions]
LDAP_LINK_DRYRUN_SEARCH: Takes a set of LDAP Link connection and search parameters as arguments and begins a dry run search for users and groups that would get imported from the LDAP server. [LDAP link functions]
LDAP_LINK_DRYRUN_SYNC: Takes a set of LDAP Link connection and search parameters as arguments and begins a dry run synchronization between the database and the LDAP server, which maps and synchronizes the LDAP server's users and groups with their equivalents in Vertica. [LDAP link functions]
LDAP_LINK_SYNC_CANCEL: Cancels in-progress LDAP Link synchronizations (including those started by LDAP_LINK_DRYRUN_SYNC) between the LDAP server and Vertica. [LDAP link functions]
LDAP_LINK_SYNC_START: Begins the synchronization between the LDAP and Vertica servers immediately rather than waiting for the next scheduled run set by the parameters LDAPLinkInterval and LDAPLinkCron. [LDAP link functions]
LEAD [analytic]: Returns values from the row after the current row within a , letting you access more than one row in a table at the same time. [Analytic functions]
LEAST: Returns the smallest value in a list of expressions of any data type. [String functions]
LEASTB: Returns the smallest value in a list of expressions of any data type, using binary ordering. [String functions]
LEFT: Returns the specified characters from the left side of a string. [String functions]
LENGTH: Returns the length of a string. [String functions]
LIFT_TABLE: Returns a table that compares the predictive quality of a machine learning model. [Model evaluation]
LINEAR_REG: Executes linear regression on an input relation, and returns a linear regression model. [Machine learning algorithms]
LIST_ENABLED_CIPHERS: Returns a list of enabled cipher suites, which are sets of algorithms used to secure TLS/SSL connections. [System information functions]
LISTAGG: Transforms non-null values from a group of rows into a list of values that are delimited by commas (default) or a configurable separator. [Aggregate functions]
LN: Returns the natural logarithm of the argument. [Mathematical functions]
LOCALTIME: Returns a value of type TIME that represents the start of the current transaction. [Date/time functions]
LOCALTIMESTAMP: Returns a value of type TIMESTAMP/TIMESTAMPTZ that represents the start of the current transaction, and remains unchanged until the transaction is closed. [Date/time functions]
LOG: Returns the logarithm to the specified base of the argument. [Mathematical functions]
LOG10: Returns the base 10 logarithm of the argument, also known as the common logarithm. [Mathematical functions]
LOGISTIC_REG: Executes logistic regression on an input relation. [Machine learning algorithms]
LOWER: Takes a string value and returns a VARCHAR value converted to lowercase. [String functions]
LOWERB: Returns a character string with each ASCII character converted to lowercase. [String functions]
LPAD: Returns a VARCHAR value representing a string of a specific length filled on the left with specific characters. [String functions]
LTRIM: Returns a VARCHAR value representing a string with leading blanks removed from the left side (beginning). [String functions]

M

MAKE_AHM_NOW: Sets the (AHM) to the greatest allowable value. [Epoch functions]
MAKEUTF8: Coerces a string to UTF-8 by removing or replacing non-UTF-8 characters. [String functions]
MAPAGGREGATE: Returns a LONG VARBINARY VMap with key and value pairs supplied from two VARCHAR input columns. [Flex map functions]
MAPCONTAINSKEY: Determines whether a VMap contains a virtual column (key). [Flex map functions]
MAPCONTAINSVALUE: Determines whether a VMap contains a specific value. [Flex map functions]
MAPDELIMITEDEXTRACTOR: Extracts data with a delimiter character and other optional arguments, returning a single VMap value. [Flex extractor functions]
MAPITEMS: Returns information about items in a VMap. [Flex map functions]
MAPJSONEXTRACTOR: Extracts content of repeated JSON data objects,, including nested maps, or data with an outer list of JSON elements. [Flex extractor functions]
MAPKEYS: Returns the virtual columns (and values) present in any VMap data. [Flex map functions]
MAPKEYSINFO: Returns virtual column information from a given map. [Flex map functions]
MAPLOOKUP: Returns single-key values from VMAP data. [Flex map functions]
MAPPUT: Accepts a VMap and one or more key/value pairs and returns a new VMap with the key/value pairs added. [Flex map functions]
MAPREGEXEXTRACTOR: Extracts data with a regular expression and returns results as a VMap. [Flex extractor functions]
MAPSIZE: Returns the number of virtual columns present in any VMap data. [Flex map functions]
MAPTOSTRING: Recursively builds a string representation of VMap data, including nested JSON maps. [Flex map functions]
MAPVALUES: Returns a string representation of the top-level values from a VMap. [Flex map functions]
MAPVERSION: Returns the version or invalidity of any map data. [Flex map functions]
MARK_DESIGN_KSAFE: Enables or disables high availability in your environment, in case of a failure. [Catalog functions]
MATCH_COLUMNS: Specified as an element in a SELECT list, returns all columns in queried tables that match the specified pattern. [Regular expression functions]
MATCH_ID: Returns a successful pattern match as an INTEGER value. [MATCH clause functions]
MATERIALIZE_FLEXTABLE_COLUMNS: Materializes virtual columns listed as key_names in the flextable_keys table you compute using either COMPUTE_FLEXTABLE_KEYS or COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW. [Flex data functions]
MAX [aggregate]: Returns the greatest value of an expression over a group of rows. [Aggregate functions]
MAX [analytic]: Returns the maximum value of an expression within a. [Analytic functions]
MD5: Calculates the MD5 hash of string, returning the result as a VARCHAR string in hexadecimal. [String functions]
MEASURE_LOCATION_PERFORMANCE: Measures a storage location's disk performance. [Storage functions]
MEDIAN [analytic]: For each row, returns the median value of a value set within each partition. [Analytic functions]
MEMORY_TRIM: Calls glibc function malloc_trim() to reclaim free memory from malloc and return it to the operating system. [Database functions]
MICROSECOND: Returns the microsecond portion of the specified date as an integer. [Date/time functions]
MIDNIGHT_SECONDS: Within the specified date, returns the number of seconds between midnight and the date's time portion. [Date/time functions]
MIGRATE_ENTERPRISE_TO_EON: Migrates an Enterprise database to an Eon Mode database. [Eon Mode functions]
MIN [aggregate]: Returns the smallest value of an expression over a group of rows. [Aggregate functions]
MIN [analytic]: Returns the minimum value of an expression within a. [Analytic functions]
MINUTE: Returns the minute portion of the specified date as an integer. [Date/time functions]
MOD: Returns the remainder of a division operation. [Mathematical functions]
MONTH: Returns the month portion of the specified date as an integer. [Date/time functions]
MONTHS_BETWEEN: Returns the number of months between two dates. [Date/time functions]
MOVE_PARTITIONS_TO_TABLE: Moves partitions from one table to another. [Partition functions]
MOVE_RETIRED_LOCATION_DATA: Moves all data from the specified retired storage location or from all retired storage locations in the database. [Storage functions]
MOVE_STATEMENT_TO_RESOURCE_POOL: Attempts to move the specified query to the specified target pool. [Workload management functions]
MOVING_AVERAGE: Creates a moving-average (MA) model from a stationary time series with consistent timesteps that can then be used for prediction via PREDICT_MOVING_AVERAGE. [Machine learning algorithms]
MSE: Returns a table that displays the mean squared error of the prediction and response columns in a machine learning model. [Model evaluation]

N

NAIVE_BAYES: Executes the Naive Bayes algorithm on an input relation and returns a Naive Bayes model. [Machine learning algorithms]
NEW_TIME: Converts a timestamp value from one time zone to another and returns a TIMESTAMP. [Date/time functions]
NEXT_DAY: Returns the date of the first instance of a particular day of the week that follows the specified date. [Date/time functions]
NEXTVAL: Returns the next value in a sequence. [Sequence functions]
NORMALIZE: Runs a normalization algorithm on an input relation. [Data preparation]
NORMALIZE_FIT: This function differs from NORMALIZE, which directly outputs a view with normalized results, rather than storing normalization parameters into a model for later operation. [Data preparation]
NOTIFY: Sends a specified message to a NOTIFIER. [Notifier functions]
NOW [date/time]: Returns a value of type TIMESTAMP WITH TIME ZONE representing the start of the current transaction. [Date/time functions]
NTH_VALUE [analytic]: Returns the value evaluated at the row that is the nth row of the window (counting from 1). [Analytic functions]
NTILE [analytic]: Equally divides an ordered data set (partition) into a {value} number of subsets within a , where the subsets are numbered 1 through the value in parameter constant-value. [Analytic functions]
NULLIF: Compares two expressions. [NULL-handling functions]
NULLIFZERO: Evaluates to NULL if the value in the column is 0. [NULL-handling functions]
NVL: Returns the value of the first non-null expression in the list. [NULL-handling functions]
NVL2: Takes three arguments. [NULL-handling functions]

O

OCTET_LENGTH: Takes one argument as an input and returns the string length in octets for all string types. [String functions]
ONE_HOT_ENCODER_FIT: Generates a sorted list of each of the category levels for each feature to be encoded, and stores the model. [Data preparation]
OVERLAPS: Evaluates two time periods and returns true when they overlap, false otherwise. [Date/time functions]
OVERLAY: Replaces part of a string with another string and returns the new string value as a VARCHAR. [String functions]
OVERLAYB: Replaces part of a string with another string and returns the new string as an octet value. [String functions]

P

PARTITION_PROJECTION: Splits containers for a specified projection. [Partition functions]
PARTITION_TABLE: Invokes the to reorganize ROS storage containers as needed to conform with the current partitioning policy. [Partition functions]
PATTERN_ID: Returns an integer value that is a partition-wide unique identifier for the instance of the pattern that matched. [MATCH clause functions]
PCA: Computes principal components from the input table/view. [Data preparation]
PERCENT_RANK [analytic]: Calculates the relative rank of a row for a given row in a group within a by dividing that row’s rank less 1 by the number of rows in the partition, also less 1. [Analytic functions]
PERCENTILE_CONT [analytic]: An inverse distribution function where, for each row, PERCENTILE_CONT returns the value that would fall into the specified percentile among a set of values in each partition within a. [Analytic functions]
PERCENTILE_DISC [analytic]: An inverse distribution function where, for each row, PERCENTILE_DISC returns the value that would fall into the specified percentile among a set of values in each partition within a. [Analytic functions]
PI: Returns the constant pi (P), the ratio of any circle's circumference to its diameter in Euclidean geometry The return type is DOUBLE PRECISION. [Mathematical functions]
PLS_REG: Executes PLS regression on an input relation, and returns a PLS regression model. [Machine learning algorithms]
POISSON_REG: Executes Poisson regression on an input relation, and returns a Poisson regression model. [Machine learning algorithms]
POSITION: Returns an INTEGER value representing the character location of a specified substring with a string (counting from one). [String functions]
POSITIONB: Returns an INTEGER value representing the octet location of a specified substring with a string (counting from one). [String functions]
POWER: Returns a DOUBLE PRECISION value representing one number raised to the power of another number. [Mathematical functions]
PRC: Returns a table that displays the points on a receiver precision recall (PR) curve. [Model evaluation]
PREDICT_ARIMA: Applies an autoregressive integrated moving average (ARIMA) model to an input relation or makes predictions using the in-sample data. [Transformation functions]
PREDICT_AUTOREGRESSOR: Applies an autoregressor (AR) model to an input relation. [Transformation functions]
PREDICT_LINEAR_REG: Applies a linear regression model on an input relation and returns the predicted value as a FLOAT. [Transformation functions]
PREDICT_LOGISTIC_REG: Applies a logistic regression model on an input relation. [Transformation functions]
PREDICT_MOVING_AVERAGE: Applies a moving-average (MA) model, created by MOVING_AVERAGE, to an input relation. [Transformation functions]
PREDICT_NAIVE_BAYES: Applies a Naive Bayes model on an input relation. [Transformation functions]
PREDICT_NAIVE_BAYES_CLASSES: Applies a Naive Bayes model on an input relation and returns the probabilities of classes:. [Transformation functions]
PREDICT_PLS_REG: Applies a PLS regression model on an input relation and returns the predicted values. [Transformation functions]
PREDICT_PMML: Applies an imported PMML model on an input relation. [Transformation functions]
PREDICT_POISSON_REG: Applies a Poisson regression model on an input relation and returns the predicted value as a FLOAT. [Transformation functions]
PREDICT_RF_CLASSIFIER: Applies a random forest model on an input relation. [Transformation functions]
PREDICT_RF_CLASSIFIER_CLASSES: Applies a random forest model on an input relation and returns the probabilities of classes:. [Transformation functions]
PREDICT_RF_REGRESSOR: Applies a random forest model on an input relation, and returns with a FLOAT data type that specifies the predicted value of the random forest model—the average of the prediction of the trees in the forest. [Transformation functions]
PREDICT_SVM_CLASSIFIER: Uses an SVM model to predict class labels for samples in an input relation, and returns the predicted value as a FLOAT data type. [Transformation functions]
PREDICT_SVM_REGRESSOR: Uses an SVM model to perform regression on samples in an input relation, and returns the predicted value as a FLOAT data type. [Transformation functions]
PREDICT_TENSORFLOW: Applies a TensorFlow model on an input relation, and returns with the result expected for the encoded model type. [Transformation functions]
PREDICT_TENSORFLOW_SCALAR: Applies a TensorFlow model on an input relation, and returns with the result expected for the encoded model type. This function supports 1D complex types as input and output. [Transformation functions]
PREDICT_XGB_CLASSIFIER: Applies an XGBoost classifier model on an input relation. [Transformation functions]
PREDICT_XGB_CLASSIFIER_CLASSES: Applies an XGBoost classifier model on an input relation and returns the probabilities of classes:. [Transformation functions]
PREDICT_XGB_REGRESSOR: Applies an XGBoost regressor model on an input relation. [Transformation functions]
PROMOTE_SUBCLUSTER_TO_PRIMARY: Converts a secondary subcluster to a. [Eon Mode functions]
PURGE: Permanently removes delete vectors from ROS storage containers so disk space can be reused. [Database functions]
PURGE_PARTITION: Purges a table partition of deleted rows. [Partition functions]
PURGE_PROJECTION: PURGE_PROJECTION can use significant disk space while purging the data. [Projection functions]
PURGE_TABLE: This function was formerly named PURGE_TABLE_PROJECTIONS(). [Table functions]

Q

QUARTER: Returns calendar quarter of the specified date as an integer, where the January-March quarter is 1. [Date/time functions]
QUOTE_IDENT: Returns the specified string argument in the format required to use the string as an identifier in an SQL statement. [String functions]
QUOTE_LITERAL: Returns the given string suitably quoted for use as a string literal in a SQL statement string. [String functions]
QUOTE_NULLABLE: Returns the given string suitably quoted for use as a string literal in an SQL statement string; or if the argument is null, returns the unquoted string NULL. [String functions]

R

RADIANS: Returns a DOUBLE PRECISION value representing an angle expressed in radians. [Mathematical functions]
RANDOM: Returns a uniformly-distributed random DOUBLE PRECISION value x, where 0 <= x < 1. [Mathematical functions]
RANDOMINT: Accepts and returns an integer between 0 and the integer argument expression-1. [Mathematical functions]
RANDOMINT_CRYPTO: Accepts and returns an INTEGER value from a set of values between 0 and the specified function argument -1. [Mathematical functions]
RANK [analytic]: Within each window partition, ranks all rows in the query results set according to the order specified by the window's ORDER BY clause. [Analytic functions]
READ_CONFIG_FILE: Reads and returns the key-value pairs of all the parameters of a given tokenizer. [Text search functions]
READ_TREE: Reads the contents of trees within the random forest or XGBoost model. [Model evaluation]
REALIGN_CONTROL_NODES: Causes Vertica to re-evaluate which nodes in the cluster or subcluster are and which nodes are assigned to them as dependents when large cluster is enabled. [Cluster functions]
REBALANCE_CLUSTER: Rebalances the database cluster synchronously as a session foreground task. [Cluster functions]
REBALANCE_SHARDS: Rebalances shard assignments in a subcluster or across the entire cluster in Eon Mode. [Eon Mode functions]
REBALANCE_TABLE: Synchronously rebalances data in the specified table. [Table functions]
REENABLE_DUPLICATE_KEY_ERROR: Restores the default behavior of error reporting by reversing the effects of DISABLE_DUPLICATE_KEY_ERROR. [Table functions]
REFRESH: Synchronously refreshes one or more table projections in the foreground, and updates the PROJECTION_REFRESHES system table. [Projection functions]
REFRESH_COLUMNS: Refreshes table columns that are defined with the constraint SET USING or DEFAULT USING. [Projection functions]
REGEXP_COUNT: Returns the number times a regular expression matches a string. [Regular expression functions]
REGEXP_ILIKE: Returns true if the string contains a match for the regular expression. [Regular expression functions]
REGEXP_INSTR: Returns the starting or ending position in a string where a regular expression matches. [Regular expression functions]
REGEXP_LIKE: Returns true if the string matches the regular expression. [Regular expression functions]
REGEXP_NOT_ILIKE: Returns true if the string does not match the case-insensitive regular expression. [Regular expression functions]
REGEXP_NOT_LIKE: Returns true if the string does not contain a match for the regular expression. [Regular expression functions]
REGEXP_REPLACE: Replaces all occurrences of a substring that match a regular expression with another substring. [Regular expression functions]
REGEXP_SUBSTR: Returns the substring that matches a regular expression within a string. [Regular expression functions]
REGISTER_MODEL: Registers a trained model and adds it to Model Versioning environment with a status of 'under_review'. [Model management]
REGR_AVGX: Returns the DOUBLE PRECISION average of the independent expression in an expression pair. [Aggregate functions]
REGR_AVGY: Returns the DOUBLE PRECISION average of the dependent expression in an expression pair. [Aggregate functions]
REGR_COUNT: Returns the count of all rows in an expression pair. [Aggregate functions]
REGR_INTERCEPT: Returns the y-intercept of the regression line determined by a set of expression pairs. [Aggregate functions]
REGR_R2: Returns the square of the correlation coefficient of a set of expression pairs. [Aggregate functions]
REGR_SLOPE: Returns the slope of the regression line, determined by a set of expression pairs. [Aggregate functions]
REGR_SXX: Returns the sum of squares of the difference between the independent expression (expression2) and its average. [Aggregate functions]
REGR_SXY: Returns the sum of products of the difference between the dependent expression (expression1) and its average and the difference between the independent expression (expression2) and its average. [Aggregate functions]
REGR_SYY: Returns the sum of squares of the difference between the dependent expression (expression1) and its average. [Aggregate functions]
RELEASE_ALL_JVM_MEMORY: Forces all sessions to release the memory consumed by their Java Virtual Machines (JVM). [Session functions]
RELEASE_JVM_MEMORY: Terminates a Java Virtual Machine (JVM), making available the memory the JVM was using. [Session functions]
RELEASE_SYSTEM_TABLES_ACCESS: Enables non-superuser access to all system tables. [Privileges and access functions]
RELOAD_ADMINTOOLS_CONF: Updates the admintools.conf on each UP node in the cluster. [Catalog functions]
RELOAD_SPREAD: Updates cluster changes to the catalog's Spread configuration file. [Cluster functions]
REPEAT: Replicates a string the specified number of times and concatenates the replicated values as a single string. [String functions]
REPLACE: Replaces all occurrences of characters in a string with another set of characters. [String functions]
RESERVE_SESSION_RESOURCE: Reserves memory resources from the general resource pool for the exclusive use of the Vertica backup and restore process. [Session functions]
RESET_LOAD_BALANCE_POLICY: Resets the counter each host in the cluster maintains, to track which host it will refer a client to when the native connection load balancing scheme is set to ROUNDROBIN. [Client connection functions]
RESET_SESSION: Applies your default connection string configuration settings to your current session. [Session functions]
RESHARD_DATABASE: Changes the number of shards in a database. [Eon Mode functions]
RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW: Restores the keys table and the view. [Flex data functions]
RESTORE_LOCATION: Restores a storage location that was previously retired with RETIRE_LOCATION. [Storage functions]
RESTRICT_SYSTEM_TABLES_ACCESS: Checks system table SYSTEM_TABLES to determine which system tables non-superusers can access. [Privileges and access functions]
RETIRE_LOCATION: Deactivates the specified storage location. [Storage functions]
REVERSE_NORMALIZE: Reverses the normalization transformation on normalized data, thereby de-normalizing the normalized data. [Transformation functions]
RF_CLASSIFIER: Trains a random forest model for classification on an input relation. [Machine learning algorithms]
RF_PREDICTOR_IMPORTANCE: Measures the importance of the predictors in a random forest model using the Mean Decrease Impurity (MDI) approach. [Model evaluation]
RF_REGRESSOR: Trains a random forest model for regression on an input relation. [Machine learning algorithms]
RIGHT: Returns the specified characters from the right side of a string. [String functions]
ROC: Returns a table that displays the points on a receiver operating characteristic curve. [Model evaluation]
ROUND: Rounds the specified date or time. [Date/time functions]
ROUND: Rounds a value to a specified number of decimal places, retaining the original precision and scale. [Mathematical functions]
ROW_NUMBER [analytic]: Assigns a sequence of unique numbers to each row in a partition, starting with 1. [Analytic functions]
RPAD: Returns a VARCHAR value representing a string of a specific length filled on the right with specific characters. [String functions]
RSQUARED: Returns a table with the R-squared value of the predictions in a regression model. [Model evaluation]
RTRIM: Returns a VARCHAR value representing a string with trailing blanks removed from the right side (end). [String functions]
RUN_INDEX_TOOL: Runs the Index tool on a Vertica database to perform one of these tasks:. [Database functions]

S

SANDBOX_SUBCLUSTER: Creates a sandbox for a secondary subcluster. [Eon Mode functions]
SAVE_PLANS: Creates optimizer-generated directed queries from the most frequently executed queries, up to the maximum specified. [Directed queries functions]
SECOND: Returns the seconds portion of the specified date as an integer. [Date/time functions]
SECURITY_CONFIG_CHECK: Returns the status of various security-related parameters. [Database functions]
SESSION_USER: Returns a VARCHAR containing the name of the user who initiated the current database session. [System information functions]
SET_AHM_EPOCH: Sets the (AHM) to the specified epoch. [Epoch functions]
SET_AHM_TIME: Sets the (AHM) to the epoch corresponding to the specified time on the initiator node. [Epoch functions]
SET_AUDIT_TIME: Sets the time that Vertica performs automatic database size audit to determine if the size of the database is compliant with the raw data allowance in your Vertica license. [License functions]
SET_CLIENT_LABEL: Assigns a label to a client connection for the current session. [Client connection functions]
SET_CONFIG_PARAMETER: Sets or clears a configuration parameter at the specified level. [Database functions]
SET_CONTROL_SET_SIZE: Sets the number of that participate in the spread service when large cluster is enabled. [Cluster functions]
SET_DATA_COLLECTOR_NOTIFY_POLICY: Creates/enables notification policies for a component. [Notifier functions]
SET_DATA_COLLECTOR_POLICY: Updates the following retention policy properties for the specified component:. [Data Collector functions]
SET_DATA_COLLECTOR_POLICY (using parameters): Updates selected retention policy properties for a component. [Data Collector functions]
SET_DATA_COLLECTOR_TIME_POLICY: Updates the retention policy property INTERVAL_TIME for the specified component. [Data Collector functions]
SET_DEPOT_ANTI_PIN_POLICY_PARTITION: Assigns the highest depot eviction priority to a partition. [Eon Mode functions]
SET_DEPOT_ANTI_PIN_POLICY_PROJECTION: Assigns the highest depot eviction priority to a projection. [Eon Mode functions]
SET_DEPOT_ANTI_PIN_POLICY_TABLE: Assigns the highest depot eviction priority to a table. [Eon Mode functions]
SET_DEPOT_PIN_POLICY_PARTITION: Pins the specified partitions of a table or projection to a subcluster depot, or all database depots, to reduce exposure to depot eviction. [Eon Mode functions]
SET_DEPOT_PIN_POLICY_PROJECTION: Pins a projection to a subcluster depot, or all database depots, to reduce its exposure to depot eviction. [Eon Mode functions]
SET_DEPOT_PIN_POLICY_TABLE: Pins a table to a subcluster depot, or all database depots, to reduce its exposure to depot eviction. [Eon Mode functions]
SET_LOAD_BALANCE_POLICY: Sets how native connection load balancing chooses a host to handle a client connection. [Client connection functions]
SET_LOCATION_PERFORMANCE: Sets disk performance for a storage location. [Storage functions]
SET_OBJECT_STORAGE_POLICY: Creates or changes the storage policy of a database object by assigning it a labeled storage location. [Storage functions]
SET_SCALING_FACTOR: Sets the scaling factor that determines the number of storage containers used when rebalancing the database and when using local data segmentation is enabled. [Cluster functions]
SET_SPREAD_OPTION: Changes daemon settings. [Database functions]
SET_TOKENIZER_PARAMETER: Configures the tokenizer parameters. [Text search functions]
SET_UNION: Returns a SET containing all elements of two input sets. [Collection functions]
SHA1: Uses the US Secure Hash Algorithm 1 to calculate the SHA1 hash of string. [String functions]
SHA224: Uses the US Secure Hash Algorithm 2 to calculate the SHA224 hash of string. [String functions]
SHA256: Uses the US Secure Hash Algorithm 2 to calculate the SHA256 hash of string. [String functions]
SHA384: Uses the US Secure Hash Algorithm 2 to calculate the SHA384 hash of string. [String functions]
SHA512: Uses the US Secure Hash Algorithm 2 to calculate the SHA512 hash of string. [String functions]
SHOW_PROFILING_CONFIG: Shows whether profiling is enabled. [Profiling functions]
SHUTDOWN: Shuts down a Vertica database. [Database functions]
SHUTDOWN_SUBCLUSTER: Shuts down a subcluster. [Eon Mode functions]
SHUTDOWN_WITH_DRAIN: Gracefully shuts down a subcluster or subclusters. [Eon Mode functions]
SIGN: Returns a DOUBLE PRECISION value of -1, 0, or 1 representing the arithmetic sign of the argument. [Mathematical functions]
SIN: Returns a DOUBLE PRECISION value that represents the trigonometric sine of the passed parameter. [Mathematical functions]
SINH: Returns a DOUBLE PRECISION value that represents the hyperbolic sine of the passed parameter. [Mathematical functions]
SLEEP: Waits a specified number of seconds before executing another statement or command. [Workload management functions]
SOUNDEX: Takes a VARCHAR argument and returns a four-character code that enables comparison of that argument with other SOUNDEX-encoded strings that are spelled differently in English, but are phonetically similar. [String functions]
SOUNDEX_MATCHES: Compares the Soundex encodings of two strings. [String functions]
SPACE: Returns the specified number of blank spaces, typically for insertion into a character string. [String functions]
SPLIT_PART: Splits string on the delimiter and returns the string at the location of the beginning of the specified field (counting from 1). [String functions]
SPLIT_PARTB: Divides an input string on a delimiter character and returns the Nth segment, counting from 1. [String functions]
SQRT: Returns a DOUBLE PRECISION value representing the arithmetic square root of the argument. [Mathematical functions]
ST_Area: Calculates the area of a spatial object. [Geospatial functions]
ST_AsBinary: Creates the Well-Known Binary (WKB) representation of a spatial object. [Geospatial functions]
ST_AsText: Creates the Well-Known Text (WKT) representation of a spatial object. [Geospatial functions]
ST_Boundary: Calculates the boundary of the specified GEOMETRY object. [Geospatial functions]
ST_Buffer: Creates a GEOMETRY object greater than or equal to a specified distance from the boundary of a spatial object. [Geospatial functions]
ST_Centroid: Calculates the geometric center—the centroid—of a spatial object. [Geospatial functions]
ST_Contains: Determines if a spatial object is entirely inside another spatial object without existing only on its boundary. [Geospatial functions]
ST_ConvexHull: Calculates the smallest convex GEOMETRY object that contains a GEOMETRY object. [Geospatial functions]
ST_Crosses: Determines if one GEOMETRY object spatially crosses another GEOMETRY object. [Geospatial functions]
ST_Difference: Calculates the part of a spatial object that does not intersect with another spatial object. [Geospatial functions]
ST_Disjoint: Determines if two GEOMETRY objects do not intersect or touch. [Geospatial functions]
ST_Distance: Calculates the shortest distance between two spatial objects. [Geospatial functions]
ST_Envelope: Calculates the minimum bounding rectangle that contains the specified GEOMETRY object. [Geospatial functions]
ST_Equals: Determines if two spatial objects are spatially equivalent. [Geospatial functions]
ST_GeographyFromText: Converts a Well-Known Text (WKT) string into its corresponding GEOGRAPHY object. [Geospatial functions]
ST_GeographyFromWKB: Converts a Well-Known Binary (WKB) value into its corresponding GEOGRAPHY object. [Geospatial functions]
ST_GeoHash: Returns a GeoHash in the shape of the specified geometry. [Geospatial functions]
ST_GeometryN: Returns the n geometry within a geometry object. [Geospatial functions]
ST_GeometryType: Determines the class of a spatial object. [Geospatial functions]
ST_GeomFromGeoHash: Returns a polygon in the shape of the specified GeoHash. [Geospatial functions]
ST_GeomFromGeoJSON: Converts the geometry portion of a GeoJSON record in the standard format into a GEOMETRY object. [Geospatial functions]
ST_GeomFromText: Converts a Well-Known Text (WKT) string into its corresponding GEOMETRY object. [Geospatial functions]
ST_GeomFromWKB: Converts the Well-Known Binary (WKB) value to its corresponding GEOMETRY object. [Geospatial functions]
ST_Intersection: Calculates the set of points shared by two GEOMETRY objects. [Geospatial functions]
ST_Intersects: Determines if two GEOMETRY or GEOGRAPHY objects intersect or touch at a single point. [Geospatial functions]
ST_IsEmpty: Determines if a spatial object represents the empty set. [Geospatial functions]
ST_IsSimple: Determines if a spatial object does not intersect itself or touch its own boundary at any point. [Geospatial functions]
ST_IsValid: Determines if a spatial object is well formed or valid. [Geospatial functions]
ST_Length: Calculates the length of a spatial object. [Geospatial functions]
ST_NumGeometries: Returns the number of geometries contained within a spatial object. [Geospatial functions]
ST_NumPoints: Calculates the number of vertices of a spatial object, empty objects return NULL. [Geospatial functions]
ST_Overlaps: Determines if a GEOMETRY object shares space with another GEOMETRY object, but is not completely contained within that object. [Geospatial functions]
ST_PointFromGeoHash: Returns the center point of the specified GeoHash. [Geospatial functions]
ST_PointN: Finds the n point of a spatial object. [Geospatial functions]
ST_Relate: Determines if a given GEOMETRY object is spatially related to another GEOMETRY object, based on the specified DE-9IM pattern matrix string. [Geospatial functions]
ST_SRID: Identifies the spatial reference system identifier (SRID) stored with a spatial object. [Geospatial functions]
ST_SymDifference: Calculates all the points in two GEOMETRY objects except for the points they have in common, but including the boundaries of both objects. [Geospatial functions]
ST_Touches: Determines if two GEOMETRY objects touch at a single point or along a boundary, but do not have interiors that intersect. [Geospatial functions]
ST_Transform: Returns a new GEOMETRY with its coordinates converted to the spatial reference system identifier (SRID) used by the srid argument. [Geospatial functions]
ST_Union: Calculates the union of all points in two spatial objects. [Geospatial functions]
ST_Within: If spatial object g1 is completely inside of spatial object g2, then ST_Within returns true. [Geospatial functions]
ST_X: Determines the x- coordinate for a GEOMETRY point or the longitude value for a GEOGRAPHY point. [Geospatial functions]
ST_XMax: Returns the maximum x-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object. [Geospatial functions]
ST_XMin: Returns the minimum x-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object. [Geospatial functions]
ST_Y: Determines the y-coordinate for a GEOMETRY point or the latitude value for a GEOGRAPHY point. [Geospatial functions]
ST_YMax: Returns the maximum y-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object. [Geospatial functions]
ST_YMin: Returns the minimum y-coordinate of the minimum bounding rectangle of the GEOMETRY or GEOGRAPHY object. [Geospatial functions]
START_DRAIN_SUBCLUSTER: Drains a subcluster or subclusters. [Eon Mode functions]
START_REAPING_FILES: Starts the disk file deletion in the background as an asynchronous function. [Eon Mode functions]
START_REBALANCE_CLUSTER: Asynchronously rebalances the database cluster as a background task. [Cluster functions]
START_REFRESH: Refreshes projections in the current schema with the latest data of their respective. [Projection functions]
STATEMENT_TIMESTAMP: Similar to TRANSACTION_TIMESTAMP, returns a value of type TIMESTAMP WITH TIME ZONE that represents the start of the current statement. [Date/time functions]
STDDEV [aggregate]: Evaluates the statistical sample standard deviation for each member of the group. [Aggregate functions]
STDDEV [analytic]: Computes the statistical sample standard deviation of the current row with respect to the group within a. [Analytic functions]
STDDEV_POP [aggregate]: Evaluates the statistical population standard deviation for each member of the group. [Aggregate functions]
STDDEV_POP [analytic]: Evaluates the statistical population standard deviation for each member of the group. [Analytic functions]
STDDEV_SAMP [aggregate]: Evaluates the statistical sample standard deviation for each member of the group. [Aggregate functions]
STDDEV_SAMP [analytic]: Computes the statistical sample standard deviation of the current row with respect to the group within a. [Analytic functions]
STRING_TO_ARRAY: Splits a string containing array values and returns a native one-dimensional array. [Collection functions]
STRPOS: Returns an INTEGER value that represents the location of a specified substring within a string (counting from one). [String functions]
STRPOSB: Returns an INTEGER value representing the location of a specified substring within a string, counting from one, where each octet in the string is counted (as opposed to characters). [String functions]
STV_AsGeoJSON: Returns the geometry or geography argument as a Geometry Javascript Object Notation (GeoJSON) object. [Geospatial functions]
STV_Create_Index: Creates a spatial index on a set of polygons to speed up spatial intersection with a set of points. [Geospatial functions]
STV_Describe_Index: Retrieves information about an index that contains a set of polygons. [Geospatial functions]
STV_Drop_Index: Deletes a spatial index. [Geospatial functions]
STV_DWithin: Determines if the shortest distance from the boundary of one spatial object to the boundary of another object is within a specified distance. [Geospatial functions]
STV_Export2Shapefile: Exports GEOGRAPHY or GEOMETRY data from a database table or a subquery to a shapefile. [Geospatial functions]
STV_Extent: Returns a bounding box containing all of the input data. [Geospatial functions]
STV_ForceLHR: Alters the order of the vertices of a spatial object to follow the left-hand-rule. [Geospatial functions]
STV_Geography: Casts a GEOMETRY object into a GEOGRAPHY object. [Geospatial functions]
STV_GeographyPoint: Returns a GEOGRAPHY point based on the input values. [Geospatial functions]
STV_Geometry: Casts a GEOGRAPHY object into a GEOMETRY object. [Geospatial functions]
STV_GeometryPoint: Returns a GEOMETRY point, based on the input values. [Geospatial functions]
STV_GetExportShapefileDirectory: Returns the path of the export directory. [Geospatial functions]
STV_Intersect scalar function: Spatially intersects a point or points with a set of polygons. [Geospatial functions]
STV_Intersect transform function: Spatially intersects points and polygons. [Geospatial functions]
STV_IsValidReason: Determines if a spatial object is well formed or valid. [Geospatial functions]
STV_LineStringPoint: Retrieves the vertices of a linestring or multilinestring. [Geospatial functions]
STV_MemSize: Returns the length of the spatial object in bytes as an INTEGER. [Geospatial functions]
STV_NN: Calculates the distance of spatial objects from a reference object and returns (object, distance) pairs in ascending order by distance from the reference object. [Geospatial functions]
STV_PolygonPoint: Retrieves the vertices of a polygon as individual points. [Geospatial functions]
STV_Refresh_Index: Appends newly added or updated polygons and removes deleted polygons from an existing spatial index. [Geospatial functions]
STV_Rename_Index: Renames a spatial index. [Geospatial functions]
STV_Reverse: Reverses the order of the vertices of a spatial object. [Geospatial functions]
STV_SetExportShapefileDirectory: Specifies the directory to export GEOMETRY or GEOGRAPHY data to a shapefile. [Geospatial functions]
STV_ShpCreateTable: Returns a CREATE TABLE statement with the columns and types of the attributes found in the specified shapefile. [Geospatial functions]
STV_ShpSource and STV_ShpParser: These two functions work with COPY to parse and load geometries and attributes from a shapefile into a Vertica table, and convert them to the appropriate GEOMETRY data type. [Geospatial functions]
SUBSTR: Returns VARCHAR or VARBINARY value representing a substring of a specified string. [String functions]
SUBSTRB: Returns an octet value representing the substring of a specified string. [String functions]
SUBSTRING: Returns a value representing a substring of the specified string at the given position, given a value, a position, and an optional length. [String functions]
SUM [aggregate]: Computes the sum of an expression over a group of rows. [Aggregate functions]
SUM [analytic]: Computes the sum of an expression over a group of rows within a. [Analytic functions]
SUM_FLOAT [aggregate]: Computes the sum of an expression over a group of rows and returns a DOUBLE PRECISION value. [Aggregate functions]
SUMMARIZE_CATCOL: Returns a statistical summary of categorical data input, in three columns:. [Data preparation]
SUMMARIZE_NUMCOL: Returns a statistical summary of columns in a Vertica table:. [Data preparation]
SVD: Computes singular values (the diagonal of the S matrix) and right singular vectors (the V matrix) of an SVD decomposition of the input relation. [Data preparation]
SVM_CLASSIFIER: Trains the SVM model on an input relation. [Machine learning algorithms]
SVM_REGRESSOR: Trains the SVM model on an input relation. [Machine learning algorithms]
SWAP_PARTITIONS_BETWEEN_TABLES: Swaps partitions between two tables. [Partition functions]
SYNC_CATALOG: Synchronizes the catalog to communal storage to enable reviving the current catalog version in the case of an imminent crash. [Eon Mode functions]
SYNC_WITH_HCATALOG_SCHEMA: Copies the structure of a Hive database schema available through the HCatalog Connector to a Vertica schema. [Hadoop functions]
SYNC_WITH_HCATALOG_SCHEMA_TABLE: Copies the structure of a single table in a Hive database schema available through the HCatalog Connector to a Vertica table. [Hadoop functions]
SYSDATE: Returns the current statement's start date and time as a TIMESTAMP value. [Date/time functions]

T

TAN: Returns a DOUBLE PRECISION value that represents the trigonometric tangent of the passed parameter. [Mathematical functions]
TANH: Returns a DOUBLE PRECISION value that represents the hyperbolic tangent of the passed parameter. [Mathematical functions]
Template patterns for date/time formatting: In an output template string (for TO_CHAR), certain patterns are recognized and replaced with appropriately formatted data from the value to format. [Formatting functions]
Template patterns for numeric formatting: A sign formatted using SG, PL, or MI is not anchored to the number. [Formatting functions]
THROW_ERROR: Returns a user-defined error message. [Error-handling functions]
TIME_SLICE: Aggregates data by different fixed-time intervals and returns a rounded-up input TIMESTAMP value to a value that corresponds with the start or end of the time slice interval. [Date/time functions]
TIMEOFDAY: Returns the wall-clock time as a text string. [Date/time functions]
TIMESTAMP_ROUND: Rounds the specified TIMESTAMP. [Date/time functions]
TIMESTAMP_TRUNC: Truncates the specified TIMESTAMP. [Date/time functions]
TIMESTAMPADD: Adds the specified number of intervals to a TIMESTAMP or TIMESTAMPTZ value and returns a result of the same data type. [Date/time functions]
TIMESTAMPDIFF: Returns the time span between two TIMESTAMP or TIMESTAMPTZ values, in the intervals specified. [Date/time functions]
TO_BITSTRING: This topic is shared in two locations: Formatting Functions and String Functions. [Formatting functions]
TO_CHAR: Converts date/time and numeric values into text strings. [Formatting functions]
TO_DATE: This topic shared in two places: Date/Time functions and Formatting Functions. [Formatting functions]
TO_HEX: This topic is shared in two locations: Formatting Functions and String Functions. [Formatting functions]
TO_JSON: Returns the JSON representation of a complex-type argument, including mixed and nested complex types. [Collection functions]
TO_NUMBER: Converts a string value to DOUBLE PRECISION. [Formatting functions]
TO_TIMESTAMP: Converts a string value or a UNIX/POSIX epoch value to a TIMESTAMP type. [Formatting functions]
TO_TIMESTAMP_TZ: Converts a string value or a UNIX/POSIX epoch value to a TIMESTAMP WITH TIME ZONE type. [Formatting functions]
TRANSACTION_TIMESTAMP: Returns a value of type TIME WITH TIMEZONE that represents the start of the current transaction. [Date/time functions]
TRANSLATE: Replaces individual characters in string_to_replace with other characters. [String functions]
TRIM: Combines the BTRIM, LTRIM, and RTRIM functions into a single function. [String functions]
TRUNC: Truncates the specified date or time. [Date/time functions]
TRUNC: Returns the expression value fully truncated (toward zero). [Mathematical functions]
TS_FIRST_VALUE: Processes the data that belongs to each time slice. [Aggregate functions]
TS_LAST_VALUE: Processes the data that belongs to each time slice. [Aggregate functions]

U

UNNEST: Expands the elements of one or more collection columns (ARRAY or SET) into individual rows. [Collection functions]
UNSANDBOX_SUBCLUSTER: Removes a subcluster from a sandbox. [Eon Mode functions]
UPGRADE_MODEL: Upgrades a model from a previous Vertica version. [Model management]
UPPER: Returns a VARCHAR value containing the argument converted to uppercase letters. [String functions]
UPPERB: Returns a character string with each ASCII character converted to uppercase. [String functions]
URI_PERCENT_DECODE: Decodes a percent-encoded Universal Resource Identifier (URI) according to the RFC 3986 standard. [URI functions]
URI_PERCENT_ENCODE: Encodes a Universal Resource Identifier (URI) according to the RFC 3986 standard for percent encoding. [URI functions]
USER: Returns a VARCHAR containing the name of the user who initiated the current database connection. [System information functions]
USERNAME: Returns a VARCHAR containing the name of the user who initiated the current database connection. [System information functions]
UUID_GENERATE: Returns a new universally unique identifier (UUID) that is generated based on high-quality randomness from /dev/urandom. [UUID functions]

V

V6_ATON: Converts a string containing a colon-delimited IPv6 network address into a VARBINARY string. [IP address functions]
V6_NTOA: Converts an IPv6 address represented as varbinary to a character string. [IP address functions]
V6_SUBNETA: Returns a VARCHAR containing a subnet address in CIDR (Classless Inter-Domain Routing) format from a binary or alphanumeric IPv6 address. [IP address functions]
V6_SUBNETN: Calculates a subnet address in CIDR (Classless Inter-Domain Routing) format from a varbinary or alphanumeric IPv6 address. [IP address functions]
V6_TYPE: Returns an INTEGER value that classifies the type of the network address passed to it as defined in IETF RFC 4291 section 2.4. [IP address functions]
VALIDATE_STATISTICS: Validates statistics in the XML file generated by EXPORT_STATISTICS. [Statistics management functions]
VAR_POP [aggregate]: Evaluates the population variance for each member of the group. [Aggregate functions]
VAR_POP [analytic]: Returns the statistical population variance of a non-null set of numbers (nulls are ignored) in a group within a. [Analytic functions]
VAR_SAMP [aggregate]: Evaluates the sample variance for each row of the group. [Aggregate functions]
VAR_SAMP [analytic]: Returns the sample variance of a non-NULL set of numbers (NULL values in the set are ignored) for each row of the group within a. [Analytic functions]
VARIANCE [aggregate]: Evaluates the sample variance for each row of the group. [Aggregate functions]
VARIANCE [analytic]: Returns the sample variance of a non-NULL set of numbers (NULL values in the set are ignored) for each row of the group within a. [Analytic functions]
VERIFY_HADOOP_CONF_DIR: Verifies that the Hadoop configuration that is used to access HDFS is valid on all Vertica nodes. [Hadoop functions]
VERSION: Returns a VARCHAR containing a Vertica node's version information. [System information functions]

W

WEEK: Returns the week of the year for the specified date as an integer, where the first week begins on the first Sunday on or preceding January 1. [Date/time functions]
WEEK_ISO: Returns the week of the year for the specified date as an integer, where the first week starts on Monday and contains January 4. [Date/time functions]
WIDTH_BUCKET: Constructs equiwidth histograms, in which the histogram range is divided into intervals (buckets) of identical sizes. [Mathematical functions]
WITHIN GROUP ORDER BY clause: Specifies how to sort rows that are grouped by aggregate functions, one of the following:. [Aggregate functions]

X

XGB_CLASSIFIER: Trains an XGBoost model for classification on an input relation. [Machine learning algorithms]
XGB_PREDICTOR_IMPORTANCE: Measures the importance of the predictors in an XGBoost model. [Model evaluation]
XGB_REGRESSOR: Trains an XGBoost model for regression on an input relation. [Machine learning algorithms]

Y

YEAR: Returns an integer that represents the year portion of the specified date. [Date/time functions]
YEAR_ISO: Returns an integer that represents the year portion of the specified date. [Date/time functions]

Z

ZEROIFNULL: Evaluates to 0 if the column is NULL. [NULL-handling functions]

Functions

List of all functions

In this section