---
title: SQL Function Reference
description: User-facing SQL functions exposed by ParadeDB and pg_search
canonical: https://www.paradedb.com/docs/reference/sql-functions
---
This page lists the callable, user-facing SQL functions and function-like casts that make up ParadeDB's SQL API. It intentionally omits planner support functions, type input/output functions such as `pdb.simple_in`, and generated cast helper names. Use the casts documented here instead of calling those helpers directly.
Most new queries should use the `pdb` schema. A smaller set of operational and lower-level functions lives in the `paradedb` schema.
## Query Builders
Query builders return a ParadeDB query object for the right-hand side of `@@@`.
| Function | Signature | Use | Reference |
| ----------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------------------------- | ----------------------------------------------------------------------------------- |
| `pdb.all` | `pdb.all()` | Match every row in the index. | [All](/reference/full-text/all) |
| `pdb.empty` | `pdb.empty()` | Match no rows. Mostly useful for generated SQL. | This page |
| `pdb.exists` | `pdb.exists()` | Match rows where the field exists in the index. | [Filtering](/reference/filtering/overview) |
| `pdb.parse` | `pdb.parse(query_string text, lenient bool default null, conjunction_mode bool default null)` | Parse Lucene/Tantivy-style query syntax. | [Query Parser](/reference/full-text/query-parser) |
| `pdb.parse_with_field` | `pdb.parse_with_field(query_string text, lenient bool default null, conjunction_mode bool default null)` | Parse query syntax relative to the field on the left side of `@@@`. Usually produced by planner rewrites. | [Query Parser](/reference/full-text/query-parser) |
| `pdb.match` | `pdb.match(value text, tokenizer jsonb default null, distance int default null, transposition_cost_one bool default null, prefix bool default null, conjunction_mode bool default null)` | Tokenized match with optional tokenizer, fuzzy, prefix, and conjunction controls. | [Match](/reference/full-text/match) |
| `pdb.match_disjunction` | `pdb.match_disjunction(text)` or `pdb.match_disjunction(text[])` | Match any token. Equivalent to the match-disjunction operator. | [Match Disjunction](/reference/full-text/match#match-disjunction) |
| `pdb.match_conjunction` | `pdb.match_conjunction(text)` or `pdb.match_conjunction(text[])` | Match all tokens. Equivalent to `&&&`. | [Match Conjunction](/reference/full-text/match#match-conjunction) |
| `pdb.fuzzy_term` | `pdb.fuzzy_term(value text default null, distance int default null, transposition_cost_one bool default null, prefix bool default null)` | Build a fuzzy exact-token query. Most users use `=== ...::pdb.fuzzy(...)` instead. | [Fuzzy](/reference/full-text/fuzzy) |
| `pdb.term` | `pdb.term(value)` | Match an exact indexed token or scalar value. | [Term](/reference/full-text/term) |
| `pdb.term_set` | `pdb.term_set(values[])` | Match any exact value from an array. | [Term](/reference/full-text/term#term-set) |
| `pdb.range` | `pdb.range(range)` | Match values inside a Postgres range. | [Filtering](/reference/filtering/overview) |
| `pdb.range_term` | `pdb.range_term(value)` or `pdb.range_term(range, relation)` | Search Postgres range fields by contained term or range relation. | [Range Term](/reference/full-text/range-term) |
| `pdb.phrase` | `pdb.phrase(text)` or `pdb.phrase(text[], slop int default null)` | Match tokens in order and position. Equivalent to `###`. | [Phrase](/reference/full-text/phrase) |
| `pdb.phrase_array` | `pdb.phrase_array(text[])` | Pretokenized phrase match. | [Phrase](/reference/full-text/phrase#using-pretokenized-text) |
| `pdb.phrase_prefix` | `pdb.phrase_prefix(text[], max_expansion int default null)` | Phrase query where the final token is a prefix. | [Phrase Prefix](/reference/full-text/phrase-prefix) |
| `pdb.regex` | `pdb.regex(pattern text)` | Match indexed terms with a regex. | [Regex](/reference/full-text/regex) |
| `pdb.regex_phrase` | `pdb.regex_phrase(regexes text[], slop int default null, max_expansions int default null)` | Match a phrase made of regex terms. | [Regex Phrase](/reference/full-text/regex-phrase) |
| `pdb.more_like_this` | `pdb.more_like_this(key_value anyelement, fields text[] default null, ...)` | Find rows similar to an indexed row. | [More Like This](/reference/full-text/more-like-this) |
| `pdb.more_like_this` | `pdb.more_like_this(document text, ...)` | Find rows similar to a JSON document string. | [More Like This](/reference/full-text/more-like-this#using-a-custom-input-document) |
`pdb.term`, `pdb.term_set`, `pdb.range`, and `pdb.range_term` are overloaded for supported Postgres scalar and range types.
The aggregate form `pdb.term_set(value bigint)` still exists for compatibility, but new queries should use `pdb.term_set(array_agg(...))` instead.
## Query Modifier Casts
Modifier casts attach scoring, fuzzy, or slop settings to another query expression.
| Cast or typmod | Use | Reference |
| ------------------------------------------------------- | -------------------------------------------------------- | -------------------------------------------------------------------------- |
| `::pdb.fuzzy(distance, prefix, transposition_cost_one)` | Allow edit-distance matching for match and term queries. | [Fuzzy](/reference/full-text/fuzzy) |
| `::pdb.boost(factor)` | Multiply a query's contribution to BM25 score. | [Relevance Tuning](/reference/full-text/relevance-tuning) |
| `::pdb.const(score)` | Assign a constant score to a query. | [Constant Scoring](/reference/full-text/relevance-tuning#constant-scoring) |
| `::pdb.slop(distance)` | Allow gaps or transpositions in phrase queries. | [Phrase Slop](/reference/full-text/phrase#adding-slop) |
## Proximity Builders
These functions create proximity clauses for `@@@`.
| Function | Signature | Use | Reference |
| -------------------------- | ------------------------------------------------------------------------------- | ---------------------------------------------------------------- | ----------------------------------------------------------------- |
| `pdb.prox_term` | `pdb.prox_term(term text)` | Create a single proximity term. | [Proximity](/reference/full-text/proximity) |
| `pdb.prox_regex` | `pdb.prox_regex(regex text, max_expansions int default 50)` | Create a regex proximity term. | [Proximity Regex](/reference/full-text/proximity#proximity-regex) |
| `pdb.prox_array` | `pdb.prox_array(variadic clauses)` | Match any of several proximity terms or clauses. | [Proximity Array](/reference/full-text/proximity#proximity-array) |
| `pdb.prox_clause` | `pdb.prox_clause(left, distance int, right)` | Build an unordered proximity clause. | [Proximity](/reference/full-text/proximity) |
| `pdb.prox_clause_in_order` | `pdb.prox_clause_in_order(left, distance int, right)` | Build an ordered proximity clause. | [Proximity](/reference/full-text/proximity) |
| `pdb.proximity` | `pdb.proximity(proximity_clause)` or `pdb.proximity(left, distance int, right)` | Convert a proximity clause into a query builder result. | [Proximity](/reference/full-text/proximity) |
| `pdb.proximity_in_order` | `pdb.proximity_in_order(left, distance int, right)` | Convert an ordered proximity clause into a query builder result. | [Proximity](/reference/full-text/proximity) |
The symbolic `##` and `##>` operators are usually more concise than these function forms.
## Scoring and Highlighting
These functions are placeholders that ParadeDB's custom scan resolves during query execution. Use them in queries that also contain a ParadeDB predicate.
| Function | Signature | Use | Reference |
| ----------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------- | ---------------------------------------------------------------- |
| `pdb.score` | `pdb.score(key_field anyelement) -> float4` | Return the BM25 score for a matched row. | [BM25 Scoring](/reference/full-text/score) |
| `pdb.snippet` | `pdb.snippet(field anyelement, start_tag text default '', end_tag text default '', max_num_chars int default 150, limit int default null, offset int default null) -> text` | Return the best highlighted text snippet. | [Highlighting](/reference/full-text/highlight) |
| `pdb.snippets` | `pdb.snippets(field anyelement, start_tag text default '', end_tag text default '', max_num_chars int default 150, limit int default null, offset int default null, sort_by text default 'score') -> text[]` | Return multiple highlighted snippets. | [Highlighting](/reference/full-text/highlight#multiple-snippets) |
| `pdb.snippet_positions` | `pdb.snippet_positions(field anyelement, limit int default null, offset int default null) -> int[]` | Return byte offsets for highlighted snippets. | [Highlighting](/reference/full-text/highlight#byte-offsets) |
## Aggregates
| Function | Signature | Use | Reference |
| -------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------- | -------------------------------------------------- |
| `pdb.agg` | `pdb.agg(agg jsonb) -> jsonb` | Execute an Elasticsearch-compatible aggregate inside ParadeDB's aggregate scan. | [Aggregate Syntax](/reference/aggregates/overview) |
| `pdb.agg` | `pdb.agg(agg jsonb, solve_mvcc bool) -> jsonb` | Execute the same aggregate with explicit MVCC visibility control. | [Aggregate Syntax](/reference/aggregates/overview) |
| `paradedb.aggregate` | `paradedb.aggregate(index regclass, query searchqueryinput, agg json, solve_mvcc bool default true, memory_limit bigint default 500000000, bucket_limit bigint default null) -> jsonb` | Execute an aggregate directly against an index. | [Aggregate Syntax](/reference/aggregates/overview) |
Supported aggregate JSON shapes are documented under [Aggregates](/reference/aggregates/overview), including [terms](/reference/aggregates/bucket/terms), [range](/reference/aggregates/bucket/range), [histogram](/reference/aggregates/bucket/histogram), [date histogram](/reference/aggregates/bucket/datehistogram), [filters](/reference/aggregates/bucket/filters), [sum](/reference/aggregates/metrics/sum), [average](/reference/aggregates/metrics/average), [min/max](/reference/aggregates/metrics/minmax), [count](/reference/aggregates/metrics/count), [stats](/reference/aggregates/metrics/stats), [percentiles](/reference/aggregates/metrics/percentiles), [cardinality](/reference/aggregates/metrics/cardinality), and [top hits](/reference/aggregates/metrics/tophits).
## Index Configuration and Tokenizers
| Function | Signature | Use | Reference |
| --------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | -------------------------------------------------------------------- | ------------------------------------------------ |
| `paradedb.field` | `paradedb.field(name text, indexed bool default null, stored bool default null, fast bool default null, fieldnorms bool default null, record text default null, expand_dots bool default null, tokenizer jsonb default null, normalizer text default null, alias text default null) -> jsonb` | Build JSON field configuration for index options. | [Create Index](/reference/indexing/create-index) |
| `paradedb.tokenizer` | `paradedb.tokenizer(name text, remove_long int default 255, lowercase bool default true, min_gram int default null, max_gram int default null, prefix_only bool default null, language text default null, pattern text default null, stemmer text default null, stopwords_language text default null, stopwords_languages text[] default null, stopwords text[] default null, ascii_folding bool default null) -> jsonb` | Build JSON tokenizer configuration for index options or `pdb.match`. | [Tokenizers](/reference/tokenizers/overview) |
| `paradedb.tokenize` | `paradedb.tokenize(tokenizer_setting jsonb, input_text text) -> table(token text, position int)` | Preview tokenizer output for a tokenizer configuration. | [Tokenizers](/reference/tokenizers/overview) |
| `paradedb.tokenizers` | `paradedb.tokenizers() -> table(tokenizer text)` | List tokenizer names available to `paradedb.tokenizer`. | [Tokenizers](/reference/tokenizers/overview) |
The `fast` parameter on `paradedb.field` is a compatibility name for columnar
storage. New index definitions should use tokenizer casts with the `columnar`
option where text or JSON fields need columnar storage.
ParadeDB also generates cast-support helpers such as `pdb.tokenize_simple`, `pdb.tokenize_icu`, `pdb.tokenize_ngram`, and `pdb.tokenize_edge_ngram`. Prefer tokenizer casts or `paradedb.tokenize(...)`; the generated helper names are part of the cast implementation.
## Index Diagnostics and Maintenance
| Function | Signature | Use | Reference |
| ------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------ |
| `paradedb.schema` | `paradedb.schema(index regclass) -> table(...)` | Inspect the fields, field types, and tokenizer settings stored in an index schema. | [Create Index](/reference/indexing/create-index) |
| `paradedb.index_fields` | `paradedb.index_fields(index regclass) -> jsonb` | Return the stored field configuration for an index. | [Create Index](/reference/indexing/create-index) |
| `pdb.verify_index` | `pdb.verify_index(index regclass, heapallindexed bool default false, sample_rate float8 default null, report_progress bool default false, verbose bool default false, on_error_stop bool default false, segment_ids int[] default null)` | Verify one ParadeDB index. | [Verify Index Integrity](/operate/index-maintenance/verify-index) |
| `pdb.verify_all_indexes` | `pdb.verify_all_indexes(schema_pattern text default null, index_pattern text default null, heapallindexed bool default false, sample_rate float8 default null, report_progress bool default false, on_error_stop bool default false)` | Verify all ParadeDB indexes in a database, optionally filtered by pattern. | [Verify Index Integrity](/operate/index-maintenance/verify-index) |
| `pdb.index_segments` | `pdb.index_segments(index regclass)` | List segment-level metadata for one index. | [Verify Index Integrity](/operate/index-maintenance/verify-index) |
| `pdb.indexes` | `pdb.indexes()` | List ParadeDB indexes in the current database. | [Verify Index Integrity](/operate/index-maintenance/verify-index) |
| `paradedb.index_info` | `paradedb.index_info(index regclass, show_invisible bool default false) -> table(...)` | Inspect per-segment visibility, size, and Tantivy component statistics. | [Verify Index Integrity](/operate/index-maintenance/verify-index) |
| `paradedb.vector_info` | `paradedb.vector_info(index regclass, field text) -> table(...)` | Inspect per-segment vector storage statistics for one vector field. | [Tuning Recall and Latency](/reference/vector/tuning) |
| `paradedb.vector_config` | `paradedb.vector_config(index regclass, field text) -> table(index_oid oid, quantized boolean, layers integer[], bytes_per_row integer, settings_version integer)` | Inspect the quantization build target for each leaf index. | [Tuning Recall and Latency](/reference/vector/tuning) |
| `paradedb.vector_clusters` | `paradedb.vector_clusters(index regclass, field text) -> table(...)` | Inspect IVF cluster sizes and radii for one vector field. | [Tuning Recall and Latency](/reference/vector/tuning) |
| `paradedb.merge_info` | `paradedb.merge_info(index regclass) -> table(...)` | Inspect active or pending segment merge entries. | [Index Maintenance](/operate/index-maintenance/reindexing) |
| `paradedb.vacuum_info` | `paradedb.vacuum_info(index regclass) -> table(...)` | Inspect segments queued for vacuum cleanup. | [Index Maintenance](/operate/index-maintenance/reindexing) |
| `paradedb.layer_sizes` | `paradedb.layer_sizes(index regclass) -> numeric[]` | Return foreground merge layer thresholds for an index. | [Index Maintenance](/operate/index-maintenance/reindexing) |
| `paradedb.background_layer_sizes` | `paradedb.background_layer_sizes(index regclass) -> numeric[]` | Return background merge layer thresholds for an index. | [Index Maintenance](/operate/index-maintenance/reindexing) |
| `paradedb.combined_layer_sizes` | `paradedb.combined_layer_sizes(index regclass) -> numeric[]` | Return combined foreground and background merge layer thresholds. | [Index Maintenance](/operate/index-maintenance/reindexing) |
| `paradedb.storage_info` | `paradedb.storage_info(index regclass) -> table(block bigint, max_offset int)` | Inspect low-level metadata storage pages. | [Verify Index Integrity](/operate/index-maintenance/verify-index) |
| `paradedb.page_info` | `paradedb.page_info(index regclass, blockno bigint) -> table(...)` | Inspect one low-level metadata storage page. | [Verify Index Integrity](/operate/index-maintenance/verify-index) |
| `paradedb.find_ctid` | `paradedb.find_ctid(index regclass, ctid tid) -> text[]` | Find segment IDs containing one heap tuple identifier. | [Verify Index Integrity](/operate/index-maintenance/verify-index) |
| `paradedb.index_created_at` | `paradedb.index_created_at(index regclass) -> timestamptz` | Return when an index was built, or `NULL` for older indexes. | [Verify Index Integrity](/operate/index-maintenance/verify-index#inspecting-index-creation-time) |
| `paradedb.index_created_by` | `paradedb.index_created_by(index regclass) -> text` | Return the pg_search version that built the index, or `NULL` for older indexes. | [Verify Index Integrity](/operate/index-maintenance/verify-index#inspecting-index-creation-time) |
| `paradedb.version_info` | `paradedb.version_info() -> table(version text, build_mode text)` | Show the installed pg_search version and build mode. | This page |
| `paradedb.merge_lock_garbage_collect` | `paradedb.merge_lock_garbage_collect(index regclass) -> setof int` | Clear stale merge-lock entries and return affected process IDs. | [Index Maintenance](/operate/index-maintenance/reindexing) |
Related views:
| View | Use |
| --------------------------- | ------------------------------------------------------------------------ |
| `pdb.index_layer_info` | Show index segments grouped into foreground and background merge layers. |
| `paradedb.index_layer_info` | Deprecated compatibility view; use `pdb.index_layer_info`. |
## Lower-Level SearchQueryInput Builders
The `paradedb` schema exposes lower-level builders that return `searchqueryinput` directly. They are useful for compatibility and generated SQL, but new hand-written queries should usually prefer `pdb.*` query builders plus `@@@`.
| Function | Signature | Use |
| -------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | --------------------------------------------------------------------------------------- |
| `paradedb.all` | `paradedb.all()` | Lower-level match-all query. |
| `paradedb.empty` | `paradedb.empty()` | Lower-level match-none query. |
| `paradedb.parse` | `paradedb.parse(query_string text, lenient bool default null, conjunction_mode bool default null)` | Lower-level parser query. |
| `paradedb.boolean` | `paradedb.boolean(must searchqueryinput default null, should searchqueryinput default null, must_not searchqueryinput default null, minimum_should_match bigint default null)` | Compose one Boolean query from optional clauses. |
| `paradedb.boolean` | `paradedb.boolean(must searchqueryinput[] default array[]::searchqueryinput[], should searchqueryinput[] default array[]::searchqueryinput[], must_not searchqueryinput[] default array[]::searchqueryinput[], minimum_should_match bigint default null)` | Compose one Boolean query from clause arrays. |
| `paradedb.boost` | `paradedb.boost(factor float4, query searchqueryinput)` | Lower-level score boost wrapper. |
| `paradedb.const_score` | `paradedb.const_score(score float4, query searchqueryinput)` | Lower-level constant score wrapper. |
| `paradedb.disjunction_max` | `paradedb.disjunction_max(disjuncts searchqueryinput[], tie_breaker float4 default null)` | Prefer the best scoring subquery, with optional tie breaking. |
| `paradedb.term` | `paradedb.term(field fieldname, value supported_scalar)` | Lower-level fielded exact-value query. |
| `paradedb.term_set` | `paradedb.term_set(terms searchqueryinput[])` | Lower-level set query composed from fielded term queries. |
| `paradedb.to_search_query_input` | `paradedb.to_search_query_input(field fieldname, query pdb.query)` | Convert a `pdb.query` into a fielded `searchqueryinput`. |
| `paradedb.with_index` | `paradedb.with_index(index regclass, query searchqueryinput)` | Attach a specific index to a `searchqueryinput` for generated or sequential-scan plans. |
| `paradedb.term_with_operator` | `paradedb.term_with_operator(field fieldname, operator text, value anyelement)` | Build a fielded comparison query for planner rewrites. |
| `paradedb.terms_with_operator` | `paradedb.terms_with_operator(field fieldname, operator text, value anyelement, conjunction_mode bool)` | Build array comparison queries for planner rewrites. |
Most `pdb.*` query builders that target a single field also have a generated `paradedb.*(field fieldname, ...) -> searchqueryinput` counterpart. That includes `parse_with_field`, `match`, `match_disjunction`, `match_conjunction`, `exists`, `fuzzy_term`, `term`, `term_set`, `range`, `range_term`, `phrase`, `phrase_array`, `phrase_prefix`, `regex`, `regex_phrase`, `proximity`, and `proximity_in_order`.
## Tokenizer Casts
Tokenizer configuration is usually expressed through casts when creating an index or issuing tokenizer-specific queries.
| Cast or typmod | Use | Reference |
| -------------------------- | --------------------------------------------------------------------------- | ----------------------------------------------------------------------------------- |
| `::pdb.simple(...)` | Default configurable tokenizer with filters such as stemming and stopwords. | [Simple](/reference/tokenizers/available-tokenizers/simple) |
| `::pdb.icu(...)` | ICU tokenizer for multilingual text. | [ICU](/reference/tokenizers/available-tokenizers/icu) |
| `::pdb.ngram(...)` | N-gram tokenization. | [Ngrams](/reference/tokenizers/available-tokenizers/ngrams) |
| `::pdb.edge_ngram(...)` | Prefix-oriented n-gram tokenization. | [Edge Ngrams](/reference/tokenizers/available-tokenizers/edge-ngrams) |
| `::pdb.whitespace` | Whitespace tokenization. | [Whitespace](/reference/tokenizers/available-tokenizers/whitespace) |
| `::pdb.literal` | Treat the whole text value as one token. | [Literal](/reference/tokenizers/available-tokenizers/literal) |
| `::pdb.literal_normalized` | Literal tokenization with normalization. | [Literal Normalized](/reference/tokenizers/available-tokenizers/literal-normalized) |
| `::pdb.regex_pattern(...)` | Regex-based tokenization. | [Regex Tokenizer](/reference/tokenizers/available-tokenizers/regex) |
| `::pdb.source_code` | Source-code tokenization. | [Source Code](/reference/tokenizers/available-tokenizers/source-code) |
| `::pdb.unicode_words` | Unicode word tokenization. | [Unicode](/reference/tokenizers/available-tokenizers/unicode) |
| `::pdb.jieba(...)` | Chinese tokenization with Jieba. | [Jieba](/reference/tokenizers/available-tokenizers/jieba) |
| `::pdb.lindera(...)` | Japanese/Korean tokenization with Lindera. | [Lindera](/reference/tokenizers/available-tokenizers/lindera) |
| `::pdb.chinese_compatible` | Chinese-compatible tokenizer. | [Chinese Compatible](/reference/tokenizers/available-tokenizers/chinese-compatible) |
| `::pdb.alias(...)` | Assign an indexed expression a queryable field name. | [Indexing Expressions](/reference/indexing/indexing-expressions) |
## Compatibility and Internal Functions
Older `paradedb.*` scoring and highlighting names remain for backwards compatibility. Prefer `pdb.score`, `pdb.snippet`, `pdb.snippets`, and `pdb.snippet_positions`.
| Deprecated name | Replacement |
| ---------------------------- | ----------------------- |
| `paradedb.score` | `pdb.score` |
| `paradedb.snippet` | `pdb.snippet` |
| `paradedb.snippets` | `pdb.snippets` |
| `paradedb.snippet_positions` | `pdb.snippet_positions` |
| `paradedb.is_merging` | `paradedb.merge_info` |
| `paradedb.validate_checksum` | `pdb.verify_index` |
| `paradedb.force_merge` | `VACUUM` |
Generated type I/O functions, cast helpers, and planner support functions are implementation details. They may appear in `\df` output, but they are not part of the recommended SQL surface.