String search and full-text functions ​

Search functions test patterns, find regular-expression matches, compare similar text and construct full-text search values. LIKE/GLOB patterns, regular expressions, trigrams and tsquery expressions are different query languages.

Use literal search when the text is literal. For full-text matching, build a tsvector and a tsquery and combine them with the SQL @@ operator. Each family below documents its syntax, configuration and NULL behavior.

Example: Testing an identifier pattern ​

SELECT like('invoice-%', 'invoice-123'),
       regexp_like('invoice-123', '^invoice-[0-9]+$')
FORMAT csv;

1,true

like and glob ​

like(pattern,text[,escape]) and glob(pattern,text) expose the pattern operators as function calls. The pattern comes first. LIKE folds ASCII case and accepts one optional escape character; GLOB is case-sensitive and uses shell-style wildcards. NULL arguments propagate.

SELECT like('he%','hello'),glob('h*','hello') FORMAT csv;

1,1

array_to_tsvector ​

Create a vector from literal array lexemes.

Syntax ​

array_to_tsvector(lexemes)

Arguments ​

One-dimensional native string Array. Reject NULL or empty members; deduplicate and sort without normalization. NULL input propagates.

Returned value ​

tsvector

Example ​

SELECT CAST(array_to_tsvector(array('rat','cat','rat')) AS TEXT) FORMAT csv;

"'cat' 'rat'"

json_to_tsvector ​

Build a search vector from selected JSON components.

Syntax ​

json_to_tsvector([configuration,]document,filter)

Arguments ​

Same filters/configurations as jsonb_to_tsvector; original object order and duplicate keys are retained. NULL propagates.

Returned value ​

tsvector

Example ​

SELECT CAST(json_to_tsvector('simple','{"title":"fat cats"}','"string"') AS TEXT) FORMAT csv;

"'cats':2 'fat':1"

jsonb_to_tsvector ​

Build a search vector from selected JSONB components.

Syntax ​

jsonb_to_tsvector([configuration,]document,filter)

Arguments ​

Configurations simple/english/russian, default english. Filter is a JSON string or string array: string,numeric,boolean,key,all. Canonical JSONB key order and one gap between selected components. Native search limits apply; NULL propagates.

Returned value ​

tsvector

Example ​

SELECT CAST(jsonb_to_tsvector('simple','{"title":"fat cats"}','"string"') AS TEXT) FORMAT csv;

"'cats':2 'fat':1"

levenshtein ​

Compute Unicode edit distance.

Syntax ​

levenshtein(source,target); optional insertion/deletion/substitution costs

Arguments ​

At most 255 characters per input. Optional nonnegative 32-bit insertion/deletion/substitution costs. SQL NULL propagates.

Returned value ​

INTEGER edit distance.

Example ​

SELECT levenshtein('GUMBO','GAMBOL') FORMAT csv;

2

levenshtein_less_equal ​

Compute Unicode edit distance up to a threshold.

Syntax ​

levenshtein_less_equal(source,target,max_distance); optional insertion/deletion/substitution costs

Arguments ​

At most 255 characters per input. Optional nonnegative 32-bit insertion/deletion/substitution costs. Threshold is last; negative threshold requests the full distance. Above threshold any larger value is valid. SQL NULL propagates.

Returned value ​

INTEGER edit distance or a value greater than the threshold.

Example ​

SELECT levenshtein_less_equal('GUMBO','GAMBOL',2) FORMAT csv;

2

numnode ​

Count lexemes and operators in a query.

Syntax ​

numnode(query)

Arguments ​

Native tsquery or raw query text. Empty query yields zero; NULL propagates.

Returned value ​

INTEGER

Example ​

SELECT numnode(to_tsquery('simple','cat & dog')) FORMAT csv;

3

phraseto_tsquery ​

Construct a phrase query retaining gaps from stop words.

Syntax ​

phraseto_tsquery([configuration,]text)

Arguments ​

Configurations simple/english/russian; default english. Unicode word matching supports compound-word positions, language-specific word stems and stop words. Queries are bounded to 128 terms/levels; lexemes to 2046 bytes. Only the listed language configurations are supported. SQL NULL propagates.

Returned value ​

Native tsquery usable in tables and subqueries.

