HP Vertica Analytics Platform 7.0.x SQL Reference Manual

• Contents
• SQL Overview
  • HP Vertica Support for ANSI SQL Standards
  • Support for Historical Queries
  • Joins
  • Transactions
• System Limits
• SQL Language Elements
  • Keywords and Reserved Words
    • Keywords
    • Reserved Words
  • Identifiers
    • Non-ASCII Characters
  • Literals
    • Number-Type Literals
    • String Literals
      • Character String Literals
        • Single Quotes in a String
        • Standard Conforming Strings and Escape Characters
      • Dollar-Quoted String Literals
      • Unicode String Literals
        • Using Standard Conforming Strings
      • VARBINARY String Literals
      • Extended String Literals
        • Standard Conforming Strings and Escape Characters
        • Identifying Strings That Are Not Standard Conforming
        • Doubled Single Quotes
    • Date/Time Literals
      • Time Zone Values
      • Day of the Week Names
      • Month Names
      • Interval Values
        • Interval-Literal
        • Interval-Qualifier
  • Operators
    • Binary Operators
    • Boolean Operators
    • Comparison Operators
    • Data Type Coercion Operators (CAST)
    • Date/Time Operators
    • Mathematical Operators
    • NULL Operators
    • String Concatenation Operators
  • Expressions
    • Operator Precedence
    • Expression Evaluation Rules
    • Aggregate Expressions
    • CASE Expressions
    • Column References
    • Comments
    • Date/Time Expressions
    • NULL Value
    • Numeric Expressions
  • Predicates
    • BETWEEN-predicate
    • Boolean-Predicate
    • Column-Value-Predicate
    • IN-predicate
    • INTERPOLATE
    • Join-Predicate
    • LIKE-predicate
    • NULL-predicate
• SQL Data Types
  • Binary Data Types
  • Boolean Data Type
  • Character Data Types
    • The Difference Between NULL and NUL
  • Date/Time Data Types
    • Time Zone Abbreviations for Input
    • DATE
    • DATETIME
    • INTERVAL
      • Displaying or Omitting Interval Units in Output
      • Specifying Units on Input
      • How the Interval-Qualifier Affects Output Units
      • Specifying Precision
      • Casting with Intervals
      • Processing Signed Intervals
      • Processing Interval-Literals Without Units
      • Using INTERVALYM for INTERVAL YEAR TO MONTH
      • Operations with Intervals
      • Fractional Seconds in Interval Units
      • Interval-Literal
      • Interval-Qualifier
    • SMALLDATETIME
    • TIME
    • TIME AT TIME ZONE
    • TIMESTAMP
    • TIMESTAMP AT TIME ZONE
  • Long Data Types
  • Numeric Data Types
    • DOUBLE PRECISION (FLOAT)
    • INTEGER
    • NUMERIC
      • Numeric Data Type Overflow
  • Data Type Coercion
  • Data Type Coercion Chart
