SQL Reference

Hotdata's native SQL is HotSQL — standard SQL with extensions for analytics, time-series, and complex types. If you know Postgres, you already know most of it. You can also write queries in PostgreSQL, DuckDB, or Snowflake syntax (see SQL dialects).

SQL dialects

Hotdata accepts queries in four SQL dialects — HotSQL (the native dialect), PostgreSQL, DuckDB, and Snowflake. Set the dialect field on the query request to write in a non-HotSQL dialect, and Hotdata translates it to HotSQL before running — so queries written for another engine run without a rewrite. Only read-only queries are accepted, and an unrecognized dialect is rejected.

{
  "sql": "SELECT IFF(amount > 100, 'big', 'small') AS bucket FROM orders",
  "dialect": "snowflake"
}

SELECT syntax

The core query form is:

SELECT ... FROM ... [ WHERE ... ] [ GROUP BY ... ] [ HAVING ... ] [ ORDER BY ... ] [ LIMIT ... ]

WITH (CTEs)

Use WITH for common table expressions:

WITH top_customers AS (
  SELECT customer_id, sum(amount) AS total
  FROM orders
  GROUP BY customer_id
  ORDER BY total DESC
  LIMIT 10
)
SELECT * FROM top_customers;

FROM clause

Reference tables by schema and name. Tables live in your instant databases. Use fully qualified names when needed: catalog.schema.table or schema.table.

WHERE clause

Filter rows with conditions. Supports AND, OR, NOT, comparison operators (=, !=, <, >, <=, >=), IN, BETWEEN, LIKE, IS NULL, IS NOT NULL, and subqueries with EXISTS, IN, ANY, ALL.

JOIN clause

Join tables with INNER, LEFT, RIGHT, FULL, CROSS joins. Use ON for join conditions:

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2025-01-01';

GROUP BY and HAVING

Group rows and filter groups:

SELECT department, count(*) AS headcount, avg(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING count(*) > 5
ORDER BY avg_salary DESC;

ORDER BY and LIMIT

Sort and limit results:

SELECT * FROM events
ORDER BY created_at DESC
LIMIT 100;

ORDER BY supports ASC, DESC, and multiple columns. LIMIT can optionally include OFFSET for pagination.

UNION

Combine result sets with UNION (distinct) or UNION ALL (keeps duplicates):

SELECT id, name FROM users
UNION ALL
SELECT id, name FROM archived_users;

Subqueries

Subqueries are supported in SELECT, FROM, WHERE, and HAVING:

SELECT *
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE tier = 'premium');

Data types

Standard SQL types are supported:

BINARY, VARBINARY, and BLOB are not accepted as cast targets. For binary data use BYTEA, or arrow_cast(x, 'Binary') and decode(x, 'hex').

Operators

Comparison

=, !=, <>, <, >, <=, >=, IS NULL, IS NOT NULL, BETWEEN ... AND ..., LIKE, IN (...)

Logical

AND, OR, NOT

Numerical

+, -, *, /, % (modulo)

Bitwise

&, |, ^ (bitwise XOR), <<, >>

There is no bitwise-NOT operator: ~ is not supported, and there is no bit_not function to stand in for it.

String

|| for concatenation

Functions

HotSQL includes a comprehensive function library. Each category has a dedicated reference with signatures, arguments, and runnable examples:

• Math
• String — text, regular-expression, and hashing functions
• Date & time
• Conditional & utility
• Aggregate
• Window
• Array, struct & map
• JSON — path access with -> and ->>
• Spatial

Aggregates: FILTER and WITHIN GROUP

Aggregates accept a FILTER (WHERE ...) clause, and ordered-set aggregates (percentile_cont, approx_percentile_cont) accept WITHIN GROUP (ORDER BY ...):

SELECT
  sum(amount) FILTER (WHERE status = 'completed')       AS completed_revenue,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY latency)  AS median_latency
FROM orders;

Window functions

Any aggregate or window function runs over a frame with OVER (PARTITION BY ... ORDER BY ...):

