Greenplum Dialect

Overview

Reference documentation for the greenplum SQL dialect.

Property	Value
Dialect name	`greenplum`
Extends	`PostgresDialect` (postgres)
Driver	`pg`
Cloud SDK	none
Default port	5432
Local runtime	self-hosted; no image in this repo

Source: greenplum
Tests: greenplum, greenplum-crud

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:

export class GreenplumDialect extends PostgresDialect {
  readonly name: string = 'greenplum';
  readonly library: string = 'pg';

  constructor(config: GreenplumDialectOptions) {
    // Greenplum's coordinator listens on the standard Postgres port 5432.
    super({ port: 5432, ...config });
  }
}

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:

export interface GreenplumDialectOptions extends PostgresDialectOptions {}

Quick start

Install

$ npm install ts-prorm-orm pg

$ pnpm add ts-prorm-orm pg

$ yarn add ts-prorm-orm pg

$ bun add ts-prorm-orm pg

pg is the same driver the postgres dialect uses. pg-copy-streams is optional: only the features that need it load it.

Connect

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'greenplum',
  host: 'coordinator.internal',
  database: 'analytics',
  username: 'gpadmin',
  password: process.env.DB_PASSWORD,
});

import { Prorm } from 'ts-prorm-orm';

export const db = new Prorm({
  dialect: 'greenplum',
  url: 'greenplum://gpadmin:secret@coordinator.internal:5432/analytics',
});

A URL is folded into host, port, database, username and password before the dialect is built; any of those passed alongside it wins.

Define a model

import { DataTypes } from 'ts-prorm-orm';

const User = db.define('User', {
  id: { type: DataTypes.INTEGER(), primaryKey: true, autoIncrement: true },
  email: { type: DataTypes.STRING(255), allowNull: false, unique: true },
  name: { type: DataTypes.STRING(100) },
  meta: { type: DataTypes.JSONB() },
});

await db.sync(); // CREATE TABLE — see Generated SQL below

meta is JSONB, which the containment query below needs.

Query

import { Op } from 'ts-prorm-orm';

await User.create({ email: 'ada@example.com', name: 'Ada' });

const page = await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

Generated SQL

Each block is the statement the model call above it hands the dialect's query(), captured with the driver stubbed out. The model layer inlines the values.

Create table

db.sync() for the model in the Quick start:

CREATE TABLE

Insert, update and delete

await User.create({ email: 'ada@example.com', name: 'Ada' });
await User.update({ name: 'Grace' }, { where: { email: 'ada@example.com' } });
await User.destroy({ where: { id: 1 } });

Upsert

await User.upsert({ id: 1, email: 'ada@example.com', name: 'Ada Lovelace' });

Upsert statements

upsert() also reads the row by primary key before and after the write, to return the instance.

Pagination

await User.findAll({
  where: { name: { [Op.like]: 'A%' } },
  order: [['id', 'ASC']],
  limit: 20,
  offset: 40,
});

JSON

Containment on the meta column:

import { Op } from 'ts-prorm-orm';

await User.findAll({ where: { meta: { [Op.contains]: { plan: 'pro' } } } });

Transaction with a savepoint

await db.transaction(async (t) => {
  await User.create({ email: 'grace@example.com', name: 'Grace' }, { transaction: t });
  // A nested transaction is a SAVEPOINT; its failure undoes only its own work.
  await db
    .transaction(async (sp) => {
      await User.destroy({ where: { id: 1 }, transaction: sp });
      throw new Error('keep the row');
    }, { transaction: t })
    .catch(() => {});
});

Skip locked rows (`SKIP LOCKED`)

A worker that claims rows from a queue table should pass over rows another transaction holds rather than wait for them. lock with skipLocked inside a transaction:

await db.transaction(async (t) => {
  await User.findAll({ where: { name: 'Ada' }, lock: { level: 'UPDATE', skipLocked: true }, transaction: t });
});

Type mapping for the portable descriptors: STRING → VARCHAR(255), auto-increment INTEGER → SERIAL, BOOLEAN → BOOLEAN, DATE → TIMESTAMP, UUID → UUID, JSON → JSON. The type comparison lines the major engines up side by side.

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:

