Docs / SQL Reference

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

CategorySupported
Comparison= != < > <= >= BETWEEN IN IS [NOT] NULL IS [NOT] DISTINCT FROM
PatternLIKE ILIKE NOT LIKE REGEXP / ~ (escapes \% \_ supported)
LogicalAND 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.

What Deltex deliberately doesn't do

Deltex is not a drop-in Postgres. Knowing the boundaries up front saves migration surprises:

← Prev
Getting Started
Next →
HTTP API