Want to deploy your JVM, Node.js and Go apps effortlessly to AWS? Try our service Boxfuse

HP Vertica

Versions

  • 6.5 and later

Default Driver

  • com.vertica.jdbc.Driver

Sql Script Syntax

  • Standard Sql syntax with statement delimiter ;
  • Dollar-quoted string constants
  • Function bodies are delimited by BEGIN/END

Compatibility

  • DDL exported by EXPORT_CATALOG, EXPORT_TABLES or EXPORT_OBJECTS can be used unchanged in a Flyway migration.
  • Any HP Vertica sql script executed by Flyway, can be executed by the vsql command-line tool and other Vertica-compatible tools (after the placeholders have been replaced).

Example

/* Single line comment */
CREATE SEQUENCE test_seq;

CREATE TABLE test_data (
  test_id INT NOT NULL PRIMARY KEY,
  value VARCHAR(25) NOT NULL
) ORDER BY value;

/*
Multi-line
comment
*/
CREATE FUNCTION default_to_hello(x VARCHAR) RETURN VARCHAR
AS BEGIN
  -- The body of a SQL function in Vertica must be in the form 'RETURN expression'
  RETURN (CASE WHEN (x IS NOT NULL) THEN x ELSE 'Hello' END);
END;

INSERT INTO test_data (test_id, value) VALUES (nextval('test_seq'), default_to_hello(null));

CREATE TABLE another_table AS SELECT 'some-data' as name;

-- Placeholder
INSERT INTO ${tableName} (name) VALUES ('Mr. T');

Limitations

  • No support for vsql meta-commands with no Jdbc equivalent like \set
  • No support for COPY ... FROM stdin as generated by pg_dump as it has no Jdbc equivalent
  • The current implementation of the clean command does not drop external functions, procedures and libraries (e.g, implemented in C++ or R)

H2