SQL Function Reference

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
`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
`pdb.parse`	`pdb.parse(query_string text, lenient bool default null, conjunction_mode bool default null)`	Parse Lucene/Tantivy-style query syntax.	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
`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
`pdb.match_disjunction`	`pdb.match_disjunction(text)` or `pdb.match_disjunction(text[])`	Match any token. Equivalent to the match-disjunction operator.	Match Disjunction
`pdb.match_conjunction`	`pdb.match_conjunction(text)` or `pdb.match_conjunction(text[])`	Match all tokens. Equivalent to `&&&`.	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
`pdb.term`	`pdb.term(value)`	Match an exact indexed token or scalar value.	Term
`pdb.term_set`	`pdb.term_set(values[])`	Match any exact value from an array.	Term
`pdb.range`	`pdb.range(range)`	Match values inside a Postgres range.	Filtering
`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
`pdb.phrase`	`pdb.phrase(text)` or `pdb.phrase(text[], slop int default null)`	Match tokens in order and position. Equivalent to `###`.	Phrase
`pdb.phrase_array`	`pdb.phrase_array(text[])`	Pretokenized phrase match.	Phrase
`pdb.phrase_prefix`	`pdb.phrase_prefix(text[], max_expansion int default null)`	Phrase query where the final token is a prefix.	Phrase Prefix
`pdb.regex`	`pdb.regex(pattern text)`	Match indexed terms with a regex.	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
`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
`pdb.more_like_this`	`pdb.more_like_this(document text, ...)`	Find rows similar to a JSON document string.	More Like This

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
`::pdb.boost(factor)`	Multiply a query’s contribution to BM25 score.	Relevance Tuning
`::pdb.const(score)`	Assign a constant score to a query.	Constant Scoring
`::pdb.slop(distance)`	Allow gaps or transpositions in phrase queries.	Phrase 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
`pdb.prox_regex`	`pdb.prox_regex(regex text, max_expansions int default 50)`	Create a regex proximity term.	Proximity Regex
`pdb.prox_array`	`pdb.prox_array(variadic clauses)`	Match any of several proximity terms or clauses.	Proximity Array
`pdb.prox_clause`	`pdb.prox_clause(left, distance int, right)`	Build an unordered proximity clause.	Proximity
`pdb.prox_clause_in_order`	`pdb.prox_clause_in_order(left, distance int, right)`	Build an ordered proximity clause.	Proximity
`pdb.proximity`	`pdb.proximity(proximity_clause)` or `pdb.proximity(left, distance int, right)`	Convert a proximity clause into a query builder result.	Proximity
`pdb.proximity_in_order`	`pdb.proximity_in_order(left, distance int, right)`	Convert an ordered proximity clause into a query builder result.	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
`pdb.snippet`	`pdb.snippet(field anyelement, start_tag text default '<b>', end_tag text default '</b>', max_num_chars int default 150, limit int default null, offset int default null) -> text`	Return the best highlighted text snippet.	Highlighting
`pdb.snippets`	`pdb.snippets(field anyelement, start_tag text default '<b>', end_tag text default '</b>', 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
`pdb.snippet_positions`	`pdb.snippet_positions(field anyelement, limit int default null, offset int default null) -> int[]`	Return byte offsets for highlighted snippets.	Highlighting

Aggregates

Function	Signature	Use	Reference
`pdb.agg`	`pdb.agg(agg jsonb) -> jsonb`	Execute an Elasticsearch-compatible aggregate inside ParadeDB’s aggregate scan.	Aggregate Syntax
`pdb.agg`	`pdb.agg(agg jsonb, solve_mvcc bool) -> jsonb`	Execute the same aggregate with explicit MVCC visibility control.	Aggregate Syntax
`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

Supported aggregate JSON shapes are documented under Aggregates, including terms, range, histogram, date histogram, filters, sum, average, min/max, count, stats, percentiles, cardinality, and top hits.

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
`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
`paradedb.tokenize`	`paradedb.tokenize(tokenizer_setting jsonb, input_text text) -> table(token text, position int)`	Preview tokenizer output for a tokenizer configuration.	Tokenizers
`paradedb.tokenizers`	`paradedb.tokenizers() -> table(tokenizer text)`	List tokenizer names available to `paradedb.tokenizer`.	Tokenizers

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
`paradedb.index_fields`	`paradedb.index_fields(index regclass) -> jsonb`	Return the stored field configuration for an index.	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
`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
`pdb.index_segments`	`pdb.index_segments(index regclass)`	List segment-level metadata for one index.	Verify Index Integrity
`pdb.indexes`	`pdb.indexes()`	List ParadeDB indexes in the current database.	Verify Index Integrity
`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
`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
`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
`paradedb.merge_info`	`paradedb.merge_info(index regclass) -> table(...)`	Inspect active or pending segment merge entries.	Index Maintenance
`paradedb.vacuum_info`	`paradedb.vacuum_info(index regclass) -> table(...)`	Inspect segments queued for vacuum cleanup.	Index Maintenance
`paradedb.layer_sizes`	`paradedb.layer_sizes(index regclass) -> numeric[]`	Return foreground merge layer thresholds for an index.	Index Maintenance
`paradedb.background_layer_sizes`	`paradedb.background_layer_sizes(index regclass) -> numeric[]`	Return background merge layer thresholds for an index.	Index Maintenance
`paradedb.combined_layer_sizes`	`paradedb.combined_layer_sizes(index regclass) -> numeric[]`	Return combined foreground and background merge layer thresholds.	Index Maintenance
`paradedb.storage_info`	`paradedb.storage_info(index regclass) -> table(block bigint, max_offset int)`	Inspect low-level metadata storage pages.	Verify Index Integrity
`paradedb.page_info`	`paradedb.page_info(index regclass, blockno bigint) -> table(...)`	Inspect one low-level metadata storage page.	Verify Index Integrity
`paradedb.find_ctid`	`paradedb.find_ctid(index regclass, ctid tid) -> text[]`	Find segment IDs containing one heap tuple identifier.	Verify Index Integrity
`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
`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
`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

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
`::pdb.icu(...)`	ICU tokenizer for multilingual text.	ICU
`::pdb.ngram(...)`	N-gram tokenization.	Ngrams
`::pdb.edge_ngram(...)`	Prefix-oriented n-gram tokenization.	Edge Ngrams
`::pdb.whitespace`	Whitespace tokenization.	Whitespace
`::pdb.literal`	Treat the whole text value as one token.	Literal
`::pdb.literal_normalized`	Literal tokenization with normalization.	Literal Normalized
`::pdb.regex_pattern(...)`	Regex-based tokenization.	Regex Tokenizer
`::pdb.source_code`	Source-code tokenization.	Source Code
`::pdb.unicode_words`	Unicode word tokenization.	Unicode
`::pdb.jieba(...)`	Chinese tokenization with Jieba.	Jieba
`::pdb.lindera(...)`	Japanese/Korean tokenization with Lindera.	Lindera
`::pdb.chinese_compatible`	Chinese-compatible tokenizer.	Chinese Compatible
`::pdb.alias(...)`	Assign an indexed expression a queryable field name.	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.

Indexing

Full-Text / Lexical Search

Vector / Semantic Search

Hybrid Search

Aggregations

JOINs

Cheatsheets

SQL Function Reference

Query Builders

Query Modifier Casts

Proximity Builders

Scoring and Highlighting

Aggregates

Index Configuration and Tokenizers

Index Diagnostics and Maintenance

Lower-Level SearchQueryInput Builders

Tokenizer Casts

Compatibility and Internal Functions

​Query Builders

​Query Modifier Casts

​Proximity Builders

​Scoring and Highlighting

​Aggregates

​Index Configuration and Tokenizers

​Index Diagnostics and Maintenance

​Lower-Level SearchQueryInput Builders

​Tokenizer Casts

​Compatibility and Internal Functions

Query Builders

Query Modifier Casts

Proximity Builders

Scoring and Highlighting

Aggregates

Index Configuration and Tokenizers

Index Diagnostics and Maintenance

Lower-Level SearchQueryInput Builders

Tokenizer Casts

Compatibility and Internal Functions