Example ​

SELECT CAST(phraseto_tsquery('english','The fat rats') AS TEXT) FORMAT csv;

"'fat' <-> 'rat'"

plainto_tsquery ​

Construct an AND query from ordinary text.

Syntax ​

plainto_tsquery([configuration,]text)

Arguments ​

Configurations simple/english/russian; default english. Unicode word matching supports compound-word positions, language-specific word stems and stop words. Queries are bounded to 128 terms/levels; lexemes to 2046 bytes. Only the listed language configurations are supported. SQL NULL propagates.

Returned value ​

Native tsquery usable in tables and subqueries.

Example ​

SELECT CAST(plainto_tsquery('english','The fat rats') AS TEXT) FORMAT csv;

"'fat' & 'rat'"

querytree ​

Render the indexable positive part of a query.

Syntax ​

querytree(query)

Arguments ​

Ignore negative restrictions. Empty query yields empty text; unrestricted alternatives yield T. This helper does not create an index. NULL propagates.

Returned value ​

String

Example ​

SELECT querytree(to_tsquery('simple','cat & !dog')) FORMAT csv;

"'cat'"

regexp_count ​

Count nonoverlapping Unicode regex matches starting at a one-based character position.

Syntax ​

regexp_count(text, pattern[, start[, flags]])

Arguments ​

Start defaults to 1 and must be positive; flags follow existing regex rules; empty matches advance one UTF-8 character.

Returned value ​

INTEGER count, zero without matches, or NULL for any NULL argument.

Example ​

SELECT regexp_count('123456789012','[0-9]{3}',2) FORMAT csv;

3

regexp_instr ​

Return the one-based character boundary of a selected regex match or capture.

Syntax ​

regexp_instr(text,pattern[,start[,occurrence[,endoption[,flags[,subexpr]]]]])

Arguments ​

Start/occurrence positive; endoption 0 selects the start, 1 the boundary after the end; subexpr nonnegative; existing bounded regex flags.

Returned value ​

INTEGER position; 0 for no matching capture; NULL propagates.

Example ​

SELECT regexp_instr('ABCDEF','c(.)(..)',1,1,0,'i') FORMAT csv;

3

regexp_like ​

Test whether a Unicode regex matches anywhere in the input.

Syntax ​

regexp_like(text, pattern[, flags])

Arguments ​

Text, Unicode regular expression, optional supported flags; g is rejected.

Returned value ​

Native Bool, or NULL for any NULL argument.

Example ​

SELECT regexp_like('Hello World','world$','i') FORMAT csv;

true

regexp_match ​

Return the first regex match or its capturing groups.

Syntax ​

regexp_match(value, pattern[, flags])

Arguments ​

Flags support i,c,s,n,m,p,w,x,t,q,e; active q and x modes are incompatible. Uses Unicode regex syntax; look-around, pattern backreferences and POSIX longest-match behavior are not supported.

Returned value ​

Native array of nullable TEXT capturing groups, or the whole match when there are no groups; NULL for no match or NULL input.

Example ​

SELECT array_element(regexp_match('abc123','([a-z]+)([0-9]+)'),2) FORMAT csv;

"123"

regexp_matches ​

Return regex match groups as native arrays in separate FROM rows.

Syntax ​

FROM regexp_matches(text,pattern[,flags])

Arguments ​

Existing regex syntax and flags; g emits all matches, otherwise the first; NULL input gives no rows.

Returned value ​

One regexp_matches column containing Array(Nullable(String)); no-match input gives no rows.

Example ​

SELECT array_to_string(regexp_matches,',') FROM regexp_matches('a1 b2','([a-z])([0-9])','g') FORMAT csv;

"a,1"
"b,2"

regexp_substr ​

Return the requested regex occurrence or capture, preserving anchors against the original string.

Syntax ​

regexp_substr(text, pattern[, start[, occurrence[, flags[, subexpr]]]])

Arguments ​

Positive one-based start/occurrence default to 1; nonnegative subexpr defaults to whole match 0.

Returned value ​

TEXT, or NULL for no match, unmatched/missing capture, or any NULL argument.

Example ​

SELECT regexp_substr('ABCDEFGHI','(c..)(...)',1,1,'i',2) FORMAT csv;

