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]
CHECK_CLUSTER_HEALTH
Checks the health of the cluster. [Management 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]