REST Query Syntax

The REST→SQL engine speaks PostgREST query syntax. This is the complete reference for filters, modifiers, and headers. The Supabase SDKs build these for you; this page is for raw HTTP and for understanding exactly what the SDK sends.

Filters take the form column=operator.value, combined with &.

Filter operators

Operator	SQL	Example
`eq`	`=`	`status=eq.published`
`neq`	`<>`	`status=neq.draft`
`gt`	`>`	`views=gt.100`
`gte`	`>=`	`views=gte.100`
`lt`	`<`	`price=lt.50`
`lte`	`<=`	`price=lte.50`
`like`	`LIKE` (`*` = `%`)	`title=like.intro`
`ilike`	`ILIKE` (case-insensitive)	`title=ilike.intro`
`match`	`~` (POSIX regex)	`title=match.^intro`
`imatch`	`~*` (POSIX regex, case-insensitive)	`title=imatch.^intro`
`in`	`IN (…)`	`id=in.(1,2,3)`
`is`	`IS` (null/true/false/unknown)	`deleted_at=is.unknown`
`not`	negation prefix	`status=not.eq.draft`
`l2` `cosine` `ip`	pgvector distance (`<->` `<=>` `<#>`) — AnvilBase extension	`embedding=l2.[1,0,0]`

Full-text (fts/plfts/phfts/wfts), array/range (cs/cd/ov/sl/sr/…), and like(all)/ilike(any) quantifiers are also supported.

The is operator accepts only null, true, false, or unknown; any other value returns 400.

Negation

Prefix any operator with not.:

status=not.in.(draft,archived)
title=not.ilike.*spam*

Selecting columns

?select=*                       all columns
?select=id,title,created_at     specific columns

JSON & JSONB columns

Address a nested key inside a json/jsonb column with the PostgreSQL arrow operators, in filters, select, and order:

-> returns json/jsonb (keep drilling deeper).
->> returns text (compare or project a scalar).

?meta->>tier=eq.gold             filter on meta->>'tier'
?select=id,meta->>tier           project meta->>'tier' (field named "tier")
?order=meta->>rank.desc          order by meta->>'rank'
?select=id,meta->stats->>count   multi-hop path
?select=tags->0                  integer array index

An unaliased JSON-path select item is named after its last key (meta->>tier → field tier), matching PostgREST; a path ending in an integer index has no natural name — alias it explicitly (first:tags->0).

Restriction: path keys must be bare identifiers ([a-zA-Z_][a-zA-Z0-9_]*) or integer indices. Quoted keys (meta->>"key with spaces") are not yet supported and return 400.

Embedded resources (joins)

Embed FK-related tables inside select; the engine resolves the foreign key and returns nested JSON.

?select=*,author:authors(name)              many-to-one → single object
?select=*,comments(*)                       one-to-many → array
?select=*,posts(*,comments(*))              nested (multi-level)
?select=*,posts!author_id(*)                disambiguate the FK by column/constraint
?select=*,posts!inner(*)                    inner join (drop base rows with no match)

Aliases: alias:table(cols) renames the embedded key in the response.
Depth cap: nesting beyond 5 levels returns 400 (PGRST204).

Embed-level modifiers

Prefix a modifier with the embed name (or its alias) and a dot to scope it inside the embedded relation (it shapes the embedded rows without dropping the base row):

?posts.order=created_at.desc                order the embedded posts
?posts.limit=10&posts.offset=20             paginate the embedded posts
?posts.status=eq.published                  filter the embedded posts
?posts.comments.order=id.desc               modifier on a nested embed

The Supabase clients emit these via the referencedTable/foreignTable option (e.g. .order('created_at', { referencedTable: 'posts', ascending: false })).

Many-to-many (junction tables)

For base ↔ junction ↔ embed relationships, embed across the junction. A single unambiguous junction is inferred; otherwise name it with the ! hint:

?select=*,tags(*)                           inferred junction (one path only)
?select=*,tags!posts_tags(*)                explicit junction table

If multiple junction tables link the two relations, the hint is required — an ambiguous embed returns 400.

Vector ordering (pgvector)

AnvilBase extension. For a vector column, order rows by distance to a query vector — the primitive for nearest-neighbor / semantic search:

?order=embedding.l2.[1,0,0]                 Euclidean (L2) distance,  <->
?order=embedding.cosine.[1,0,0]             cosine distance,          <=>
?order=embedding.ip.[1,0,0]                 (negative) inner product, <#>
?order=embedding.l2.[1,0,0].desc            farthest-first (default is nearest)