• SQL Functions
  • Aggregate Functions
    • APPROXIMATE_COUNT_DISTINCT
    • APPROXIMATE_COUNT_DISTINCT_OF_SYNOPSIS
    • APPROXIMATE_COUNT_DISTINCT_SYNOPSIS
    • AVG [Aggregate]
    • BIT_AND
    • BIT_OR
    • BIT_XOR
    • CORR
    • COUNT [Aggregate]
    • COVAR_POP
    • COVAR_SAMP
    • MAX [Aggregate]
    • MIN [Aggregate]
    • REGR_AVGX
    • REGR_AVGY
    • REGR_COUNT
    • REGR_INTERCEPT
    • REGR_R2
    • REGR_SLOPE
    • REGR_SXX
    • REGR_SXY
    • REGR_SYY
    • STDDEV [Aggregate]
    • STDDEV_POP [Aggregate]
    • STDDEV_SAMP [Aggregate]
    • SUM [Aggregate]
    • SUM_FLOAT [Aggregate]
    • VAR_POP [Aggregate]
    • VAR_SAMP [Aggregate]
    • VARIANCE [Aggregate]
  • Analytic Functions
    • Analytic Function Syntax
    • Analytic Syntactic Construct
    • window_partition_clause
    • window_order_clause
    • window_frame_clause
      • Window Aggregates
    • named_windows
    • AVG [Analytic]
    • CONDITIONAL_CHANGE_EVENT [Analytic]
    • CONDITIONAL_TRUE_EVENT [Analytic]
    • COUNT [Analytic]
    • CUME_DIST [Analytic]
    • DENSE_RANK [Analytic]
    • EXPONENTIAL_MOVING_AVERAGE [Analytic]
    • FIRST_VALUE [Analytic]
    • LAG [Analytic]
    • LAST_VALUE [Analytic]
    • LEAD [Analytic]
    • MAX [Analytic]
    • MEDIAN [Analytic]
    • MIN [Analytic]
    • NTILE [Analytic]
    • PERCENT_RANK [Analytic]
    • PERCENTILE_CONT [Analytic]
    • PERCENTILE_DISC [Analytic]
    • RANK [Analytic]
    • ROW_NUMBER [Analytic]
    • STDDEV [Analytic]
    • STDDEV_POP [Analytic]
    • STDDEV_SAMP [Analytic]
    • SUM [Analytic]
    • VAR_POP [Analytic]
    • VAR_SAMP [Analytic]
    • VARIANCE [Analytic]
  • Date/Time Functions
    • Usage
    • Daylight Savings Time Considerations
    • Date/Time Functions in Transactions
    • ADD_MONTHS
    • AGE_IN_MONTHS
    • AGE_IN_YEARS
    • CLOCK_TIMESTAMP
    • CURRENT_DATE
    • CURRENT_TIME
    • CURRENT_TIMESTAMP
    • DATE_PART
    • DATE
    • DATE_TRUNC
    • DATEDIFF
    • DAY
    • DAYOFMONTH
    • DAYOFWEEK
    • DAYOFWEEK_ISO
    • DAYOFYEAR
    • DAYS
    • EXTRACT
    • GETDATE
    • GETUTCDATE
    • HOUR
    • ISFINITE
    • JULIAN_DAY
    • LAST_DAY
    • LOCALTIME
    • LOCALTIMESTAMP
    • MICROSECOND
    • MIDNIGHT_SECONDS
    • MINUTE
    • MONTH
    • MONTHS_BETWEEN
    • NEW_TIME
    • NEXT_DAY
    • NOW [Date/Time]
    • OVERLAPS
    • QUARTER
    • ROUND [Date/Time]
    • SECOND
    • STATEMENT_TIMESTAMP
    • SYSDATE
    • TIME_SLICE
    • TIMEOFDAY
    • TIMESTAMPADD
    • TIMESTAMPDIFF
    • TIMESTAMP_ROUND
    • TIMESTAMP_TRUNC
    • TRANSACTION_TIMESTAMP
    • TRUNC [Date/Time]
    • WEEK
    • WEEK_ISO
    • YEAR
    • YEAR_ISO
  • Formatting Functions
    • TO_BITSTRING
    • TO_CHAR
    • TO_DATE
    • TO_HEX
    • TO_TIMESTAMP
    • TO_TIMESTAMP_TZ
    • TO_NUMBER
    • Template Patterns for Date/Time Formatting
      • Template Pattern Modifiers for Date/Time Formatting
    • Template Patterns for Numeric Formatting
  • Geospatial Package SQL Functions
    • To Install the Geospatial package:
    • Contents of the Geospatial Package
    • Using Geospatial Package SQL Functions
    • Using Built-In HP Vertica Functions for Geospatial Analysis
    • Geospatial SQL Functions
      • WGS-84 SQL Functions
      • Earth Radius, Radius of Curvature, and Bearing SQL Functions
      • ECEF Conversion SQL Functions
      • Bounding Box SQL Functions
      • Miles/Kilometer Conversion SQL Functions
    • BB_WITHIN
    • BEARING
    • CHORD_TO_ARC
    • DWITHIN
    • ECEF_CHORD
    • ECEF_x
    • ECEF_y
    • ECEF_z
    • ISLEFT
    • KM2MILES
    • LAT_WITHIN
    • LL_WITHIN
    • LLD_WITHIN
    • LON_WITHIN
    • MILES2KM
    • RADIUS_LON
    • RADIUS_M
    • RADIUS_N
    • RADIUS_R
    • RADIUS_Ra
    • RADIUS_Rc
    • RADIUS_Rv
    • RADIUS_SI
    • RAYCROSSING
    • WGS84_a
    • WGS84_b
    • WGS84_e2
    • WGS84_f
    • WGS84_if
    • WGS84_r1
  • IP Conversion Functions
    • INET_ATON
    • INET_NTOA
    • V6_ATON
    • V6_NTOA
    • V6_SUBNETA
    • V6_SUBNETN
    • V6_TYPE
  • Mathematical Functions
    • ABS
    • ACOS
    • ASIN
    • ATAN
    • ATAN2
    • CBRT
    • CEILING (CEIL)
    • COS
    • COT
    • DEGREES
    • DISTANCE
    • DISTANCEV
    • EXP
    • FLOOR
    • HASH
    • LN
    • LOG
    • MOD
    • MODULARHASH
    • PI
    • POWER (or POW)
    • RADIANS
    • RANDOM
    • RANDOMINT
    • ROUND
    • SIGN
    • SIN
    • SQRT
    • TAN
    • TRUNC
    • WIDTH_BUCKET
  • NULL-handling Functions
    • COALESCE
    • IFNULL
    • ISNULL
    • NULLIF
    • NULLIFZERO
    • NVL
    • NVL2
    • ZEROIFNULL
  • Pattern Matching Functions
    • EVENT_NAME
    • MATCH_ID
    • PATTERN_ID
  • Regular Expression Functions
    • ISUTF8
    • REGEXP_COUNT
    • REGEXP_INSTR
    • REGEXP_LIKE
    • REGEXP_REPLACE
    • REGEXP_SUBSTR
  • Sequence Functions
    • NEXTVAL
    • CURRVAL
    • LAST_INSERT_ID
  • String Functions
    • ASCII
    • BIT_LENGTH
    • BITCOUNT
    • BITSTRING_TO_BINARY
    • BTRIM
    • CHARACTER_LENGTH
    • CHR
    • CONCAT
    • DECODE
    • GREATEST
    • GREATESTB
    • HEX_TO_BINARY
    • HEX_TO_INTEGER
    • INET_ATON
    • INET_NTOA
    • INITCAP
    • INITCAPB
    • INSERT
    • INSTR
    • INSTRB
    • ISUTF8
    • LEAST
    • LEASTB
    • LEFT
    • LENGTH
    • LOWER
    • LOWERB
    • LPAD
    • LTRIM
    • MD5
    • OCTET_LENGTH
    • OVERLAY
    • OVERLAYB
    • POSITION
    • POSITIONB
    • QUOTE_IDENT
    • QUOTE_LITERAL
    • REPEAT
    • REPLACE
    • RIGHT
    • RPAD
    • RTRIM
    • SPACE
    • SPLIT_PART
    • SPLIT_PARTB
    • STRPOS
    • STRPOSB
    • SUBSTR
    • SUBSTRB
    • SUBSTRING
    • TO_BITSTRING
    • TO_HEX
    • TRANSLATE
    • TRIM
    • UPPER
    • UPPERB
    • V6_ATON
    • V6_NTOA
    • V6_SUBNETA
    • V6_SUBNETN
    • V6_TYPE
  • System Information Functions
    • CURRENT_DATABASE
    • CURRENT_SCHEMA
    • CURRENT_USER
    • DBNAME (function)
    • HAS_TABLE_PRIVILEGE
    • SESSION_USER
    • USER
    • USERNAME
    • VERSION
  • Timeseries Functions
    • TS_FIRST_VALUE
    • TS_LAST_VALUE
  • URI Encode/Decode Functions
    • URI_PERCENT_DECODE
    • URI_PERCENT_ENCODE
  • HP Vertica Meta-Functions
    • Alphabetical List of HP Vertica Meta-Functions
      • ADD_LOCATION
        • Storage Location Subdirectories
      • ADVANCE_EPOCH
      • ALTER_LOCATION_USE
        • USER Storage Location Restrictions
        • Monitoring Storage Locations
      • ALTER_LOCATION_LABEL
      • ANALYZE_CONSTRAINTS
        • Detecting Constraint Violations During a Load Process
        • Understanding Function Failures
      • ANALYZE_CORRELATIONS
      • ANALYZE_HISTOGRAM
      • ANALYZE_STATISTICS
      • ANALYZE_WORKLOAD
      • AUDIT
      • AUDIT_FLEX
      • AUDIT_LICENSE_SIZE
      • AUDIT_LICENSE_TERM
      • BUILD_FLEXTABLE_VIEW
      • CANCEL_REBALANCE_CLUSTER
      • CANCEL_REFRESH
      • CHANGE_CURRENT_STATEMENT_RUNTIME_PRIORITY
      • CHANGE_RUNTIME_PRIORITY
      • CLEAR_CACHES
      • CLEAR_DATA_COLLECTOR
      • CLEAR_PROFILING
      • CLEAR_PROJECTION_REFRESHES
      • CLEAR_RESOURCE_REJECTIONS
      • CLEAR_OBJECT_STORAGE_POLICY
      • CLOSE_SESSION
        • Controlling Sessions
      • CLOSE_ALL_SESSIONS
        • Controlling Sessions
      • COMPUTE_FLEXTABLE_KEYS
      • COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW
      • CURRENT_SCHEMA
      • DATA_COLLECTOR_HELP
      • DISABLE_DUPLICATE_KEY_ERROR
      • DISABLE_ELASTIC_CLUSTER
      • DISABLE_LOCAL_SEGMENTS
      • DISABLE_PROFILING
      • DISPLAY_LICENSE
      • DO_TM_TASK
      • DROP_LICENSE
      • DROP_LOCATION
        • Retiring or Dropping a Storage Location
        • Storage Locations with Temp and Data Files
      • DROP_PARTITION
      • DROP_STATISTICS
      • DUMP_CATALOG
      • DUMP_LOCKTABLE
      • DUMP_PARTITION_KEYS
      • DUMP_PROJECTION_PARTITION_KEYS
      • DUMP_TABLE_PARTITION_KEYS
      • ENABLE_ELASTIC_CLUSTER
      • ENABLE_LOCAL_SEGMENTS
      • ENABLE_PROFILING
      • EVALUATE_DELETE_PERFORMANCE
      • EXPORT_CATALOG
      • EXPORT_OBJECTS
      • EXPORT_STATISTICS
      • EXPORT_TABLES
      • FLUSH_DATA_COLLECTOR
      • GET_AHM_EPOCH
      • GET_AHM_TIME
      • GET_AUDIT_TIME
      • GET_COMPLIANCE_STATUS
      • GET_CURRENT_EPOCH
      • GET_DATA_COLLECTOR_POLICY
      • GET_LAST_GOOD_EPOCH
      • GET_NUM_ACCEPTED_ROWS
      • GET_NUM_REJECTED_ROWS
      • GET_PROJECTION_STATUS
      • GET_PROJECTIONS, GET_TABLE_PROJECTIONS
      • HAS_ROLE
      • IMPORT_STATISTICS
      • INTERRUPT_STATEMENT
      • INSTALL_LICENSE
      • LAST_INSERT_ID
      • MAKE_AHM_NOW
      • MARK_DESIGN_KSAFE
      • MATERIALIZE_FLEXTABLE_COLUMNS
      • MEASURE_LOCATION_PERFORMANCE
      • MERGE_PARTITIONS
      • MOVE_PARTITIONS_TO_TABLE
      • PARTITION_PROJECTION
      • PARTITION_TABLE
      • PURGE
      • PURGE_PARTITION
      • PURGE_PROJECTION
      • PURGE_TABLE
      • REALIGN_CONTROL_NODES
      • REBALANCE_CLUSTER
      • REENABLE_DUPLICATE_KEY_ERROR
      • REFRESH
      • RELEASE_ALL_JVM_MEMORY
      • RELEASE_JVM_MEMORY
      • RELOAD_SPREAD
      • RESET_LOAD_BALANCE_POLICY
      • RESTORE_LOCATION
        • Effects of Restoring a Previously Retired Location
        • Monitoring Storage Locations
      • RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW
      • RETIRE_LOCATION
        • Effects of Retiring a Storage Location
        • Monitoring Storage Locations
      • SET_AHM_EPOCH
      • SET_AHM_TIME
      • SET_AUDIT_TIME
      • SET_CONTROL_SET_SIZE
      • SET_DATA_COLLECTOR_POLICY
      • SET_DATA_COLLECTOR_TIME_POLICY
      • SET_LOAD_BALANCE_POLICY
      • SET_LOCATION_PERFORMANCE
      • SET_SCALING_FACTOR
      • SET_OBJECT_STORAGE_POLICY
        • New Storage Policy
        • Existing Storage Policy
        • Forcing Existing Data Storage to a New Storage Location
      • SHUTDOWN
      • SLEEP
      • START_REBALANCE_CLUSTER
      • START_REFRESH
      • SYNCH_WITH_HCATALOG_SCHEMA
    • Catalog Management Functions
      • DROP_LICENSE
      • DUMP_CATALOG
      • EXPORT_CATALOG
      • EXPORT_OBJECTS
      • INSTALL_LICENSE
      • MARK_DESIGN_KSAFE
      • SYNCH_WITH_HCATALOG_SCHEMA
    • Client Connection Management Functions
      • SET_LOAD_BALANCE_POLICY
      • RESET_LOAD_BALANCE_POLICY
    • Cluster Management Functions
      • SET_CONTROL_SET_SIZE
      • REALIGN_CONTROL_NODES
      • RELOAD_SPREAD
      • REBALANCE_CLUSTER
    • Cluster Scaling Functions
      • CANCEL_REBALANCE_CLUSTER
      • DISABLE_ELASTIC_CLUSTER
      • DISABLE_LOCAL_SEGMENTS
      • ENABLE_ELASTIC_CLUSTER
      • ENABLE_LOCAL_SEGMENTS
      • REBALANCE_CLUSTER
      • SET_SCALING_FACTOR
      • START_REBALANCE_CLUSTER
    • Constraint Management Functions
      • ANALYZE_CONSTRAINTS
        • Detecting Constraint Violations During a Load Process
        • Understanding Function Failures
      • ANALYZE_CORRELATIONS
      • DISABLE_DUPLICATE_KEY_ERROR
      • LAST_INSERT_ID
      • REENABLE_DUPLICATE_KEY_ERROR
    • Data Collector Functions
      • Related Topics
      • CLEAR_DATA_COLLECTOR
      • DATA_COLLECTOR_HELP
      • FLUSH_DATA_COLLECTOR
      • GET_DATA_COLLECTOR_POLICY
      • SET_DATA_COLLECTOR_POLICY
      • SET_DATA_COLLECTOR_TIME_POLICY
    • Database Designer Functions
      • DESIGNER_ADD_DESIGN_QUERIES
      • DESIGNER_ADD_DESIGN_QUERIES_FROM_RESULTS
      • DESIGNER_ADD_DESIGN_QUERY
      • DESIGNER_ADD_DESIGN_TABLES
      • DESIGNER_CANCEL_POPULATE_DESIGN
      • DESIGNER_CREATE_DESIGN
      • DESIGNER_DESIGN_PROJECTION_ENCODINGS
      • DESIGNER_DROP_ALL_DESIGNS
      • DESIGNER_DROP_DESIGN
      • DESIGNER_OUTPUT_ALL_DESIGN_PROJECTIONS
      • DESIGNER_OUTPUT_DEPLOYMENT_SCRIPT
      • DESIGNER_RESET_DESIGN
      • DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY
      • DESIGNER_SET_ANALYZE_CORRELATIONS_MODE
      • DESIGNER_SET_DESIGN_KSAFETY
      • DESIGNER_SET_DESIGN_TYPE
      • DESIGNER_SET_OPTIMIZATION_OBJECTIVE
      • DESIGNER_SET_PROPOSE_UNSEGMENTED_PROJECTIONS
      • DESIGNER_WAIT_FOR_DESIGN
    • Database Management Functions
      • CLEAR_RESOURCE_REJECTIONS
      • DUMP_LOCKTABLE
      • DUMP_PARTITION_KEYS
      • EXPORT_TABLES
      • HAS_ROLE
      • SET_CONFIG_PARAMETER
      • SHUTDOWN
    • Epoch Management Functions
      • ADVANCE_EPOCH
      • GET_AHM_EPOCH
      • GET_AHM_TIME
      • GET_CURRENT_EPOCH
      • GET_LAST_GOOD_EPOCH
      • MAKE_AHM_NOW
      • SET_AHM_EPOCH
      • SET_AHM_TIME
    • Flex Table Functions
      • COMPUTE_FLEXTABLE_KEYS
      • COMPUTE_FLEXTABLE_KEYS
      • COMPUTE_FLEXTABLE_KEYS_AND_BUILD_VIEW
      • MATERIALIZE_FLEXTABLE_COLUMNS
      • RESTORE_FLEXTABLE_DEFAULT_KEYS_TABLE_AND_VIEW
    • License Management Functions
      • AUDIT
      • AUDIT_FLEX
      • AUDIT_LICENSE_SIZE
      • AUDIT_LICENSE_TERM
      • GET_AUDIT_TIME
      • GET_COMPLIANCE_STATUS
      • DISPLAY_LICENSE
      • SET_AUDIT_TIME
    • Partition Management Functions
      • DROP_PARTITION
      • DUMP_PROJECTION_PARTITION_KEYS
      • DUMP_TABLE_PARTITION_KEYS
      • MERGE_PARTITIONS
      • MOVE_PARTITIONS_TO_TABLE
      • PARTITION_PROJECTION
      • PARTITION_TABLE
      • PURGE_PARTITION
    • Profiling Functions
      • CLEAR_PROFILING
      • DISABLE_PROFILING
      • ENABLE_PROFILING
    • Projection Management Functions
      • EVALUATE_DELETE_PERFORMANCE
      • GET_PROJECTION_STATUS
      • GET_PROJECTIONS, GET_TABLE_PROJECTIONS
      • REFRESH
      • START_REFRESH
    • Purge Functions
      • PURGE
      • PURGE_PARTITION
      • PURGE_PROJECTION
      • PURGE_TABLE
    • Session Management Functions
      • CANCEL_REFRESH
      • CLOSE_ALL_SESSIONS
        • Controlling Sessions
      • CLOSE_SESSION
        • Controlling Sessions
      • GET_NUM_ACCEPTED_ROWS
      • GET_NUM_REJECTED_ROWS
      • INTERRUPT_STATEMENT
      • RELEASE_ALL_JVM_MEMORY
      • RELEASE_JVM_MEMORY
    • Statistic Management Functions
      • ANALYZE_HISTOGRAM
      • ANALYZE_STATISTICS
      • DROP_STATISTICS
      • EXPORT_STATISTICS
      • IMPORT_STATISTICS
    • Storage Management Functions
      • ADD_LOCATION
        • Storage Location Subdirectories
      • ALTER_LOCATION_USE
        • USER Storage Location Restrictions
        • Monitoring Storage Locations
      • ALTER_LOCATION_LABEL
      • CLEAR_CACHES
      • CLEAR_OBJECT_STORAGE_POLICY
      • DROP_LOCATION
        • Retiring or Dropping a Storage Location
        • Storage Locations with Temp and Data Files
      • MEASURE_LOCATION_PERFORMANCE
      • RESTORE_LOCATION
        • Effects of Restoring a Previously Retired Location
        • Monitoring Storage Locations
      • RETIRE_LOCATION
        • Effects of Retiring a Storage Location
        • Monitoring Storage Locations
      • SET_LOCATION_PERFORMANCE
      • SET_OBJECT_STORAGE_POLICY
        • New Storage Policy
        • Existing Storage Policy
        • Forcing Existing Data Storage to a New Storage Location
    • Tuple Mover Functions
      • DO_TM_TASK
    • Workload Management Functions
      • ANALYZE_WORKLOAD
      • CHANGE_CURRENT_STATEMENT_RUNTIME_PRIORITY
      • CHANGE_RUNTIME_PRIORITY
      • CLEAR_CACHES
      • SLEEP