import { Prorm, DataTypes } from 'ts-prorm-orm';

const prorm = new Prorm({
  dialect: 'greenplum',
  host: 'coordinator.example.com',
  port: 5432, // default — Greenplum coordinator uses the Postgres port
  database: 'analytics',
  username: 'gpadmin',
  password: 'password',
});

const Sale = prorm.define('Sale', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true,
  },
  region: { type: DataTypes.STRING(20) },
  amount: { type: DataTypes.DECIMAL(10, 2) },
});

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):

distributedReplicated: true → DISTRIBUTED REPLICATED

non-empty distributedBy: [...] → DISTRIBUTED BY (cols)

otherwise, if any distribution intent was expressed (distributedRandomly,
or distributedBy/distributedReplicated present but not usable, e.g. an
empty array) → DISTRIBUTED RANDOMLY

nothing supplied → '' (no clause)

d.buildCreateTableSQL(
  't',
  { id: { type: 'INTEGER' }, region: { type: 'VARCHAR(20)' } },
  { distributedBy: ['id'] },
);

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.

d.buildCreateTableSQL(
  't',
  { id: { type: 'INTEGER' } },
  {
    appendOptimized: true,
    orientation: 'column',
    compressType: 'zstd',
    compressLevel: 5,
    blocksize: 32768,
  },
);

Supported option values:

orientation: 'row' | 'column'

compressType: 'zlib' | 'zstd' | 'rle_type' | '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:

d.buildCreateTableSQL(
  'events',
  { id: { type: 'INTEGER' }, ts: { type: 'TIMESTAMP' } },
  { appendOptimized: true, orientation: 'column', compressType: 'zstd', distributedBy: ['id'] },
);

TABLESPACE placement

tablespace is placed after storage/partitioning and before the
DISTRIBUTED clause. The tablespace name is emitted raw (not escaped):

d.buildCreateTableSQL(
  't',
  { id: { type: 'INTEGER' } },
  { tablespace: 'fastspace', distributedBy: ['id'] },
);

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.

d.buildRangePartitionClause('sale_date', {
  start: '2020-01-01',
  end: '2021-01-01',
  every: "INTERVAL '1 month'",
});

Numeric bounds are inlined verbatim:

d.buildRangePartitionClause('bucket', { start: 0, end: 100, every: 10 });

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:

d.buildExternalTableSQL(
  'ext_sales',
  { id: { type: 'INTEGER' }, amt: { type: 'DECIMAL(10,2)' } },
  { locations: ['gpfdist://host:8081/sales/*.csv'], format: 'CSV', delimiter: ',', header: true },
);

Writable web table:

d.buildExternalTableSQL(
  'ext_out',
  { id: { type: 'INTEGER' } },
  { locations: ['gpfdist://host:8081/out/'], writable: true, web: true },
);

Error routing with LOG ERRORS / SEGMENT REJECT LIMIT:

d.buildExternalTableSQL(
  'ext',
  { id: { type: 'INTEGER' } },
  { locations: ['gpfdist://h/*'], logErrors: true, rejectLimit: 100, rejectLimitType: 'ROWS' },
);

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.

d.buildCreateTableSQL(
  'orders',
  { id: { type: 'INTEGER' }, customer_id: { type: 'INTEGER' } },
  {
    constraints: [
      { type: 'PRIMARY KEY', fields: ['id'], name: 'pk_orders' },
      { type: 'FOREIGN KEY', fields: ['customer_id'], references: { table: 'customers', field: 'id' } },
    ],
    distributedBy: ['id'],
  },
);

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:

d.buildInsertQuery('t', { id: 1, v: 2 }, { upsert: true, conflictFields: ['id'] });
// throws:
// GreenplumDialect: INSERT ... ON CONFLICT upsert is not supported by Greenplum
// (Postgres 8/9-derived). Use buildUpsertQuery(), which emits a staging-table
// CREATE TEMP + DELETE ... USING + INSERT ... SELECT strategy instead.