The query vector literal is a bracketed list of numbers and is bound as a ::vector parameter (never interpolated). These operators are also accepted as filters (embedding=l2.[1,0,0] → embedding <-> '[1,0,0]'::vector), an AnvilBase extension — PostgREST has no standard vector filter operator, so ordering is the portable form for nearest-neighbor search.

Ordering

?order=created_at.desc
?order=pinned.desc,created_at.desc      multiple keys, left-to-right

Pagination

?limit=20                       cap rows
?offset=40                      skip rows
?limit=20&offset=40             rows 41–60

limit/offset must be non-negative integers; a negative value returns 400.

The SDK’s .range(from, to) maps to limit/offset (inclusive bounds).

Raw clients may also send the native PostgREST Range header (unit items):

Range-Unit: items
Range: 0-24                     rows 0–24 (25 rows, inclusive)

Query-param limit/offset always win over the header. Reads are capped at ANVILBASE_REST_MAX_ROWS (default 1000); the Content-Range total still reports the true count so clients know when to page.

Request headers

Header	Effect
`apikey: <key>`	project API key (required)
`Authorization: Bearer <jwt>`	run as a signed-in user
`Content-Type: application/json`	for POST/PATCH bodies
`Prefer: return=representation`	return affected rows on writes
`Prefer: return=minimal`	return nothing (default)
`Prefer: count=exact\|planned\|estimated`	include a count (in `Content-Range`); on writes it is the affected-row count (`*/N`)
`Prefer: resolution=merge-duplicates\|ignore-duplicates`	upsert behavior
`Range: <start>-<end>` / `Range-Unit: items`	window a read (rows inclusive, zero-based); query-param `limit`/`offset` override it
`Accept: application/vnd.pgrst.object+json`	return a single object, not an array

Upsert

POST /v1/rest/<id>/<table>?on_conflict=<column>
Prefer: resolution=merge-duplicates

on_conflict names the unique column/constraint; resolution chooses update vs. ignore.

RPC

POST /v1/rest/<id>/rpc/<function>
{ "arg1": ..., "arg2": ... }       # named parameters in the body

Set-returning functions respond as a JSON array. See RPC.

Worked examples

BASE="https://<host>/v1/rest/<project_id>"; KEY="$ANON_KEY"

# Published posts, newest first, columns trimmed, first 10
curl "$BASE/posts?select=id,title,created_at&status=eq.published&order=created_at.desc&limit=10" -H "apikey: $KEY"

# Search titles, exclude drafts, with a total count
curl -i "$BASE/posts?title=ilike.*launch*&status=not.eq.draft" -H "apikey: $KEY" -H "Prefer: count=exact"

# A single row as an object
curl "$BASE/posts?id=eq.$ID" -H "apikey: $KEY" -H "Accept: application/vnd.pgrst.object+json"

# Insert and return the row
curl -X POST "$BASE/posts" -H "apikey: $KEY" -H "Content-Type: application/json" \
  -H "Prefer: return=representation" -d '{"title":"Hi","status":"draft"}'

# Upsert by slug
curl -X POST "$BASE/posts?on_conflict=slug" -H "apikey: $KEY" -H "Content-Type: application/json" \
  -H "Prefer: resolution=merge-duplicates" -d '{"slug":"hi","title":"Hi v2"}'

# Update matching rows
curl -X PATCH "$BASE/posts?id=eq.$ID" -H "apikey: $KEY" -H "Content-Type: application/json" \
  -d '{"status":"published"}'

# Delete matching rows
curl -X DELETE "$BASE/posts?status=eq.spam" -H "apikey: $SERVICE_KEY"

Notes & limits

Writes require a filter (PATCH/DELETE) — the engine guards against whole-table operations.
Logical or=/and= groups work on writes too — a PATCH/DELETE may add an or=(…) / and=(…) group alongside its required flat filter; all parts are AND-combined, so the group only narrows the affected rows.
Identifiers are validated and quoted; values are bound as parameters (injection-safe).
in.() (an empty list) returns an empty set — e.g. .in('id', []) yields [], never a type error, on any column type.
Quoted in elements may contain commas: id=in.("a,b",c) filters on the two values a,b and c.
Results are RLS-filtered by your credential’s scope (Row Level Security).

See also Reading Data and Modifying Data for SDK equivalents.