• SQL Statements
  • ALTER DATABASE
  • ALTER FAULT GROUP
  • ALTER FUNCTION
  • ALTER LIBRARY
  • ALTER NODE
  • ALTER NETWORK INTERFACE
  • ALTER PROJECTION RENAME
  • ALTER PROFILE
  • ALTER PROFILE RENAME
  • ALTER RESOURCE POOL
  • ALTER ROLE RENAME
  • ALTER SCHEMA
  • ALTER SEQUENCE
  • ALTER SUBNET
  • ALTER TABLE
    • Table Behavior After Alteration
    • Changing a Data Type for a Column Specified in a SEGMENTED BY Clause
    • Locked Tables
    • Adding and Changing Constraints on Columns Using ALTER TABLE
    • Adding and Dropping NOT NULL Column Constraints
    • Table-Constraint
      • Specifying Primary and Foreign Keys
      • Adding Constraints to Views
  • ALTER USER
    • Database Account Changes Users Can Make
  • ALTER VIEW
  • BEGIN
  • COMMENT ON Statements
    • COMMENT ON COLUMN
    • COMMENT ON CONSTRAINT
    • COMMENT ON FUNCTION
    • COMMENT ON LIBRARY
    • COMMENT ON NODE
    • COMMENT ON PROJECTION
    • COMMENT ON SCHEMA
    • COMMENT ON SEQUENCE
    • COMMENT ON TABLE
    • COMMENT ON TRANSFORM FUNCTION
    • COMMENT ON VIEW
  • COMMIT
  • CONNECT
    • Connection Details
  • COPY
    • COPY Option Summary
      • Setting vsql Variables
      • Using Compressed Data and Named Pipes
  • COPY LOCAL
    • How Copy Local Works
    • Viewing Copy Local Operations in a Query Plan
  • COPY FROM VERTICA
    • Source and Destination Column Mapping
  • CREATE EXTERNAL TABLE AS COPY
  • CREATE FAULT GROUP
  • CREATE FLEX TABLE
    • Unsupported CREATE Options for Flex Tables
    • Default Flex Table and Keys Table Projections
  • CREATE FLEX EXTERNAL TABLE AS COPY
  • CREATE FUNCTION Statements
    • About Creating User Defined Transform Functions (UDTFs)
    • CREATE AGGREGATE FUNCTION
    • CREATE ANALYTIC FUNCTION
    • CREATE FILTER
    • CREATE FUNCTION (SQL Functions)
    • CREATE FUNCTION (UDF)
    • CREATE PARSER
    • CREATE SOURCE
    • CREATE TRANSFORM FUNCTION
      • UDTF Query Restrictions
  • CREATE HCATALOG SCHEMA
  • CREATE LIBRARY
  • CREATE LOCAL TEMPORARY VIEW
  • CREATE NETWORK INTERFACE
  • CREATE PROCEDURE
  • CREATE PROFILE
  • CREATE PROJECTION
    • Projections and Superprojections
    • Checking Column Constraints
    • Updating the Projection Using Refresh
    • Creating Unsegmented Projections with the ALL NODES Option
    • Encoding-Type
      • ENCODING AUTO (default)
      • ENCODING BLOCK_DICT
      • ENCODING BLOCKDICT_COMP
      • ENCODING COMMONDELTA_COMP
      • ENCODING DELTARANGE_COMP
      • ENCODING DELTAVAL
      • ENCODING GCDDELTA
      • ENCODING RLE
      • ENCODING NONE
    • Hash-Segmentation-Clause
  • CREATE RESOURCE POOL
    • Permissions
    • Built-In Pools
      • Built-In Pool
      • Settings
      • Upgrade From Earlier Versions of HP Vertica
    • Built-In Pool Configuration
      • GENERAL
      • SYSQUERY
      • SYSDATA
      • WOSDATA
      • TM
      • REFRESH
      • RECOVERY
      • DBD
      • JVM
  • CREATE ROLE
  • CREATE SCHEMA
  • CREATE SEQUENCE
    • Incrementing and Obtaining Sequence Values
    • Removing a Sequence
  • CREATE SUBNET
  • CREATE TABLE
    • Automatic Projection Creation
    • Partition Clauses
    • Column-Definition (table)
    • Column-Name-List (table)
    • Column-Constraint
    • Table-Constraint
      • Specifying Primary and Foreign Keys
      • Adding Constraints to Views
    • Hash-Segmentation-Clause (table)
  • CREATE TEMPORARY TABLE
    • Column-Definition (temp table)
    • Column-Name-List (temp table)
    • Hash-Segmentation-Clause (temp table)
  • CREATE USER
  • CREATE VIEW
    • Transforming a SELECT Query to Use a View
    • Dropping a View
    • Renaming a View
  • DELETE
    • Using the DELETE Statement
  • DISCONNECT
  • DROP AGGREGATE FUNCTION
  • DROP FAULT GROUP
  • DROP FUNCTION
    • DROP SOURCE
    • DROP FILTER
    • DROP PARSER
  • DROP LIBRARY
  • DROP NETWORK INTERFACE
  • DROP PROCEDURE
  • DROP PROFILE
  • DROP PROJECTION
  • DROP RESOURCE POOL
    • Transferring Resource Requests
  • DROP ROLE
  • DROP SCHEMA
  • DROP SEQUENCE
  • DROP SUBNET
  • DROP TABLE
  • DROP TRANSFORM FUNCTION
  • DROP USER
  • DROP VIEW
  • END
  • EXPLAIN
  • EXPORT TO VERTICA
    • Source and Destination Column Mapping
  • GRANT Statements
    • GRANT (Database)
    • GRANT (Procedure)
    • GRANT (Resource Pool)
    • GRANT (Role)
      • Creating Roles
      • Activating a Role
      • Granting One Role To Another
      • Checking for Circular References
      • Granting Administrative Privileges
    • GRANT (Schema)
    • GRANT (Sequence)
    • GRANT (Storage Location)
    • GRANT (Table)
    • GRANT (User Defined Extension)
    • GRANT (View)
  • INSERT
  • MERGE
    • Using Named Sequences
    • Improving MERGE Performance
  • PROFILE
    • Real-Time Profiling Example
    • How to Use the Linux watch Command
    • How to Find Out Which Counters are Available
  • RELEASE SAVEPOINT
  • REVOKE Statements
    • REVOKE (Database)
    • REVOKE (Procedure)
    • REVOKE (Resource Pool)
    • REVOKE (Role)
    • REVOKE (Schema)
    • REVOKE (Sequence)
    • REVOKE (Storage Location)
    • REVOKE (Table)
    • REVOKE (User Defined Extension)
    • REVOKE (View)
  • ROLLBACK
  • ROLLBACK TO SAVEPOINT
  • SAVEPOINT
  • SELECT
    • Parameters
    • EXCEPT Clause
    • FROM Clause
      • Table-Reference
      • Table-Primary
      • Joined-Table
    • GROUP BY Clause
    • HAVING Clause
    • INTERSECT Clause
    • INTO Clause
    • LIMIT Clause
    • MATCH Clause
      • Pattern Semantic Evaluation
    • MINUS Clause
    • OFFSET Clause
    • ORDER BY Clause
    • TIMESERIES Clause
    • UNION Clause
    • WHERE Clause
    • WINDOW Clause
    • WITH Clause
  • SET DATESTYLE
  • SET ESCAPE_STRING_WARNING
  • SET INTERVALSTYLE
    • Output Intervals with Units
    • Output Intervals Without Units
    • Displaying the Current Interval OUTPUT Style
  • SET LOCALE
  • SET ROLE
  • SET SEARCH_PATH
  • SET SESSION AUTOCOMMIT
  • SET SESSION CHARACTERISTICS
    • Understanding READ COMMITTED and Snapshot Isolation
    • Using SERIALIZABLE Transaction Isolation
    • Setting READ ONLY Transaction Mode
  • SET SESSION MEMORYCAP
  • SET SESSION RESOURCE_POOL
  • SET SESSION RUNTIMECAP
  • SET SESSION TEMPSPACECAP
  • SET STANDARD_CONFORMING_STRINGS
  • SET TIME ZONE
    • Time Zone Names for Setting TIME ZONE
  • SHOW
    • How to Display All Current Run-Time Parameter Settings
    • Displaying Current Search Path Settings
    • Displaying the Transaction Isolation Level
  • START TRANSACTION
  • TRUNCATE TABLE
  • UPDATE