`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:

CREATE TEMP TABLE "<t>_gp_upsert_stg" (LIKE "<t>") ON COMMIT DROP

INSERT INTO stg (cols) VALUES (...)

DELETE FROM "<t>" USING stg WHERE <join on conflictFields>

INSERT INTO "<t>" (writeCols) SELECT writeCols FROM stg [RETURNING ...]

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):

d.buildUpsertQuery('accounts', { id: 1, balance: 100 }, { conflictFields: ['id'] });

values is [1, 100].

With updateOnDuplicate + RETURNING (only key + updated columns written back;
no trailing DROP TABLE because RETURNING must be last):

d.buildUpsertQuery(
  'accounts',
  { id: 1, balance: 100, note: 'x' },
  { conflictFields: ['id'], updateOnDuplicate: ['balance'], returning: ['id'] },
);

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:

d.buildCreateTableSQL('t', {
  id: { type: { key: 'INTEGER' }, autoIncrement: true, primaryKey: true },
});
// → CREATE TABLE "t" ("id" SERIAL PRIMARY KEY)

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.

What differs from Postgres

GreenplumDialect extends PostgresDialect (postgres) and
overrides 24 of its methods (Object.getOwnPropertyNames(GreenplumDialect.prototype),
computed by the scratch test); everything else is inherited unchanged.

Area	Overridden
DML builders	`buildInsertQuery`, `buildSetDistributionSQL`, `buildUpsertQuery`
Types	`buildGreenplumColumnSql`
DDL and schema	`addRangePartition`, `analyzeTable`, `buildAddRangePartitionSQL`, `buildCreateTableAsSQL`, `buildCreateTableSQL`, `buildDistributionClause`, `buildTableConstraintSql`, `createExternalTable`, `createTable`, `createTableAs`, `dropRangePartition`, `truncateRangePartition`, `truncateTable`, `vacuumTable`
Other	`buildExternalTableSQL`, `buildRangePartitionClause`, `buildStorageClause`, `formatPartitionBound`, `inlineLiteral`, `setDistribution`

Capabilities

Computed from the code — capabilitiesOf() (src/transaction-support.ts),
whereSupportOf() (src/query-builders/expression-render.ts),
sqlObjectCapabilitiesOf() (src/sql-objects/capabilities.ts) and the
builders — by the scratch test described in the
capability matrix.

Capability	`greenplum`
Transactions atomic	yes
Savepoints	yes
Isolation	up to `SERIALIZABLE`
Transaction modes	read-only yes; deferrable yes; constraint deferral yes
RETURNING	`RETURNING` on INSERT
Upsert	a plain `INSERT`: the engine has no conflict clause (see the page)
JSON column	`JSON` (`JSONB` → `JSONB`)
Vector column	`VECTOR(3)`
Array column	`INTEGER[]`
WHERE builder	62 operator keys; `$and`/`$or`/`$not`; `$exists`: yes; `$in` subquery: yes; expression operands: yes
SQL objects	family `postgres`: triggers yes; procedures yes; functions `multiStatement`; materialized views native; sequences yes; enums `named`; RLS `policy`
Streaming	`queryStream()` present; `LISTEN` present; `createExtension()` present

Testing

tests/dialects/greenplum.test.ts exercises the dialect with a mocked driver, so nothing touches a server.

All dialects — what each engine supports

Data types — how each type maps to this engine

Querying — how a FindOptions becomes SQL

Greenplum Dialect

Overview#

Quick start#

Install#

Connect#

Define a model#

Query#

Generated SQL#

Create table#

Insert, update and delete#

Upsert#

Pagination#

JSON#

Transaction with a savepoint#

Skip locked rows (SKIP LOCKED)#

Connection#

3. Distribution & storage#

DISTRIBUTED clause — buildDistributionClause#

Append-optimized / columnar storage — buildStorageClause#

Clause ordering#

TABLESPACE placement#

Legacy range partitioning — buildRangePartitionClause#

External tables — buildExternalTableSQL#

4. Table constraints#

5. Upsert — the key divergence#

buildInsertQuery throws on upsert#

buildUpsertQuery — staging-table strategy#

6. Type mapping#

7. Not-yet-verified#

What differs from Postgres#

Capabilities#

Testing#

Related reading#