SQL Reference

Deltex implements a large subset of PostgreSQL syntax. If you know SQL, you already know Deltex. This page covers the most common statements; everything below is verified against the live engine.

SELECT

Projections, filtering, ordering, grouping, and limits all work as you would expect:

sql

SELECT dept, COUNT(*) AS n, AVG(salary) AS avg_sal
FROM employees
WHERE active AND salary > 50000
GROUP BY dept
HAVING COUNT(*) > 3
ORDER BY avg_sal DESC
LIMIT 10;

Operators & predicates

Category	Supported
Comparison	`=` `!=` `<` `>` `<=` `>=` `BETWEEN` `IN` `IS [NOT] NULL` `IS [NOT] DISTINCT FROM`
Pattern	`LIKE` `ILIKE` `NOT LIKE` `REGEXP` / `~` (escapes `\%` `\_` supported)
Logical	`AND` `OR` `NOT`, bare boolean column (`WHERE active`)
Arrays	`= ANY(ARRAY[...])` `<> ALL(ARRAY[...])`
Row values	`(a, b) > (x, y)` — lexicographic, ideal for keyset pagination
Unary minus	`-col` everywhere: `SELECT -x`, `SUM(-x)`, `UPDATE SET v = -v`

Joins

INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins are supported, including multi-table joins and LATERAL. Equi-joins use a Grace hash-join strategy automatically.

Aggregates

COUNT, SUM, AVG, MIN, MAX, COUNT(DISTINCT ...), STRING_AGG, ARRAY_AGG, BOOL_AND/BOOL_OR, EVERY, plus percentile_cont via WITHIN GROUP. Aggregates accept expression arguments — SUM(CASE WHEN c THEN 1 ELSE 0 END), SUM(price * qty) — and a FILTER (WHERE ...) clause.

Window functions

Full window support including frames, and — uniquely — windows computed over GROUP BY aggregates and deferred window arithmetic:

sql

-- running total, rank, and delta-from-previous in one query
SELECT day,
  SUM(amount) AS daily,
  SUM(SUM(amount)) OVER (ORDER BY day) AS running,
  RANK() OVER (ORDER BY SUM(amount) DESC) AS rnk
FROM sales GROUP BY day;

Available: ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE, LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE, and SUM/AVG/COUNT/MIN/MAX OVER with PARTITION BY (including PARTITION BY func(col)) and frames.

CTEs & recursion

WITH and WITH RECURSIVE are supported. Recursive CTEs follow PostgreSQL semantics — UNION deduplicates and terminates cyclic graphs; UNION ALL is capped at 100 iterations / 100k rows with a clean error.

JSON

-> and ->> arrow operators, JSON_EXTRACT, JSON_EACH, JSON_ARRAY_LENGTH, casts like (data->>'age')::int, and JSON predicates in WHERE.

DDL & transactions

CREATE/ALTER/DROP TABLE, CREATE INDEX, CREATE VIEW, primary keys, NOT NULL, UNIQUE, DEFAULT, and foreign keys. BEGIN/COMMIT/ROLLBACK with savepoints; multi-statement serializable transactions run through POST /v1/transaction. DROP TABLE + CREATE TABLE in one transaction is fully supported.

Beyond standard SQL

Deltex also ships full-text search (MATCH, BM25 scoring), vector similarity (COSINE_DISTANCE and friends), 20+ geospatial functions, row-level security policies, and query hints like /*+ CACHE(ttl=N) */ — all available on every plan.

← Prev

Getting Started

HTTP API