Documentation / Connectors

PostgreSQL

Connect any PostgreSQL database to Nexus for full read, write, and merge, with SQL filter, aggregation, join, and pagination pushdown. It is the same adapter class that powers the built-in per-tenant workspace bridge.

The PostgreSQL connector (bundle version 2.0.7) lets Nexus read from and write to any PostgreSQL database your team manages. You configure it as a bridge with standard credentials; Nexus handles connection pooling, TLS, and dialect translation. It is a full-pushdown SQL adapter — filtering, aggregation, joins, distinct, ordering, and pagination all execute on the database — and it exposes the complete write surface including ON CONFLICT upsert.

This adapter class is also what backs the built-in workspace bridge, so pipeline authors work with an identical API surface whether they are querying an external Postgres database or staged workspace data.

This page is for engineers configuring a PostgreSQL bridge. It covers authentication and TLS, the pushdown and operator surface, dialect specifics, the merge/upsert model, read-only handling of views, and how the connector relates to the built-in workspace.

Capabilities

Feature	Supported
Read	Yes
Write (insert / update / delete)	Yes
Upsert (merge)	Yes — `INSERT ... ON CONFLICT DO UPDATE`
Schema discovery	Yes
Filter pushdown	Full
Aggregation pushdown	Yes — `SUM`, `COUNT`, `AVG`, `MIN`, `MAX`
`GROUP BY` / `HAVING` / `DISTINCT`	Yes
`JOIN`s	Yes
Window functions / CTEs	Via `$expr` raw SQL

Because PostgreSQL declares full pushdown and its operators are all pushable, the Nexus engine sends your WHERE, aggregation, ordering, and LIMIT/OFFSET straight to the database and does not re-filter returned rows in memory (see Operators & pushdown).

All UQL filter operators are supported:

$eq $ne $gt $gte $lt $lte $like $in $not_in $between $empty $contains $starts_with $ends_with $expr

$expr passes a raw SQL fragment through unescaped — treat it as a trusted-author surface and bound authorship with execution roles.

Authentication and TLS

The PostgreSQL bridge authenticates with a username and password. TLS is required by default (sslmode=require) — connections are encrypted and the server certificate is validated. You cannot accidentally connect over plaintext.

Connection fields

Field	Description
`host`	Hostname or IP of the PostgreSQL server
`port`	Port (default: `5432`)
`username`	Database user
`password`	Password
`database`	Target database name

Credentials are stored as named secrets in your tenant's Azure Key Vault — never inlined in a report or pipeline definition. See Secrets and Connections & bridges.

SQL dialect

Identifiers are double-quote delimited ("schema"."table").
Paging uses LIMIT ... OFFSET.
Upserts use INSERT ... ON CONFLICT (key_cols) DO UPDATE SET col = EXCLUDED.col (or DO NOTHING when every column is part of the conflict key).
Parameters use %s placeholders (psycopg format style).

Schema discovery

Discovery reads information_schema.tables and information_schema.columns, filtering out pg_catalog and information_schema system schemas automatically. The result is a structured list of user schemas, tables, and columns — including views, which are marked read-only. Discovery is scoped to the single database named in the connection.

async def run(ctx):
    # Discover available tables in the connected database
    schema = await ctx.adapters.my_pg.discover()

    # Read with a pushed-down filter
    result = await ctx.adapters.my_pg.read({
        "container": "public",
        "collection": "orders",
        "where": {"status": {"$eq": "open"}},
        "limit": 500,
    })

    # Stage results into the workspace for downstream joins
    await ctx.workspace.upsert("open_orders", result.rows)

Merge (upsert) behavior

When you write with merge: true, the adapter generates a single INSERT ... ON CONFLICT statement. You specify the conflict key columns and Nexus constructs the DO UPDATE SET clause for the remaining columns. If every column is a conflict key, the statement uses DO NOTHING instead.

async def run(ctx):
    staged = await ctx.workspace.read("customer_updates")
    await ctx.adapters.my_pg.write({
        "container": "public",
        "collection": "customers",
        "operation": "merge",
        "key_columns": ["customer_id"],
        "records": staged.rows,
    })

Views are read-only

Tables with table_type = 'VIEW' are surfaced by discovery as read-only. Any attempt to write to a view is rejected before a query is sent — a per-adapter writability gate that sits beneath the tenant RBAC governing whether the caller may write at all.

The workspace bridge

Nexus ships a built-in PostgreSQL bridge called the workspace. Unlike a user-configured bridge, the workspace requires no credentials — it is automatically provisioned per tenant using Azure Managed Identity, and it is structurally isolated from Nexus's own operational database at the connection level. Use it to stage intermediate results, join data from multiple sources, or accumulate rows between pipeline steps.

Because the workspace uses this same adapter class, its read, write, discover, and pushdown behavior are identical to a user-configured Postgres bridge — a pipeline or report written against one transfers conceptually to the other. See the built-in workspace guide for isolation guarantees, schema-change gating, and default limits.

Choosing between PostgreSQL and the workspace

Use a user-configured PostgreSQL bridge when connecting to a database your team owns — an application database, a reporting replica, or an analytics store. Use the workspace when you need temporary staging space inside Nexus with no external credentials. Both expose identical read, write, discover, and pushdown capabilities.

Limits & behavior notes

Views cannot be written. Writes to a view fail fast with a permission-style error.
Single-database scope. Discovery and queries are scoped to the one database named in the connection; cross-database joins are not available in a single statement — combine sources in a pipeline or report.
$expr is unescaped. Trusted-author only; window functions/CTEs go through $expr or a saved report's raw SQL.
Connection budget. Per-replica DB connections are bounded by the bridge connection governor; orphans are reaped.