Filtering and paging

A filter is a query parameter of the form column=operator.value. This page is the complete reference for that grammar — every operator, how values are quoted, how or groups nest, how paging works, and what each mistake looks like when it comes back as a 400.

The shape of a filter

GET /rest/v1/articles?status=eq.open&score=gte.10&order=created_at.desc&limit=20

Every query parameter that is not a reserved name is a filter. Filters on separate parameters are ANDed. The same parameter may appear more than once, and those are ANDed too — ?score=gte.10&score=lt.100 is a range.

Six names are reserved and never treated as filters:

select   order   limit   offset   on_conflict   columns

Operators

Operator	SQL	Example
`eq`	`=`	`status=eq.open`
`neq`	`<>`	`status=neq.archived`
`gt` `gte` `lt` `lte`	`>` `>=` `<` `<=`	`score=gte.10`
`like`	`LIKE`, with `*` as the wildcard	`title=like.sql`
`ilike`	`ILIKE`	`title=ilike.SQL`
`match`	`~` (POSIX regex)	`slug=match.^post-`
`imatch`	`~*`	`slug=imatch.^POST-`
`in`	`= ANY(…)`	`id=in.(1,2,3)`
`is`	`IS`	`deleted_at=is.null`
`isdistinct`	`IS DISTINCT FROM`	`a=isdistinct.b`
`fts`	`@@ to_tsquery()`	`body=fts(english).cat & dog`
`plfts`	`@@ plainto_tsquery()`	`body=plfts.cats and dogs`
`phfts`	`@@ phraseto_tsquery()`	`body=phfts(english).black cat`
`wfts`	`@@ websearch_to_tsquery()`	`body=wfts(english).cats -dogs`
`cs`	`@>` contains	`tags=cs.{sql,db}`
`cd`	`<@` contained by	`tags=cd.{sql,db,ops}`
`ov`	`&&` overlaps	`period=ov.[2026-01-01,2026-02-01)`
`sl`	`<<` strictly left of	`period=sl.[2026-03-01,2026-04-01)`
`sr`	`>>` strictly right of	`period=sr.[2026-01-01,2026-02-01)`
`nxr`	`&<` does not extend to the right of	`period=nxr.[2026-01-01,2026-02-01)`
`nxl`	`&>` does not extend to the left of	`period=nxl.[2026-01-01,2026-02-01)`
`adj`	`-\|-` is adjacent to	`period=adj.[2026-02-01,2026-03-01)`

Negation

Prefix any operator with not.:

?status=not.eq.archived
?tags=not.cs.{draft}
?deleted_at=not.is.null

It compiles to NOT (…) around the whole condition, which is not the same as the inverse operator when NULLs are involved. status=neq.archived excludes rows where status is NULL; status=not.eq.archived also excludes them, because NOT (NULL = 'archived') is NULL and NULL is not true. To include NULLs, ask for them: ?or=(status.neq.archived,status.is.null).

Testing for null

?deleted_at=is.null        # IS NULL       — correct
?published=is.true         # IS TRUE
?verified=is.false         # IS FALSE
?flag=is.unknown           # IS UNKNOWN
?deleted_at=eq.null        # = NULL        — never true, for anything

is accepts exactly those four keywords; anything else is a 400:

{"error":{"code":"bad_request","message":"\"is\" expects null, true, false or unknown, got \"NULL()\"","details":null}}

The value grammar

You write	The server sends to Postgres
`eq.42`	the text `42`, coerced by Postgres from the column type
`eq.null`	SQL `NULL`
`eq."null"`	the four-character string `null`
`eq."Smith, J."`	the string `Smith, J.` — quoting is what keeps the comma out of the grammar
`eq."say \\"hi\\""`	the string `say "hi"` — a backslash escapes a quote or a backslash
`cs.{sql,db}`	the Postgres array literal `{sql,db}`, verbatim
`like.sql`	`%sql%` — `*` is rewritten to `%` for `like` and `ilike` only

Range, array and jsonb operators — cs, cd, ov, sl, sr, nxr, nxl, adj — take a Postgres literal on the right and are bound verbatim, because stripping quotes from {"a":1} or [2026-01-01,2026-02-01) would corrupt it.

URL encoding

The value lives in a query string, so &, #, + and % must be percent-encoded. A literal + is especially worth remembering: in a query string it decodes to a space.

# wrong: the phone number becomes " 14155552671"
curl "$URL/rest/v1/contacts?phone=eq.+14155552671" -H "apikey: $ANON_KEY"

# right
curl --get "$URL/rest/v1/contacts" --data-urlencode 'phone=eq.+14155552671' -H "apikey: $ANON_KEY"

Logic trees

Top-level filters are ANDed. For anything else there is or= and and=, and they nest.

?or=(status.eq.draft,and(views.gte.100,pinned.is.true))
?and=(score.gte.10,score.lt.100)
?not.and=(archived.is.true,owner.eq.me)
?not.or=(status.eq.spam,status.eq.deleted)

Which compiles to:

("t"."status" = $1 OR ("t"."views" >= $2 AND "t"."pinned" IS TRUE))

Inside a group the value ends at the first top-level , or ), so a value containing either must be double-quoted:

?or=(name.eq."Smith, J.",name.eq."O'Neill")

A column whose own name contains a dot or a comma is quoted the same way:

?or=("weird.name".eq.1,other.eq.2)

Nesting is capped at 20 levels, so a hostile or=(or=(or=(…))) cannot exhaust the stack. Beyond that:

