# SQL and PostgreSQL

You can query a collection with a single SQL `SELECT`. The `FROM` clause names the collection, `WHERE` filters and
searches, `ORDER BY` sorts, and `GROUP BY` turns into facets. Two dialects are accepted:

| `language` | Dialect | Adds |
|---|---|---|
| `sql` | MySQL | `MATCH ... AGAINST` and related full-text functions |
| `pgsql` | PostgreSQL | `tsvector` / `tsquery` full-text search, pgvector distance operators, casts, `date_trunc` |

```bash
curl -X POST https://api.warpsearch.io/v1/search \
  -H "Authorization: Bearer $WARPSEARCH_API_KEY" \
  -H "Content-Type: application/json" \
  --data @- <<'EOF'
{"language": "pgsql",
 "query": "SELECT title, year FROM books WHERE to_tsvector(title) @@ websearch_to_tsquery('dune -messiah') ORDER BY year DESC LIMIT 10"}
EOF
```

SQL is accepted wherever a search is: `POST /v1/search`, `POST /v1/collections/{c}/search`,
`GET /v1/collections/{c}/search?language=pgsql&q=...`, `POST /v1/collections/{c}/count`, `/v1/translate`, and the MCP
`search`, `count` and `translate_query` tools. When the request also names a collection, it must match `FROM`.

There is no PostgreSQL or MySQL wire protocol. psql, JDBC and ODBC drivers, BI tools and Grafana's PostgreSQL data
source cannot connect. Send the query over HTTP or MCP.

## What a query returns

The response is the same as for any other [search](searching.md): `hits`, `facets`, `metrics` and `total`.

- The `SELECT` columns become the returned fields. `SELECT *` returns every field you are allowed to see.
- A query with only aggregates, such as `SELECT COUNT(*), AVG(price) FROM products WHERE ...`, returns no hits, an
  exact `total` and the aggregates under `metrics`.
- `GROUP BY` returns groups as facet buckets, with the aggregates as metrics of each bucket.

```sql
SELECT category, COUNT(*), AVG(price) AS avg_price FROM products
WHERE MATCH(title) AGAINST('running')
GROUP BY category
```

```json
{"total": 3, "hits": [],
 "facets": {"category": {"type": "terms", "field": "category", "buckets": [
   {"value": "shoes", "count": 2, "metrics": {"avg_price": 104.75}},
   {"value": "apparel", "count": 1, "metrics": {"avg_price": 12}}]}}}
```

## Both dialects

| Feature | Notes |
|---|---|
| `=`, `<>` / `!=`, `<`, `<=`, `>`, `>=` | On a `text` field `=` is a phrase match. On `string`, number and date fields it is exact. |
| `IN`, `NOT IN`, `BETWEEN`, `IS [NOT] NULL`, `IS [NOT] TRUE` / `FALSE`, `AND`, `OR`, `NOT` | A bare boolean column means `col = true`. `col = NULL` matches nothing, as in SQL. |
| `LIKE`, `NOT LIKE`, `ILIKE`, with `ESCAPE` | `'abc%'` is a prefix query. Other patterns are wildcard queries (`%` and `_`), which depend on your plan. `ILIKE` matches case-sensitively, with a warning; to match a `string` field case-insensitively, give it a `lowercase` [normalizer](schemas-and-fields.md). |
| `COUNT(*)`, `COUNT(col)`, `AVG`, `SUM`, `MIN`, `MAX` | |
| `GROUP BY a, b` | Nested terms facets, 10 groups per level by default. `LIMIT` sets the number of groups at the first level. |
| `HAVING COUNT(*) >= n` | Only `COUNT(*)` with `>` or `>=`. |
| `ORDER BY` | Columns, aliases and `_score`. With `GROUP BY`, an aggregate or its alias orders the groups. The default is by count, descending. |
| `SELECT DISTINCT col` | One column, returned as a terms facet. |
| `LIMIT n OFFSET m` | `OFFSET` cannot be combined with `GROUP BY`. |

Regular expressions and wildcard patterns depend on your plan and answer `403 forbidden` when it does not include
them. See [Limits and errors](limits-and-errors.md).

## PostgreSQL (`pgsql`)

```sql
SELECT title, year FROM books
WHERE to_tsvector(title) @@ to_tsquery('dune & !messiah')
  AND author ILIKE 'frank%' AND year BETWEEN 1960 AND 1980
  AND genre = ANY(ARRAY['scifi', 'fantasy'])
ORDER BY ts_rank(title, to_tsquery('dune')) DESC LIMIT 10 OFFSET 20
```

### Full-text search

| PostgreSQL | What it does |
|---|---|
| `to_tsvector(col) @@ to_tsquery('a & b \| !c')` | `&` requires a term, `\|` makes it optional, `!` excludes it. Parentheses group. `term:*` is a prefix and `a <-> b` a phrase. |
| `plainto_tsquery('a b')` | All words must match. |
| `phraseto_tsquery('a b')` | The words as a phrase. |
| `websearch_to_tsquery('"a b" -c or d')` | Quoted phrases, `-` to exclude, `or` between alternatives. |
| `col @@ 'query'`, `'query'::tsquery` | Same as `to_tsquery` on that column. |
| `to_tsvector(a \|\| ' ' \|\| b)` | Search several fields at once. `coalesce(...)` around a column is accepted. |
| `ts_rank(...)`, `ts_rank_cd(...)` | The relevance score, which is BM25. In `ORDER BY` it sorts by score. |

