# SQL Reference

Source: https://www.hotdata.dev/docs/sql
Site index: https://www.hotdata.dev/llms.txt

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)).

## 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.

| `dialect` | Dialect |
| --- | --- |
| `hotsql` (default) | HotSQL |
| `postgres` | PostgreSQL |
| `duckdb` | DuckDB |
| `snowflake` | Snowflake |

```json
{
  "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:

```sql
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:

```sql
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:

```sql
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:

```sql
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):

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

### Subqueries

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

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

## Data types

Standard SQL types are supported:

| Category | Types |
|----------|-------|
| Numeric | `TINYINT`, `SMALLINT`, `INT`, `INTEGER`, `BIGINT`, `DECIMAL`, `FLOAT`, `DOUBLE`, `REAL` |
| Character | `CHAR`, `VARCHAR`, `STRING` |
| Date/Time | `DATE`, `TIME`, `TIMESTAMP`, `INTERVAL` |
| Boolean | `BOOLEAN` |
| Binary | `BYTEA` |
| Complex | `ARRAY`, `STRUCT`, `MAP` |

`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](/docs/sql-functions-math)
- [String](/docs/sql-functions-string) — text, regular-expression, and hashing functions
- [Date & time](/docs/sql-functions-datetime)
- [Conditional & utility](/docs/sql-functions-conditional)
- [Aggregate](/docs/sql-functions-aggregate)
- [Window](/docs/sql-functions-window)
- [Array, struct & map](/docs/sql-functions-array)
- [JSON](/docs/sql-functions-json) — path access with `->` and `->>`
- [Spatial](/docs/sql-functions-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 ...)`:

```sql
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 ...)`:

```sql
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:

```sql
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:

```sql
-- 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:

```sql
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:

```sql
-- 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:

```sql
-- 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:

```sql
-- 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](/docs/sql-functions-spatial) for the full set of 117 functions, and the [PostGIS documentation](https://postgis.net/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.

```sql
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](#vector-indexes) 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`:

| SQL function | Metric | Use case |
|---|---|---|
| `l2_distance(column, query)` | L2 (Euclidean) | General-purpose distance |
| `cosine_distance(column, query)` | Cosine | Angle between vectors (normalized embeddings) |
| `negative_dot_product(column, query)` | Inner product | Maximum inner product search |

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

```sql
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)
```

| Parameter | Type | Description |
|---|---|---|
| table | string literal | Fully-qualified table name: `'catalog.schema.table'` |
| column | string literal | Text column with a vector index and embedding provider |
| query | string literal | Search text to embed and search for |
| k | integer | Number of nearest neighbors to return |

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](#vector-indexes) on the column with an embedding provider configured.

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

```sql
-- 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)
```

| Parameter | Type | Description |
|---|---|---|
| table | string literal | Fully-qualified table name: `'catalog.schema.table'` |
| column | string literal | Vector column with a HNSW index |
| query | `ARRAY[...]` literal | Query vector |
| k | integer | Number of nearest neighbors to return |

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

**Requirements:** A [vector index](#vector-indexes) on the column.

```sql
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

| Method | Input | Requires embedding provider | Requires index | Returns |
|---|---|---|---|---|
| `vector_distance(col, 'text')` | text | yes | yes | scalar (`Float32`) — use in SELECT/ORDER BY |
| `vector_search('table', 'col', 'text', k)` | text | yes | yes | table (see [returned shape](#vector_search-table-function)) |
| `vector_search_vector('table', 'col', ARRAY, k)` | vector | no | yes | table (see [returned shape](#vector_search-table-function)) |
| `l2_distance` / `cosine_distance` / `negative_dot_product` | vector | no | optional (HNSW if present, brute-force otherwise) | scalar (`Float32`) — use in SELECT/ORDER BY |

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:

| Selectivity | Strategy | Behavior |
|---|---|---|
| > 5% | In-graph filtering | HNSW filtered search skips non-passing nodes; returns exactly k results |
| ≤ 5% | Brute-force subset | HNSW bypassed; exact distances over the valid subset; heap-select top-k |

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

```sql
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])
```

| Parameter | Type | Description |
|---|---|---|
| table | string literal | Fully-qualified table name: `'catalog.schema.table'` |
| column | string literal | Text column to search |
| query | string literal | Search query text |
| limit | integer (optional) | Maximum results to return. Default: `1000` |

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](#bm25-indexes) on the target column.

```sql
-- 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;
```

```sql
-- 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](/docs/api-reference/databases#add-table-to-database-default-catalog)), 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](/docs/api-reference/databases#load-database-table-from-inline-data-upload-or-query-result) (`upsert`, `update`, `delete`) on partitioned tables. This is complementary to [sorted indexes](#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](/docs/api-reference/indexes) or [CLI](/docs/cli-reference#search)—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.

```json
{
  "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.

```sql
-- 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):

```json
{
  "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()`](#semantic-search-with-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.

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

Or with explicit provider and metric:

```json
{
  "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](/docs/api-reference/embedding-providers).

**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.

| Field | Required | Description |
|---|---|---|
| `metric` | no | Distance metric: `l2`, `cosine`, or `dot`. Defaults to `l2` for float array columns, or the provider's metric for text columns with auto-embedding |
| `embedding_provider_id` | no | Embedding provider ID. Defaults to the system provider for text columns |
| `output_column` | no | Name for the generated embedding column. Default: `{column}_embedding` |
| `dimensions` | no | Embedding dimensions (model-dependent) |

**CLI example:**

```sh
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()`](#bm25_search).

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

**CLI example:**

```sh
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.

```sql
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

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

`DESCRIBE table` returns a single table's schema:

```sql
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:

```sql
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](#functions) — every category, with signatures and runnable examples
- [PostGIS documentation](https://postgis.net/documentation/) — semantics of the `ST_` spatial functions (Hotdata implements a planar-geometry subset)
- HNSW — approximate nearest-neighbor search