{"error":{"code":"bad_request","message":"filter nesting is too deep at position 41 of \"…\"","details":null}}

Full-text search

The four full-text operators take an optional text search configuration in parentheses. Without one, Postgres uses default_text_search_config.

alter table public.articles
  add column search tsvector
  generated always as (to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''))) stored;

create index articles_search_idx on public.articles using gin (search);

curl --get "$URL/rest/v1/articles" \
  --data-urlencode 'search=wfts(english).postgres -mysql' \
  --data-urlencode 'select=id,title' \
  -H "apikey: $ANON_KEY"

[{"id":9,"title":"Indexes for policies"},{"id":4,"title":"Two databases"}]

Operator	Parser	Good for
`wfts`	`websearch_to_tsquery`	A search box. Understands quotes, `or` and `-`, and never raises on odd input.
`plfts`	`plainto_tsquery`	Plain words, all ANDed
`phfts`	`phraseto_tsquery`	An exact phrase
`fts`	`to_tsquery`	Raw tsquery syntax — `cat & !dog`. Malformed input raises.

The configuration name must match [A-Za-z_][A-Za-z0-9_]*; anything else is a 400 invalid text search configuration rather than a Postgres error.

Ordering

?order=created_at.desc
?order=priority.desc.nullslast,created_at.asc

Modifiers: asc, desc, nullsfirst, nullslast. Terms are applied in the order written. Anything else is a 400:

{"error":{"code":"bad_request","message":"unknown order modifier \"descending\" — use asc, desc, nullsfirst or nullslast","details":null}}

Paging

?limit=20&offset=40

Or the Range header, which is what .range(from, to) in the client sends:

curl -s "$URL/rest/v1/articles?order=created_at.desc" \
  -H "apikey: $ANON_KEY" \
  -H 'range: items=40-59' -i

HTTP/1.1 200 OK
content-range: 40-59/*

Rule	Value
Default `limit`	`1000`
Maximum `limit`	`10000` — an unbounded select must not be able to exhaust memory
Precedence	An explicit `limit`/`offset` wins over a `Range` header
Range is inclusive	`items=0-19` is the first twenty rows

Content-Range is on every read. The total is * unless you ask for it:

curl -s "$URL/rest/v1/articles?limit=20" -H "apikey: $ANON_KEY" -H 'prefer: count=exact' -i

content-range: 0-19/347

The count runs in the same transaction as the page, so 347 is the number of rows your policies allow, not the table's raw row count. It costs a second aggregate over the same predicate: leave it off for infinite scroll, use it for page numbers.

Keyset paging

OFFSET makes Postgres walk and discard every skipped row, so page 500 is slower than page 1 and rows shift under a concurrent insert. For a long list, filter on the last row you saw instead:

# page 1
?select=id,title,created_at&order=created_at.desc,id.desc&limit=20

# page 2 — everything strictly older than the last row of page 1
?select=id,title,created_at&order=created_at.desc,id.desc&limit=20&created_at=lt.2026-08-14T08:00:00Z

Add id as the tiebreaker so two rows with the same timestamp cannot both be skipped or both be repeated.

Filters on writes

PATCH and DELETE use the same grammar to choose their rows, and at least one filter is required.

curl -s -X PATCH "$URL/rest/v1/articles?status=eq.draft&created_at=lt.2026-01-01" \
  -H "apikey: $ANON_KEY" -H "authorization: Bearer $TOKEN" \
  -H 'content-type: application/json' \
  -d '{"status":"archived"}'

What a bad filter looks like

Request	Response
`?titel=eq.x`	`400 bad_request` — `unknown column "titel" on public.articles`
`?title=contains.x`	`400 bad_request` — `unknown operator "contains"`
`?id=in.1,2,3`	`400 bad_request` — `the "in" operator takes a parenthesised list, got "1,2,3"`
`?or=(a.eq.1`	`400 bad_request` — `unbalanced parentheses: missing ")" at position 6`
`?or=()`	`400 bad_request` — `empty logical group`
`?limit=-5`	`400 bad_request` — `limit must be a non-negative integer, got "-5"`
`Range: bytes=0-19`	`400 bad_request` — `malformed Range header … expected items=<start>-<end>`
`Range: items=20-5`	`400 bad_request` — `Range end 5 is before its start 20`
`?age=eq.old` on an `integer`	`400 invalid_text_representation` — SQLSTATE `22P02`

In the client

const { data, error, count } = await bl
  .from('articles')
  .select('id,title,tags', { count: 'exact' })
  .eq('published', true)
  .neq('kind', 'draft')
  .gte('score', 10)
  .ilike('title', '*sql*')
  .is('deleted_at', null)
  .in('category', ['db', 'ops'])
  .contains('tags', ['sql'])
  .not('state', 'eq', 'archived')
  .or('status.eq.draft,and(views.gte.100,pinned.is.true)')
  .textSearch('search', 'postgres -mysql', { type: 'websearch', config: 'english' })
  .order('created_at', { ascending: false, nullsFirst: false })
  .range(0, 19);

Every builder method maps one-to-one onto the grammar above, and .filter(column, operator, value) reaches any operator without a named method. To see the URL a chain would send without sending it:

console.log(bl.from('articles').select('id').eq('published', true).limit(2).build());
// { method: 'GET', path: '/rest/v1/articles?select=id&published=eq.true&limit=2', headers: {}, body: undefined }

Edit this page Report a problem