Each field is searched with its own [analyzer](schemas-and-fields.md#analyzers). A text search configuration such
as `'english'` is ignored with a warning, and so are weights (`setweight`, `:A`). Inside a `<->` phrase only plain
terms are allowed, and `<N>` distances other than `<->` are not supported.

### Vector search with pgvector operators

`ORDER BY` a distance and `LIMIT` k run a nearest-neighbor search on a `vector` field:

```sql
SELECT id, title FROM docs
WHERE lang = 'en' AND embedding <=> '[0.12, -0.03, 0.44]' < 0.4
ORDER BY embedding <=> '[0.12, -0.03, 0.44]'
LIMIT 10
```

- `<->` is L2 distance, `<=>` cosine distance and `<#>` negative inner product. The operator must match the
  field's `metric`; a mismatch is an error. The L1 operator `<+>` is not supported.
- Vectors can be written `'[1,2,3]'`, `ARRAY[1,2,3]` or `'[1,2,3]'::vector`.
- k is `LIMIT` plus `OFFSET`, 10 when there is no `LIMIT`.
- Every `AND` condition in `WHERE` filters inside the vector search, so you get k matching results rather than k
  results filtered afterwards.
- A bound such as `embedding <=> v < 0.4`, on the same field, operator and vector as the `ORDER BY`, drops neighbors
  beyond that distance. It is converted to a [similarity threshold](vector-search.md#similarity-thresholds).
- The distance can be selected (`embedding <-> v AS dist`), but the value returned is the hit's similarity score,
  not the distance. A warning says so.

The distance must be the only `ORDER BY` key, ascending. It cannot be combined with `GROUP BY` or aggregates, and a
distance cannot be used in `WHERE` without the matching `ORDER BY`. See [Vector search](vector-search.md) for indexes,
recall and filters.

### Other PostgreSQL features

| Feature | Notes |
|---|---|
| `~`, `~*`, `!~`, `!~*` | Regular expressions. `~*` matches case-sensitively, with a warning. |
| `= ANY(ARRAY[...])`, `= ANY('{a,b}')`, `<> ALL(...)` | Like `IN` and `NOT IN`. |
| `BETWEEN SYMMETRIC`, `ISNULL`, `NOTNULL`, `IS UNKNOWN` | |
| `'2024-01-01'::date`, `CAST(x AS int)` | Casts convert literals. A cast on a column is ignored with a warning. |
| `now()`, `current_timestamp`, `current_date`, `date_trunc('day', now())` | |
| `now() - interval '7 days'`, `'7 days'::interval` | One unit per interval. `'1 day 2 hours'` is not supported; write `'26 hours'`. |
| `GROUP BY date_trunc('day', ts)` | A date histogram. Units: second, minute, hour, day, week, month, quarter, year. Only as the first `GROUP BY` key, and `LIMIT` does not apply to it. |
| `COUNT(DISTINCT col)` | Approximate (HyperLogLog, exact below about 3,000 values), with a warning. |
| `FETCH FIRST n ROWS ONLY`, `LIMIT ALL`, `NULLS LAST` | `NULLS FIRST` is ignored with a warning; missing values sort last. |
| `"Quoted"` identifiers, table aliases, `ORDER BY 1` | Unquoted identifiers are lowercased, as in PostgreSQL. |

## MySQL (`sql`)

```sql
SELECT title, year FROM books
WHERE year BETWEEN 1960 AND 1980 AND genre IN ('scifi', 'fantasy')
  AND MATCH(title) AGAINST('dune') AND author LIKE 'Frank%'
ORDER BY year DESC LIMIT 10 OFFSET 20
```

| Feature | Notes |
|---|---|
| `MATCH(col, ...) AGAINST('words')` | Matches the words in any of the columns. |
| `MATCH(...) AGAINST('...' IN BOOLEAN MODE)` | `+`, `-` and quotes, like a simple query string. |
| `match_query(col, 'words')`, `match_phrase(col, 'exact words')` | |
| `query_string('title:dune AND year:>1960')` | [Lucene syntax](query-languages.md#lucene) over the default fields. Also `simple_query_string`. |
| `score()` | The relevance score, in `SELECT` or `ORDER BY`. |
| `REGEXP`, `NOT REGEXP` | Regular expressions. |
| `<=>` | Null-safe equals. `col <=> NULL` matches documents without the field. |
| `NOW()`, `CURDATE()`, `NOW() - INTERVAL 7 DAY`, `DATE_SUB(...)` | Date math. ISO-8601 strings compare as dates. |

`COUNT(DISTINCT ...)` is not supported in `sql`; use `pgsql` for an approximate count.

## Not supported

These return `400`:

- Joins, subqueries, `WITH` (CTEs), `UNION`, `INTERSECT`, `EXCEPT`, window functions, `ROLLUP` and `CUBE`.
- Functions or arithmetic on columns (`lower(title) = ...`, `price * 2`), `CASE`, and comparing two columns.
- Aggregates other than `COUNT`, `AVG`, `SUM`, `MIN` and `MAX`, such as `percentile_cont`, `array_agg` and
  `string_agg`.
- `DISTINCT ON`, `SIMILAR TO`, `IS DISTINCT FROM`, `ts_headline`, `ORDER BY random()`.
- `$1` parameters and prepared statements. Inline the values, and note that dollar-quoted strings (`$$...$$`) are not supported either.
- Anything but `SELECT`: no `INSERT`, `UPDATE`, `DELETE`, transactions or DDL. Create collections and write
  documents through the [REST API](api-reference.md).
- Schema-qualified names such as `public.books`.

The same query in other languages, and how to see what WarpSearch made of yours, are on
[Query languages](query-languages.md).