Vertica dialect

Vertica dialect Reference documentation for the vertica dialect. Everything below is drawn from the dialect source (src/dialects/vertica/index.ts), its Postgres base (src/dialects/postgres/index.ts), and the dialect tests (tests/dialects/vertica.test.ts, tests/dialects/vertica-crud.test.ts). 1. Overview Vertica is an analytic, columnar, massively-parallel-processing (MPP) database. It speaks the PostgreSQL wire protocol and is broadly SQL-compatible with PostgreSQL, so VerticaDialect extends PostgresDialect and reuses the pg driver, query building, identifier escaping, and transaction handling. The tests confirm this reuse: Only genuinely Vertica-specific behavior is overridden: Default port 5433 (not Postgres's 5432). The constructor calls super({ port: 5433, ...config }), so an explicit port in your config still wins. Columnar / MPP physical-design DDL on CREATE TABLE: ORDER BY, SEGMENTED BY HASH(...) ALL NODES / UNSEGMENTED ALL NODES, PARTITION BY. CREATE PROJECTION helper — Vertica's materialized, pre-sorted / pre-segmented physical storage (its "index" equivalent). Type mapping differences: IDENTITY instead of SERIAL, LONG VARCHAR / LONG VARBINARY for large text/blob, native BOOLEAN. DML differences: upserts use MERGE (no ON CONFLICT); RETURNING is stripped from INSERT/UPDATE/DELETE; addColumn uses IDENTITY. Everything not listed above delegates to the Postgres implementation. 2. Connection vertica is a registered dialect, so you select it with dialect: 'vertica'. The options are structurally identical to the Postgres options (same pg driver): VerticaDialectOptions extends PostgresDialectOptions. If you omit port, the dialect defaults it to 5433. The available connection fields are those inherited from PostgresDialectOptions: host, port, database, username, password, ssl, max, idleTimeoutMillis, connectionTimeoutMillis, statementTimeout, queryTimeout, types. 3. Physical design Vertica-specific CREATE TABLE clauses are supplied through VerticaTableOptions (which extends the shared TableOptions): Option | Emits | --- | --- | orderBy?: string \| string[] | ORDER BY col, ... — column-store sort order | segmentedBy?: string \| string[] | SEGMENTED BY HASH(cols) ALL NODES — hash-distribute rows across nodes | unsegmented?: boolean | UNSEGMENTED ALL NODES — replicate the full table on every node (takes precedence over segmentedBy) | partitionByExpression?: string | PARTITION BY expr | Clause order is fixed: ORDER BY, then segmentation, then PARTITION BY. unsegmented: true wins over segmentedBy (they are mutually exclusive). buildCreateTableSql(tableName, columns, options?) is a pure, synchronous string builder (unit-testable without a connection); createTable(...) runs it via query(...). ORDER BY + SEGMENTED BY Note IDENTITY (not SERIAL) and LONG VARCHAR (not TEXT). UNSEGMENTED (+ IF NOT EXISTS, DEFAULT) IDENTITY columns are implicitly NOT NULL and cannot carry a DEFAULT, so the builder skips those clauses for auto-increment columns; regular columns still emit NOT NULL / DEFAULT as shown for amount. PARTITION BY The partition expression is inserted verbatim (not escaped), so pass a valid Vertica expression or bare column name. CREATE PROJECTION Projections are configured with VerticaProjectionOptions (name, table, optional columns, orderBy, segmentedBy, unsegmented, ifNotExists). buildCreateProjectionSql(...) builds the DDL; createProjection(...) executes it. When columns is omitted or empty it defaults to . Defaulting to all columns: Projections reuse the same physical-design clause builder as tables, so orderBy / segmentedBy / unsegmented behave identically (a projection has no PARTITION BY). 4. Type mapping getDataTypeSql overrides four cases and delegates everything else to the Postgres mapping. ORM type (DataTypes.) | Vertica type | Notes | --- | --- | --- | INTEGER / BIGINT with autoIncrement | IDENTITY | Vertica has no SERIAL / BIGSERIAL | INTEGER / BIGINT (no auto-increment) | INTEGER / BIGINT | inherited from Postgres | TEXT | LONG VARCHAR | large variable-length text | BLOB | LONG VARBINARY | large variable-length binary | BOOLEAN | BOOLEAN | native | everything else | Postgres mapping | delegated to super.getDataTypeSql(...) | Auto-increment is detected two ways: in getDataTypeSql via the type's autoIncrement flag, and in the column builder (buildColumnSql) via the column's autoIncrement definition flag (which also matches when the type key is INTEGER, BIGINT, or absent). Both paths emit IDENTITY. addColumn uses the same column builder, so ALTER TABLE ... ADD COLUMN also emits IDENTITY for auto-increment columns. 5. Upsert & constraints Upsert — MERGE, not ON CONFLICT Vertica has no INSERT ... ON CONFLICT. Upserts compile to MERGE INTO ... USING (SELECT ...) .... Both entry points route here: buildUpsertQuery(table, values, options) directly, and buildInsertQuery(table, values, { upsert: true, ... }), which forwards to buildUpsertQuery (so INSERT with upsert: true never emits ON CONFLICT). conflictFields is required — it supplies the ON join key, which cannot be inferred. Omitting it throws: The inserted row is materialized as a single-row source relation (SELECT $1 AS "col", ...). Non-key columns (or the explicit updateOnDuplicate list, minus any conflict keys) go into WHEN MATCHED THEN UPDATE SET; all columns go into WHEN NOT MATCHED THEN INSERT. Values are parameterized ([1, 'Ann', 'a@x.io']). Behavior details, all covered by tests: Composite keys join with AND: ON tgt."orgid" = src."orgid" AND tgt."userid" = src."userid". updateOnDuplicate restricts the UPDATE SET to the listed non-key columns. Conflict keys are excluded from UPDATE SET (the key is never updated). Insert-only: if every column is a conflict key, the WHEN MATCHED clause is omitted entirely — only WHEN NOT MATCHED THEN INSERT remains. Schema prefix is honored: MERGE INTO "analytics"."users" AS tgt. No RETURNING is ever emitted. No RETURNING on plain DML buildInsertQuery, buildUpdateQuery, and buildDeleteQuery delegate to the Postgres builders but strip any returning option first (Vertica cannot return affected rows inline — read them back with a follow-up SELECT). Both boolean (returning: true) and column-list (returning: ['id', 'name']) forms are stripped. SELECT is fully inherited from Postgres — LIMIT, OFFSET, WHERE with AND/OR, ORDER BY ... DESC, GROUP BY, and HAVING all work as in the base dialect. Constraints (advisory) Constraint DDL is emitted with the same syntax as Postgres — PRIMARY KEY, UNIQUE, NOT NULL, CHECK, and inline foreign keys: Important caveat documented in the source: Vertica only enforces PRIMARY KEY / UNIQUE / FOREIGN KEY / NOT NULL / CHECK constraints during COPY / ANALYZECONSTRAINTS (or when ENABLEd) — otherwise they are advisory metadata the optimizer trusts. A duplicate or violating row is not rejected at INSERT time by default. The ORM emits the DDL but does not add any enforcement; validate your data or run ANALYZECONSTRAINTS if you rely on these constraints. 6. Not-yet-verified SQL-generation verified (asserted by the connection-free unit tests in tests/dialects/vertica.test.ts and tests/dialects/vertica-crud.test.ts): Dialect identity: name === 'vertica', extends PostgresDialect, library === 'pg'. CREATE TABLE with ORDER BY, SEGMENTED BY HASH(...) ALL NODES, UNSEGMENTED ALL NODES, PARTITION BY, IF NOT EXISTS, IDENTITY, PRIMARY KEY, NOT NULL DEFAULT, and inline REFERENCES. CREATE PROJECTION with explicit columns and default. Type mapping for TEXT, BLOB, BOOLEAN, and auto-increment INTEGER/BIGINT, plus delegation of plain INTEGER. INSERT shape, RETURNING stripping (INSERT/UPDATE/DELETE), and MERGE upsert generation (single/composite keys, updateOnDuplicate, insert-only, schema prefix, missing-conflictFields error). SELECT LIMIT/OFFSET/ORDER BY/GROUP BY/HAVING. Not yet verified against a live Vertica cluster. The tests exercise only the synchronous string builders — no connect() is called. The following depend on a real Vertica server and have not been validated end-to-end: Actual execution over the pg wire on port 5433 (the pg driver is Postgres's; Vertica's protocol compatibility is assumed, not tested here). Runtime acceptance of the generated CREATE TABLE / CREATE PROJECTION / MERGE statements by the Vertica SQL engine. Constraint-enforcement behavior (advisory vs COPY/ANALYZECONSTRAINTS). Any inherited Postgres feature (COPY streaming, schemas, views, sequences, transactions) working against Vertica specifically. Treat everything in sections 3–5 as SQL-generation-verified; confirm runtime behavior against your own Vertica deployment before relying on it. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL