Vertica SQL
Exploring information about tables and columns
Vertica has virtual tables that contain information about the actual data tables. Use them like this:
SELECT * FROM TABLES; -- shows you information about all tables SELECT column_name, data_type FROM COLUMNS WHERE table_name = '<TABLENAME>'; -- shows you the data types of the table <TABLENAME>
Differences in Vertica SQL compared to other SQL dialects
There are no column tables, just row tables.
There is no NVARCHAR, use VARCHAR instead.
There is no DECIMAL or NUMERIC, use FLOAT instead.
There is a DROP TABLE IF EXISTS and DROP VIEW IF EXISTS statement, so there's no need to use a stored procedure or another more complicated check.
No table or view that exists at the end of the job execution can contain a column with the data type TIME, because these cannot be exported to Parquet, which we need for the engine load.
Use DATETIME columns for the date and time of an event (not TIMESTAMP).
days_between becomes datediff(dd, start, end).
Date null checks work differently (see below).
Date concatenation works differently (see below).
Inserting hard-coded values in a UNION chain has a special syntax (see below).
UPDATE statements don't take the table to be updated in the FROM clause (see below).
Inserting multiple rows at once works differently (see below).
Date null checks
These checks work differently compared to HANA:
COALESCE(date_column, '') <> '' -- does not work because in Vertica the parameters of the COALESCE function need to have the same data type date_column IS NOT NULL -- this works
Date concatenation
To concatenate date and time (especially relevant for SAP tables) you need to use the following syntax:
-- Generic CAST(date_column AS DATE) + CAST(time_column AS TIME) AS EVENTTIME -- normal CAST((date_column + day_column * INTERVAL '1 day') AS DATE) + CAST(time_column AS TIME) AS EVENTTIME -- with days added -- Examples CAST(B.BLDAT AS DATE) + CAST('00:00:00' AS TIME) AS EVENTTIME -- normal CAST((B."ZFBDT" + B."ZBD1T" * INTERVAL '1 day') AS DATE) + CAST('23:59:59' AS TIME) AS EVENTTIME -- with days added
Inserting hard-coded values in a UNION chain
To add hardcoded values into a table within a UNION chain, use this syntax. A FROM statement is not needed.
... UNION SELECT 'text1' AS col1 ,'text2' AS col2 ,'text3' AS col3 UNION ...
UPDATE Statements
Updating doesn't take the table to be updated in the FROM statement, and the join moves from the FROM statement into the WHERE statement. Joins are made by listing the FROM tables, and stating everything else in the WHERE condition.
UPDATE "_CEL_M2C_ACTIVITIES" AS ACT SET "CHANGED_FIELD_TEXT_EN" = NAME_MAPPING_COLUMNS_EN.PRETTY_NAME, "CHANGED_FIELD_TEXT_DE" = NAME_MAPPING_COLUMNS_DE.PRETTY_NAME FROM "M2C_NAME_MAPPING_COLUMNS" AS NAME_MAPPING_COLUMNS_EN ,"M2C_NAME_MAPPING_COLUMNS" AS NAME_MAPPING_COLUMNS_DE WHERE ACT."CHANGED_TABLE" = NAME_MAPPING_COLUMNS_EN."TABLE_NAME" AND ACT."CHANGED_FIELD" = NAME_MAPPING_COLUMNS_EN."FIELD_NAME" AND NAME_MAPPING_COLUMNS_EN."LANGUAGE" = 'E' AND ACT."CHANGED_TABLE" = NAME_MAPPING_COLUMNS_DE."TABLE_NAME" AND ACT."CHANGED_FIELD" = NAME_MAPPING_COLUMNS_DE."FIELD_NAME" AND NAME_MAPPING_COLUMNS_DE."LANGUAGE" = 'D' ;
Insert multiple rows at once
Inserting multiple rows at once works differently in Vertica. Here's how to do it:
-- This does not work in Vertica INSERT INTO table_name(column1,column2) VALUES (value1,value2), (value1,value2) -- Instead, use the following syntax INSERT INTO table_name SELECT value1,value2 UNION SELECT value1,value2