Greenplum Dialect

Greenplum Dialect Reference documentation for the greenplum SQL dialect. Source: greenplum Tests: greenplum, greenplum-crud 1. Overview Greenplum Database is a massively-parallel-processing (MPP) analytics data warehouse built by forking PostgreSQL (historically the 8.2/8.3 line, later rebased onto PostgreSQL 9.x and 12). Because it speaks the PostgreSQL wire protocol and is largely SQL-compatible with it, GreenplumDialect extends PostgresDialect and reuses the same pg driver: Practically everything — SELECT/INSERT/UPDATE/DELETE grammar, $n placeholders, double-quoted identifiers, type mapping — is inherited verbatim from Postgres. This dialect only adds the genuinely Greenplum-specific pieces: MPP distribution policy on CREATE TABLE. Append-optimized / column-oriented storage with compression. Legacy range partitioning (START/END/EVERY). External tables (gpfdist://, EXTERNAL WEB, writable). Table-level constraints emitted inside the column list. Upsert without ON CONFLICT (a staging-table strategy — the key divergence, see below). The connection type is a pure alias of the Postgres one: 2. Connection Greenplum is created through Prorm with dialect: 'greenplum' (registered in src/prorm.ts). The coordinator/master segment listens on the standard Postgres port 5432, which is applied by default: 3. Distribution & storage DISTRIBUTED clause — buildDistributionClause Every Greenplum table is physically distributed across segments. The dialect emits the trailing DISTRIBUTED ... clause only when a distribution option is supplied, so callers that never set one produce plain-Postgres-compatible DDL. The clause is always the final clause of a CREATE TABLE. Precedence and fallback rules (from the source): 1. distributedReplicated: true → DISTRIBUTED REPLICATED 2. non-empty distributedBy: [...] → DISTRIBUTED BY (cols) 3. otherwise, if any distribution intent was expressed (distributedRandomly, or distributedBy/distributedReplicated present but not usable, e.g. an empty array) → DISTRIBUTED RANDOMLY 4. nothing supplied → '' (no clause) Append-optimized / columnar storage — buildStorageClause Emits a WITH (...) storage clause. It activates when any of appendOptimized === true, orientation, compressType, compressLevel, or blocksize is set. appendoptimized=true is always emitted first, then each supplied parameter in order. Supported option values: orientation: 'row' | 'column' compressType: 'zlib' | 'zstd' | 'rletype' | 'quicklz' | 'none' compressLevel: number blocksize: number (bytes) Clause ordering Within a CREATE TABLE, the order is: column list (with table constraints) → INHERITS → PARTITION BY → storage WITH (...) → TABLESPACE → DISTRIBUTED ... (last). Combined example: TABLESPACE placement tablespace is placed after storage/partitioning and before the DISTRIBUTED clause. The tablespace name is emitted raw (not escaped): Legacy range partitioning — buildRangePartitionClause Greenplum's own older PARTITION BY RANGE(col) (START (...) END (...) EVERY (...)) syntax (distinct from Postgres 10+ declarative partitioning). This is a standalone clause builder — it is not automatically spliced into buildCreateTableSQL. Bounds are formatted as SQL literals: numbers verbatim, already-expression strings (leading INTERVAL/DATE/TIMESTAMP/TIME/ NUMERIC/MAXVALUE/MINVALUE keyword, a func(...) call, or an already-quoted '...') raw, and any other string single-quoted. Numeric bounds are inlined verbatim: External tables — buildExternalTableSQL Exposes external data (e.g. served by gpfdist, HTTP, or EXECUTE) as a readable or writable table. Throws Greenplum external table requires at least one LOCATION. if locations is empty. Column definitions carry types only (no distribution/constraints). Emitted order: CREATE [WRITABLE] EXTERNAL [WEB] TABLE [IF NOT EXISTS] name (cols) LOCATION (...) FORMAT '...' [(DELIMITER '...' HEADER)] [ENCODING '...'] [LOG ERRORS] [SEGMENT REJECT LIMIT n UNIT]. Readable CSV table with format options: Writable web table: Error routing with LOG ERRORS / SEGMENT REJECT LIMIT: format defaults to 'TEXT'; rejectLimitType defaults to 'ROWS'. format accepts 'TEXT' | 'CSV' | 'CUSTOM' | 'AVRO' | 'PARQUET'. The convenience method createExternalTable(...) executes the built statement against a live connection. 4. Table constraints Table-level constraints are emitted inside the column list (not as separate ALTER TABLE statements), built by buildTableConstraintSql. Supported types: PRIMARY KEY, UNIQUE, FOREIGN KEY, CHECK. An optional name produces a CONSTRAINT "name" ... prefix. Constraint shapes: PRIMARY KEY → [CONSTRAINT "name" ]PRIMARY KEY ("cols") UNIQUE → [CONSTRAINT "name" ]UNIQUE ("cols") FOREIGN KEY → [CONSTRAINT "name" ]FOREIGN KEY ("cols") REFERENCES "table"("field") (the REFERENCES part is omitted when references is not supplied) CHECK → [CONSTRAINT "name" ]CHECK (expression) — uses the raw expression string 5. Upsert — the key divergence Greenplum (Postgres 8/9-derived) does not implement INSERT ... ON CONFLICT. This dialect therefore diverges from Postgres in two ways: buildInsertQuery throws on upsert A plain insert delegates to the inherited Postgres builder, but an upsert insert is rejected with a clear error pointing at buildUpsertQuery: buildUpsertQuery — staging-table strategy conflictFields is required (it throws otherwise — the strategy matches existing rows on these key columns). The emitted SQL is a multi-statement batch joined by ;\n, never containing ON CONFLICT: 1. CREATE TEMP TABLE "<t>gpupsertstg" (LIKE "<t>") ON COMMIT DROP 2. INSERT INTO stg (cols) VALUES (...) 3. DELETE FROM "<t>" USING stg WHERE <join on conflictFields> 4. INSERT INTO "<t>" (writeCols) SELECT writeCols FROM stg [RETURNING ...] 5. DROP TABLE stg — only when there is no RETURNING (RETURNING must be the last statement; the temp table is also ON COMMIT DROP as a safety net) writeCols is conflictFields plus updateOnDuplicate when that is supplied, otherwise every provided column. Basic upsert (no updateOnDuplicate, no RETURNING → ends with DROP TABLE): values is [1, 100]. With updateOnDuplicate + RETURNING (only key + updated columns written back; no trailing DROP TABLE because RETURNING must be last): Note that the staging INSERT still carries all provided columns (id, balance, note), while the write-back SELECT narrows to the key + updated columns (id, balance). returning: true produces RETURNING ; an array produces RETURNING "col", .... 6. Type mapping Type mapping is inherited from Postgres. Notable points verified in the source and tests: Auto-increment integer columns map to SERIAL (Postgres shorthand; Greenplum backs it with a distributed sequence). This applies when autoIncrement is set and the type key is INTEGER (or unspecified); PRIMARY KEY, NOT NULL, and UNIQUE modifiers are appended after SERIAL: STRING maps to VARCHAR (e.g. { key: 'STRING', length: 40 } → VARCHAR(40)), via the inherited getDataTypeSql. Identifiers are quoted with double quotes and internal quotes doubled: escapeId('my"col') → "my""col". Column definitions support NOT NULL (allowNull: false), DEFAULT, PRIMARY KEY, UNIQUE, and REFERENCES (with ON DELETE / ON UPDATE), mirroring the common subset of the Postgres column builder. 7. Not-yet-verified SQL-generation verified (asserted in the test suites, no live DB): the distribution clause and its fallbacks, append-optimized/columnar storage, clause ordering, TABLESPACE, table-level constraints, legacy range partitioning, external tables (readable/writable/web, format options, LOG ERRORS, SEGMENT REJECT LIMIT, empty-LOCATION guard), the upsert throw and the full staging-table upsert SQL, SERIAL auto-increment, STRING→VARCHAR, identifier quoting, and inherited CRUD grammar (INSERT/SELECT/UPDATE/DELETE, RETURNING, bulk insert, WHERE AND/OR, GROUP BY/HAVING, LIMIT/OFFSET). Not verified against a live Greenplum cluster. All tests run "grammar, no connection" — they inspect generated SQL only and never execute it. The following have not been exercised end-to-end and would need a real Greenplum instance to confirm: That the generated DDL/DML actually executes and behaves as intended on a Greenplum coordinator (segment distribution, AO/columnar storage on disk, compression, tablespace placement). The staging-table upsert's transactional semantics — in particular that the batch runs in a single transaction so ON COMMIT DROP fires, and that RETURNING behaves as the final statement. Legacy PARTITION BY RANGE(...) acceptance by the target server version (the builder is standalone and not auto-spliced into buildCreateTableSQL). External table connectivity (gpfdist/web/EXECUTE sources), ENCODING, and reject-limit behavior. The inherited Greenplum caveats noted in the source header (SERIAL backed by a distributed sequence, UNIQUE guarantees only when the distribution key is a subset of the constraint, savepoint limitations) are described but not tested. Related reading All dialects — what each engine supports Data types — how each type maps to this engine Querying — how a FindOptions becomes SQL