For AI agents: this page is also available as Markdown at /docs/sql.md. Every docs page is: add .md to its URL. llms.txt lists them all.

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:

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

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

{"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

Regular expressions and wildcard patterns depend on your plan and answer 403 forbidden when it does not include them. See Limits and errors.

#PostgreSQL (pgsql)

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

Each field is searched with its own analyzer. 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:

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.
• 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 for indexes, recall and filters.

#Other PostgreSQL features

#MySQL (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

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

View this page as Markdown