SQL reference

VegaDB syntax, statements, data types, operators, functions and behavioral contracts.

VegaDB SQL is an analytical SQL language exposed through the PostgreSQL wire protocol. Identifiers are case-insensitive unless quoted. String literals use single quotes; quoted identifiers use double quotes.

SELECT
  customer_id,
  date_trunc('month', ordered_at) AS month,
  sum(net_amount) AS revenue
FROM lakehouse.sales.orders
WHERE status = 'paid'
GROUP BY ALL
QUALIFY revenue > 1000
ORDER BY month DESC;

Reference map

Language basics

Identifiers, qualification, literals, comments, aliases and naming rules.

Query syntax

SELECT, joins, CTEs, grouping, windows, set operations, PIVOT and subqueries.

Statements

Catalog, schema, table, view, materialization, DML and session statements.

Data types

Numeric, temporal, nested, JSON, UUID, enum, union, map and array types.

Type conversion

Explicit casts, try casts, implicit coercion and common-type selection.

Expressions and operators

Precedence, NULL behavior, comparisons, patterns, subqueries and lambdas.

Functions and operators

Numeric, text, date, JSON, nested, aggregate, window and approximate functions.

Function catalog

Searchable names, aliases and signatures organized by function family.

Metadata

SHOW, DESCRIBE, information_schema and result-column contracts.

Transactions and parameters

Read-only transaction behavior, prepared parameters and cancellation.

Limits and compatibility

Availability boundaries, bounded operations and PostgreSQL client compatibility.

Language coverage

Area	Included surface
Query	projections, filters, joins, subqueries, CTEs, set operations, grouping and ordering
Analytics	windows, `QUALIFY`, grouping sets, cube, rollup, pivot, unpivot and sampling
Data definition	catalogs, schemas, tables, views and materialized views
Data modification	insert, update, delete and merge where the selected catalog permits writes
Types	scalar numeric/text/binary/temporal plus list, array, struct, map, union, JSON and UUID
Functions	scalar, aggregate, approximate, window, nested-data, JSON, date/time and vector families
Client use	prepared parameters, streamed results and read-only transaction blocks over PostgreSQL clients

Compatibility and availability

Supported means the syntax and result behavior are part of the VegaDB warehouse contract.
Bounded means the construct has a documented row, byte, state, output-column or recursion limit.
Unavailable means VegaDB rejects the operation with a specific error instead of silently changing its meaning.

A query can be valid SQL and still be unavailable for a catalog, warehouse profile or release. The returned error identifies the unsupported operation or exceeded limit.

Parameters

PostgreSQL extended-query clients use positional parameters ($1, $2, …). Prepared input supports scalars, arrays and canonical JSONB converted to an explicitly typed STRUCT, MAP, UNION, VARIANT, LIST or ARRAY target.

SELECT $1::BIGINT AS account_id,
       $2::STRUCT(name VARCHAR, active BOOLEAN) AS profile;

NULL

VegaDB uses SQL three-valued logic. NULL = NULL is NULL, not TRUE; use IS NOT DISTINCT FROM for NULL-safe equality. Aggregates generally ignore NULL inputs except functions such as first, last and list-producing aggregates where NULL is part of the selected value.

SELECT
  NULL = NULL AS unknown,
  NULL IS NOT DISTINCT FROM NULL AS null_safe_true;

Availability is explicit

Reference pages distinguish supported behavior from catalog-dependent writes and preview behavior. Because overloads can be added with a warehouse release, information_schema.routines is the final machine-readable inventory for the warehouse to which a client is connected. Documentation should never be used to infer that an unlisted function or syntax variant is silently accepted.