"FGH"

setweight ​

Assign positional weights to all or selected lexemes.

Syntax ​

setweight(vector,weight[,lexemes])

Arguments ​

Weight A/B/C/D; optional native string Array selects literal lexemes. NULL propagates.

Returned value ​

tsvector

Example ​

SELECT CAST(setweight(to_tsvector('simple','cat dog'),'A') AS TEXT) FORMAT csv;

"'cat':1A 'dog':2A"

show_trgm ​

List sorted unique compact trigrams.

Syntax ​

show_trgm(text)

Arguments ​

Same Unicode word/padding/lowercase rules as similarity. Multibyte compact keys use PostgreSQL legacy CRC and little-endian byte extraction. NUL errors. SQL NULL propagates.

Returned value ​

Array(String): printable ASCII keys or hexadecimal compact keys.

Example ​

SELECT array_to_string(show_trgm('cat'),'|') FORMAT csv;

"  c| ca|at |cat"

similarity ​

Compare unique trigram sets.

Syntax ​

similarity(pattern,text)

Arguments ​

Unicode alphanumeric words, locale-independent Unicode lowercase, two leading/one trailing spaces, pg_trgm compact CRC keys and Jaccard scores. SQL NULL propagates. NUL errors.

Returned value ​

Native Float32 score in [0,1]; zero for empty trigram sets.

Example ​

SELECT similarity('word','two words') FORMAT csv;

0.36363637

strict_word_similarity ​

Maximize similarity over extents aligned to whole-word boundaries.

Syntax ​

strict_word_similarity(pattern,text)

Arguments ​

Unicode alphanumeric words, locale-independent Unicode lowercase, two leading/one trailing spaces, pg_trgm compact CRC keys and Jaccard scores. SQL NULL propagates. NUL errors.

Returned value ​

Native Float32 score in [0,1]; zero for empty trigram sets.

Example ​

SELECT strict_word_similarity('word','two words') FORMAT csv;

0.5714286

strip ​

Remove positions and weights from a vector.

Syntax ​

strip(vector)

Arguments ​

Native tsvector or vector input text. Retain lexemes; NULL propagates.

Returned value ​

tsvector

Example ​

SELECT CAST(strip(to_tsvector('simple','cat dog')) AS TEXT) FORMAT csv;

"'cat' 'dog'"

to_tsquery ​

Parse Boolean, prefix, weight and phrase query syntax.

Syntax ​

to_tsquery([configuration,]text)

Arguments ​

Configurations simple/english/russian; default english. Unicode word matching supports compound-word positions, language-specific word stems and stop words. Queries are bounded to 128 terms/levels; lexemes to 2046 bytes. Only the listed language configurations are supported. SQL NULL propagates.

Returned value ​

Native tsquery usable in tables and subqueries.

Example ​

SELECT CAST(to_tsquery('english','fat & rats') AS TEXT) FORMAT csv;

"'fat' & 'rat'"

to_tsvector ​

Normalize document words and retain lexeme positions.

Syntax ​

to_tsvector([configuration,]text)

Arguments ​

Configurations simple/english/russian; default english. Unicode word matching supports compound-word positions, language-specific word stems and stop words. Queries are bounded to 128 terms/levels; lexemes to 2046 bytes. Only the listed language configurations are supported. SQL NULL propagates.

Returned value ​

Native tsvector usable in tables and subqueries.

Example ​

SELECT CAST(to_tsvector('english','The fat rats') AS TEXT) FORMAT csv;

"'fat':2 'rat':3"

ts_delete ​

Remove literal lexemes from a vector.

Syntax ​

ts_delete(vector,lexeme_or_array)

Arguments ​

One string or native string Array; ignore absent and NULL array members. NULL input propagates.

Returned value ​

tsvector

Example ​

SELECT CAST(ts_delete(to_tsvector('simple','cat dog'),'dog') AS TEXT) FORMAT csv;

"'cat':1"

ts_filter ​

Keep selected positional weights.

Syntax ​

ts_filter(vector,weights)

Arguments ​

Native string Array of A/B/C/D weights. Reject invalid or NULL members. NULL input propagates.

Returned value ​

tsvector

Example ​