SELECT id, amount,
  sum(amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  rank()      OVER (ORDER BY amount DESC)                                          AS amount_rank
FROM transactions;

Expanding arrays

unnest(array) expands an array into rows:

SELECT id, unnest(tags) AS tag FROM articles;

Geospatial functions

Hotdata includes spatial functions that follow PostGIS-style ST_ naming, for querying geometry data. Geometries are planar (Cartesian)—there is no geography type, and coordinates are always interpreted as x = easting/longitude. Buffering and overlay operations (ST_Buffer, ST_Union, ST_Intersection, ST_Difference, ST_MakeValid, ...) and coordinate reprojection (ST_Transform) are available. For geodesic measurements in metres on lon/lat data, use the _Spheroid family (ST_Area_Spheroid, ST_Distance_Spheroid, ST_DWithin_Spheroid, ...) or ST_DistanceSphere.

Constructing geometries

Create points and geometries from coordinates or text:

-- Create a 2D point
SELECT ST_MakePoint(-122.4194, 37.7749) AS sf_point;

-- Create from Well-Known Text (WKT)
SELECT ST_GeomFromText('POINT(-122.4194 37.7749)') AS point;
SELECT ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') AS polygon;

-- Create from GeoHash
SELECT ST_PointFromGeoHash('9q8yy') AS point;

Geometry accessors

Extract properties and coordinates:

SELECT
  ST_X(location) AS lon,
  ST_Y(location) AS lat,
  ST_Area(boundary) AS area_sq_units,
  ST_Length(path) AS path_length,
  ST_GeometryType(geom) AS geom_type
FROM locations;

Spatial relationships

Test how geometries relate to each other:

-- Find stores within 10 km of a point (ST_DistanceSphere returns metres)
SELECT id, name
FROM stores
WHERE ST_DistanceSphere(
  ST_GeomFromText('POINT(-122.4194 37.7749)'),
  location
) < 10000;

-- Points contained in a polygon (e.g. delivery zones)
SELECT order_id, ST_AsText(destination)
FROM orders
WHERE ST_Within(destination, delivery_zone);

-- Intersecting geometries (e.g. overlapping regions)
SELECT a.id, b.id
FROM regions a
JOIN regions b ON ST_Intersects(a.geom, b.geom) AND a.id < b.id;

ST_Distance and ST_DWithin are planar—distances in the coordinates' own units (degrees for lon/lat data). For metres on lon/lat data use ST_DistanceSphere (great-circle) or ST_Distance_Spheroid / ST_DWithin_Spheroid (WGS84 geodesic).

Geometry processing

Compute derived geometries:

-- Convex hull of a geometry
SELECT id, ST_ConvexHull(geom) AS hull
FROM regions;

-- Simplify a polygon (Douglas-Peucker)
SELECT ST_Simplify(complex_geom, 0.001) AS simplified
FROM boundaries;

-- Centroid of a geometry
SELECT ST_Centroid(polygon) AS center FROM parcels;

-- Buffer a point by 1 unit
SELECT ST_Buffer(location, 1.0) AS service_area FROM stores;

-- Merge overlapping regions
SELECT ST_Union(a.geom, b.geom)
FROM regions a JOIN regions b ON ST_Intersects(a.geom, b.geom);

Coordinate reprojection

ST_Transform reprojects between coordinate reference systems. The CRS travels in the call (there is no per-geometry SRID); coordinates are always x = easting/longitude:

-- lon/lat (EPSG:4326) to Web Mercator metres (EPSG:3857)
SELECT ST_Transform(location, 'EPSG:4326', 'EPSG:3857') AS mercator
FROM stores;

Hotdata's spatial functions use PostGIS-style ST_ names; alternate spellings from other SQL dialects are accepted as aliases where they differ (e.g. ST_Distance_Sphere for ST_DistanceSphere). See the Spatial functions reference for the full set of 117 functions, and the PostGIS documentation for the semantics of individual functions.

Vector search

Hotdata provides vector similarity search with two approaches: semantic search on text columns using an embedding provider, and direct vector search on pre-computed embedding columns. When a vector index exists, queries are transparently rewritten into HNSW index calls—no special syntax needed.

Semantic search with vector_distance

vector_distance(column, 'search text') is the simplest way to perform semantic search. It takes a text column and a search string, automatically generates an embedding using the column's configured embedding provider, and computes the distance using the metric defined in the vector index.

SELECT id, title, vector_distance(description, 'machine learning frameworks') AS dist
FROM conn.public.articles
ORDER BY dist ASC
LIMIT 10;

Requirements:

• A vector index on the column with an embedding provider configured
• The column must be a text column (VARCHAR / TEXT)

Behind the scenes, vector_distance resolves the embedding provider from the vector index metadata, calls it to embed your search text, and rewrites the query to the appropriate distance function (e.g. cosine_distance) on the generated embedding column. You don't need to know which distance metric or embedding column is used—the index configuration handles it.

Distance functions

For direct control over vector search—when you already have embedding vectors or want to specify the exact distance metric—use the distance functions directly on vector columns. All are lower-is-closer; use ORDER BY ... ASC:

These functions accept a vector column and a literal query vector (ARRAY[...]):

SELECT id, title, cosine_distance(embedding, ARRAY[0.1, -0.2, 0.5, ...]) AS dist
FROM documents
ORDER BY dist ASC
LIMIT 10;

With a vector index: queries matching ORDER BY distance_fn(col, ARRAY[...]) ASC LIMIT k are transparently rewritten into an HNSW index call for fast approximate nearest-neighbor search.

Without a vector index: the distance functions still work via brute-force scan over all rows. This is useful for small tables or ad-hoc exploration, but much slower on large datasets.

A metric mismatch (e.g. cosine_distance on an L2-built index) falls back silently to brute-force. ORDER BY ... DESC is also never rewritten.

vector_search table function

vector_search is a table function for semantic search that returns full rows. Like vector_distance, it auto-embeds your search text using the configured embedding provider — but returns results as a table you can query directly.

vector_search('catalog.schema.table', 'column', 'search text', k)

Returns the table's columns plus a _distance column (Float32). Lower distance means more similar.

Two details of the returned shape are worth knowing before you write SELECT * against it:

• The result carries an internal row-addressing column (rowid) ahead of the table's own columns. It is an implementation detail of the index, not part of your table — list the columns you want explicitly rather than relying on SELECT *.
• The indexed embedding column is not among the columns you can select. The vector itself is not kept in the row-fetch path, so naming it — SELECT description_embedding FROM vector_search(...) — fails with an unknown-field error. Select the source text column instead.

Requirements: A vector index on the column with an embedding provider configured.

-- Semantic search returning full rows
SELECT id, title, _distance
FROM vector_search('mydb.public.articles', 'description', 'machine learning', 10)
ORDER BY _distance ASC;

-- With filtering and aggregation
SELECT category, COUNT(*) AS cnt, AVG(_distance) AS avg_dist
FROM vector_search('mydb.public.articles', 'description', 'data science', 50)
WHERE category IN ('ml', 'statistics')
GROUP BY category
ORDER BY avg_dist;

vector_search_vector table function

vector_search_vector is the direct ANN search table function — you provide the query vector yourself. It requires a vector index but no embedding provider.

vector_search_vector('catalog.schema.table', 'column', ARRAY[...], k)

Returns all table columns plus a _distance column (Float32).

Requirements: A vector index on the column.

SELECT id, title, _distance
FROM vector_search_vector('mydb.public.documents', 'embedding', ARRAY[0.1, -0.2, 0.5, ...], 10)
ORDER BY _distance ASC;

Choosing between vector search methods

Use vector_search or vector_distance when you have text and want automatic embedding. Use vector_search_vector or the distance functions when you already have vectors.

WHERE clause filtering

Scalar WHERE conditions are absorbed and applied adaptively at execution time when a vector index is present:

The 5% cutoff is a fixed engine default (not currently configurable per table).

SELECT id, title, cosine_distance(embedding, ARRAY[...]) AS dist
FROM documents
WHERE category = 'technical'
ORDER BY dist ASC
LIMIT 10;

Behavior and limitations

• expansion_search (ef_search): the beam width during HNSW traversal, a fixed engine default of 64 (higher would improve recall at the cost of speed).
• brute_force_selectivity_threshold: the selectivity cutoff below which brute-force is used instead of HNSW—a fixed engine default of 0.05 (5%).
• Literal query vectors: The optimizer only rewrites distance functions when the query vector is a compile-time literal (ARRAY[...]). Use vector_distance for text-based search.
• SELECT * with distance functions: When the table has generated embedding columns, SELECT * expands to include them. Since the HNSW index doesn't serve embedding columns, the query falls back to brute-force. Select specific columns to use the index, or use vector_search / vector_search_vector which always use HNSW.
• Stacked filters: Only one Filter → TableScan layer is absorbed.

Full-text search

Hotdata provides BM25 full-text search via the bm25_search table function. It performs ranked text retrieval against a column with a BM25 index.

bm25_search

bm25_search('catalog.schema.table', 'column', 'query text' [, limit])

Returns all columns from the base table plus a score column (Float32) containing the BM25 relevance score. Higher scores indicate stronger relevance. Rows are not returned in score order—add an explicit ORDER BY score DESC to rank them.

Requirements: A BM25 index on the target column.

-- Search articles for "machine learning", return top 20 by relevance
SELECT id, title, body, score
FROM bm25_search('mydb.public.articles', 'body', 'machine learning', 20)
ORDER BY score DESC;

-- Combine full-text search with additional filters
SELECT id, title, score
FROM bm25_search('conn.public.docs', 'content', 'kubernetes deployment', 100)
WHERE category = 'infrastructure'
ORDER BY score DESC
LIMIT 10;

Note: bm25_search retrieves its top-limit matches first, then any WHERE filters are applied to that set. When combining with filters, pass a higher limit to bm25_search than the final number of rows you need so relevant rows aren't filtered out of a too-small candidate set.

Managed table layout

Instant database tables can declare a storage layout—how their rows are partitioned and sorted on disk—when the table is created. Unlike an index, the layout is intrinsic to how the table's data is written. It is fixed at table creation (via the managed table API), cannot be changed afterward, and is not set from SQL.

• Partitioning — one or more partition keys, each a column plus a transform: identity, year, month, day, or hour. Transforms compose, so partitioning by year(ts) and month(ts) places each calendar month in its own partition. A WHERE filter on a partition column lets the planner skip whole partitions.
• Sort order — one or more sort keys (a column plus optional asc / desc and null ordering). Rows are written in this order, so range filters can prune row groups and an ORDER BY on the sort keys is satisfied without an explicit sort step.

Because the layout is applied at write time, it also organizes data for key-based mutations (upsert, update, delete) on partitioned tables. This is complementary to sorted indexes: a sorted index is a secondary, pre-sorted copy of cached data, whereas the declared layout governs the table's primary storage.

Indexes

Hotdata supports three index types to accelerate different query patterns. Indexes are created via the API or CLI—not SQL. Indexes are built over instant-database tables, so load the data first (upload parquet or run an ingest).

CLI (instant-database tables): hotdata search create --type text|vector|sorted --from <catalog>.<schema>.<table> --column <col>

Indexes are stored in the catalog and used automatically when the query planner finds a beneficial match. Use GET .../indexes to list indexes and DELETE .../indexes/{index_name} to drop one.

Sorted indexes

Pre-sorted Parquet copies of cached data, ordered by the columns you specify. Accelerate range queries, ordering, and point lookups.

{
  "index_name": "idx_created_at",
  "columns": ["created_at"],
  "index_type": "sorted"
}

A sorted index is selected when:

1. Filter match — a WHERE clause filters on the index's leading sort column. Sorted Parquet enables efficient row-group pruning for range and equality predicates.
2. Sort elimination — an ORDER BY on the index columns is satisfied without an explicit sort step.

-- Index on created_at: row-group pruning for range filter
SELECT * FROM events
WHERE created_at >= '2025-01-01' AND created_at < '2025-02-01';

-- Index on sku: sort elimination
SELECT * FROM products ORDER BY sku LIMIT 100;

Vector indexes

HNSW indexes for approximate nearest-neighbor search on vector columns. Require exactly one column.

On a pre-computed embedding column (float array):

{
  "index_name": "docs_embedding_idx",
  "columns": ["embedding"],
  "index_type": "vector",
  "metric": "cosine"
}

On a text column with auto-embedding: when the source column is text, Hotdata automatically generates embeddings using an embedding provider. This enables vector_distance() for semantic search.

If you omit embedding_provider_id, the system embedding provider is used automatically. If no system provider is configured, you must pass one explicitly. The metric also defaults to the provider's configured metric when not specified.

{
  "index_name": "docs_description_idx",
  "columns": ["description"],
  "index_type": "vector"
}

Or with explicit provider and metric:

{
  "index_name": "docs_description_idx",
  "columns": ["description"],
  "index_type": "vector",
  "metric": "cosine",
  "embedding_provider_id": "embp_abc123",
  "dimensions": 1536
}

The embedding provider generates a new column (default: {column}_embedding) containing the vector embeddings. Configure embedding providers via the Embedding Providers API.

An auto-embedding vector index must be the only index on its table. Creating one is rejected if the table already has any index, and once it exists no further index of any type can be added alongside it. Publishing an auto-embedding index rewrites the table's underlying storage, which would leave sibling indexes pointing at data that no longer matches. To combine full-text and vector search over the same content, keep them on separate tables.

This restriction applies only to auto-embedding indexes. A vector index over a pre-computed embedding column generates nothing, so it coexists with full-text and sorted indexes as normal.

CLI example:

hotdata search create --type vector --from mydb.public.documents \
  --column embedding --metric cosine

BM25 indexes

Full-text search indexes using BM25 scoring. Required for bm25_search().

{
  "index_name": "articles_body_idx",
  "columns": ["body"],
  "index_type": "bm25"
}

CLI example:

hotdata search create --type text --from mydb.public.articles \
  --column body

Point lookups

Queries that fetch a single row by equality (WHERE col = value with LIMIT 1) are classified as point lookups. Parquet bloom filter reads skip row groups that cannot contain the value, reducing I/O.

SELECT * FROM users WHERE id = 12345;

Index lifecycle

Indexes are invalidated when the underlying table is purged or resynced. After a data refresh, indexes are automatically rebuilt from the new cached data. If a rebuild fails, drop and recreate the index via the API.

Information schema

Introspect the catalog by querying the information_schema views:

• information_schema.tables — tables in scope
• information_schema.columns — columns and their types
• information_schema.schemata — schemas
• information_schema.catalogs — catalogs

SELECT table_schema, table_name
FROM information_schema.tables
ORDER BY table_schema, table_name;

DESCRIBE table returns a single table's schema:

DESCRIBE conn.public.orders;

The information_schema.views, routines, and parameters views exist for tool compatibility but are empty. SHOW TABLES and SHOW COLUMNS both work and return rows; SHOW FUNCTIONS is accepted but currently returns no rows, so use information_schema or DESCRIBE to inspect a table. You can also list tables and schemas via the API or MCP.

EXPLAIN

Use EXPLAIN to inspect the query plan:

EXPLAIN SELECT * FROM orders WHERE created_at > '2025-01-01';

EXPLAIN ANALYZE runs the query and returns execution metrics (timing, rows, etc.). Because it executes the plan, read-only enforcement still applies—you cannot run a write by wrapping it in EXPLAIN; the wrapped statement is inspected and rejected.

Further reading

• Function references — every category, with signatures and runnable examples
• PostGIS documentation — semantics of the ST_ spatial functions (Hotdata implements a planar-geometry subset)
• HNSW — approximate nearest-neighbor search