• HP Vertica System Tables
  • V_CATALOG Schema
    • ALL_TABLES
    • CLUSTER_LAYOUT
    • COLUMNS
    • COMMENTS
    • CONSTRAINT_COLUMNS
    • DATABASES
    • DUAL
    • ELASTIC_CLUSTER
    • EPOCHS
    • FAULT_GROUPS
    • FOREIGN_KEYS
    • GRANTS
    • HCATALOG_COLUMNS
    • HCATALOG_SCHEMATA
    • HCATALOG_TABLES
    • HCATALOG_TABLE_LIST
    • LARGE_CLUSTER_CONFIGURATION_STATUS
    • LICENSE_AUDITS
    • LICENSES
    • MATERIALIZE_FLEXTABLE_COLUMNS_RESULTS
    • NODES
    • ODBC_COLUMNS
    • PASSWORDS
    • PRIMARY_KEYS
    • PROFILE_PARAMETERS
    • PROFILES
    • PROJECTION_CHECKPOINT_EPOCHS
    • PROJECTION_COLUMNS
    • PROJECTION_DELETE_CONCERNS
    • PROJECTIONS
    • RESOURCE_POOL_DEFAULTS
    • RESOURCE_POOLS
    • ROLES
    • SCHEMATA
    • SEQUENCES
    • STORAGE_LOCATIONS
    • SYSTEM_COLUMNS
    • SYSTEM_TABLES
    • TABLE_CONSTRAINTS
    • TABLES
    • TYPES
    • USER_AUDITS
    • USER_FUNCTIONS
    • USER_PROCEDURES
    • USER_TRANSFORMS
    • USERS
    • VIEW_COLUMNS
    • VIEWS
  • V_MONITOR Schema
    • ACTIVE_EVENTS
    • COLUMN_STORAGE
    • CONFIGURATION_CHANGES
    • CONFIGURATION_PARAMETERS
    • CPU_USAGE
    • CRITICAL_HOSTS
    • CRITICAL_NODES
    • CURRENT_SESSION
    • DATA_COLLECTOR
    • DATABASE_BACKUPS
    • DATABASE_CONNECTIONS
    • DATABASE_SNAPSHOTS
    • DELETE_VECTORS
    • DEPLOY_STATUS
    • DEPLOYMENT_PROJECTION_STATEMENTS
    • DEPLOYMENT_PROJECTIONS
    • DESIGN_QUERIES
    • DESIGN_STATUS
    • DESIGN_TABLES
    • DESIGNS
    • DISK_RESOURCE_REJECTIONS
    • DISK_STORAGE
    • ERROR_MESSAGES
    • EVENT_CONFIGURATIONS
    • EXECUTION_ENGINE_PROFILES
    • HOST_RESOURCES
    • IO_USAGE
    • LOAD_STREAMS
    • LOCK_USAGE
    • LOCKS
    • LOGIN_FAILURES
    • MEMORY_USAGE
    • MONITORING_EVENTS
    • NETWORK_INTERFACES
    • NETWORK_USAGE
    • NODE_RESOURCES
    • NODE_STATES
    • OUTPUT_DEPLOYMENT_STATUS
    • OUTPUT_EVENT_HISTORY
    • PARTITION_REORGANIZE_ERRORS
    • PARTITION_STATUS
    • PARTITIONS
    • PROCESS_SIGNALS
    • PROJECTION_RECOVERIES
    • PROJECTION_REFRESHES
    • PROJECTION_STORAGE
    • PROJECTION_USAGE
    • QUERY_EVENTS
    • QUERY_METRICS
    • QUERY_PLAN_PROFILES
    • QUERY_PROFILES
    • QUERY_REQUESTS
    • REBALANCE_PROJECTION_STATUS
    • REBALANCE_TABLE_STATUS
    • RECOVERY_STATUS
    • RESOURCE_ACQUISITIONS
    • RESOURCE_POOL_STATUS
    • RESOURCE_QUEUES
    • RESOURCE_REJECTION_DETAILS
    • RESOURCE_REJECTIONS
    • RESOURCE_USAGE
    • SESSION_PROFILES
    • SESSIONS
    • STORAGE_CONTAINERS
    • STORAGE_POLICIES
    • STORAGE_TIERS
    • STORAGE_USAGE
    • STRATA
    • STRATA_STRUCTURES
    • SYSTEM
    • SYSTEM_RESOURCE_USAGE
    • SYSTEM_SERVICES
    • SYSTEM_SESSIONS
    • TRANSACTIONS
    • TUNING_RECOMMENDATIONS
    • TUPLE_MOVER_OPERATIONS
    • UDX_FENCED_PROCESSES
    • USER_LIBRARIES
    • USER_LIBRARY_MANIFEST
    • USER_SESSIONS
    • WOS_CONTAINER_STORAGE
• Appendix: Compatibility with Other RDBMS
  • Data Type Mappings Between HP Vertica and Oracle
• We appreciate your feedback!