SELECT CAST(ts_filter(setweight(to_tsvector('simple','cat'),'A'),array('A')) AS TEXT) FORMAT csv;

"'cat':1A"

ts_headline ​

Highlight query lexemes in bounded document fragments.

Syntax ​

ts_headline([configuration,]document,query[,options])

Arguments ​

Text documents; configurations simple/english/russian. Options StartSel/StopSel/MinWords/MaxWords/MaxFragments/FragmentDelimiter/HighlightAll. Native deterministic word-window selection can differ from PostgreSQL. Default HTML-like tags are removed; HighlightAll retains source markup. Returned text is not HTML-escaped. SQL NULL propagates.

Returned value ​

TEXT with selected lexemes marked.

Example ​

SELECT ts_headline('english','raining cats and dogs',to_tsquery('english','cat')) FORMAT csv;

"raining <b>cats</b> and dogs"

ts_rank ​

Rank matching lexemes by frequency/proximity.

Syntax ​

ts_rank([weights,]vector,query[,normalization])

Arguments ​

Native/search-text tsvector and tsquery. Optional Array of at least four non-NULL weights, ordered D/C/B/A; defaults 0.1/0.2/0.4/1. Negative weights select defaults, values above one error. Normalization mask 0..63. Stripped vector entries contribute to frequency but not cover density. SQL NULL propagates.

Returned value ​

Native Float32 relevance score.

Example ​

SELECT ts_rank(to_tsvector('simple','cat'),to_tsquery('simple','cat')) FORMAT csv;

0.06079271

ts_rank_cd ​

Rank matching lexemes by minimal cover density.

Syntax ​

ts_rank_cd([weights,]vector,query[,normalization])

Arguments ​

Native/search-text tsvector and tsquery. Optional Array of at least four non-NULL weights, ordered D/C/B/A; defaults 0.1/0.2/0.4/1. Negative weights select defaults, values above one error. Normalization mask 0..63. Stripped vector entries contribute to frequency but not cover density. SQL NULL propagates.

Returned value ​

Native Float32 relevance score.

Example ​

SELECT ts_rank_cd(to_tsvector('simple','cat'),to_tsquery('simple','cat')) FORMAT csv;

0.1

tsquery_phrase ​

Join two queries at an exact positional distance.

Syntax ​

tsquery_phrase(left,right[,distance])

Arguments ​

Native tsquery or raw query text; distance 0..16384, default 1. Native depth/term limits apply. NULL propagates.

Returned value ​

tsquery

Example ​

SELECT CAST(tsquery_phrase(to_tsquery('simple','cat'),to_tsquery('simple','dog'),2) AS TEXT) FORMAT csv;

"'cat' <2> 'dog'"

tsvector_to_array ​

Extract sorted unique search lexemes.

Syntax ​

tsvector_to_array(vector)

Arguments ​

Native tsvector or vector input text. Positions and weights are omitted. SQL NULL propagates.

Returned value ​

Array(String) lexemes.

Example ​

SELECT array_to_string(tsvector_to_array(to_tsvector('simple','rat cat rat')),',') FORMAT csv;

"cat,rat"

websearch_to_tsquery ​

Parse quoted phrases, OR and leading-minus query terms.

Syntax ​

websearch_to_tsquery([configuration,]text)

Arguments ​

Configurations simple/english/russian; default english. Unicode word matching supports compound-word positions, language-specific word stems and stop words. Queries are bounded to 128 terms/levels; lexemes to 2046 bytes. Only the listed language configurations are supported. SQL NULL propagates.

Returned value ​

Native tsquery usable in tables and subqueries.

Example ​

SELECT CAST(websearch_to_tsquery('english','The fat rats') AS TEXT) FORMAT csv;

"'fat' & 'rat'"

word_similarity ​

Maximize similarity to a contiguous extent of text trigrams.

Syntax ​

word_similarity(pattern,text)

Arguments ​

Unicode alphanumeric words, locale-independent Unicode lowercase, two leading/one trailing spaces, pg_trgm compact CRC keys and Jaccard scores. SQL NULL propagates. NUL errors.

Returned value ​

Native Float32 score in [0,1]; zero for empty trigram sets.

Example ​

SELECT word_similarity('word','two words') FORMAT csv;

0.8