Skip to main content

Edit this page

Wildcard Search

Search for partial matches using pattern wildcards. Useful when you need prefix, infix, suffix, or complex pattern matching on indexed terms.

See Setup for the shared dataset used in all examples.

Wildcard characters​

CharacterMeaning
_Match any single character
%Match zero or more characters
\_Literal underscore
\%Literal percent sign

Prefix matching​

Find genres starting with 'sci':

Query
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_like('sci%')ORDER BY id;
Result
 id | title                         | genre----+-------------------------------+--------  1 | The Matrix                    | sci-fi  2 | The Matrix Reloaded           | sci-fi  3 | The Matrix Revolutions        | sci-fi  7 | Star Trek: The Motion Picture | sci-fi  8 | Alien                         | sci-fi

Suffix matching​

Find genres ending with 'dy':

Query
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_like('%dy')ORDER BY id;
Result
 id | title                    | genre----+--------------------------+--------  6 | Scary Movie              | comedy 10 | The Grand Budapest Hotel | comedy

Infix matching​

Find genres containing 'vent':

Query
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_like('%vent%')ORDER BY id;
Result
 id | title         | genre----+---------------+-----------  4 | Jurassic Park | adventure

Single character wildcard​

Match genres with exactly one character between 'co' and 'edy':

Query
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_like('co_edy')ORDER BY id;
Result
 id | title                    | genre----+--------------------------+--------  6 | Scary Movie              | comedy 10 | The Grand Budapest Hotel | comedy

Complex patterns​

Combine wildcards for more specific matching. Find words matching 'h_____' (h + exactly 5 characters):

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_like('h_____')ORDER BY id;
Result
 id | title----+--------------  1 | The Matrix  9 | Café Society

Wildcard on text fields​

Search for tokens matching a pattern within full-text columns:

Query
SELECT id, titleFROM movies_idxWHERE description @@ ts_like('block%')ORDER BY id;
Result
 id | title----+---------------  4 | Jurassic Park  6 | Scary Movie

Combine with other search functions​

Query
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_like('sci%') AND description @@ ts_phrase('alien')ORDER BY id;
Result
 id | title                         | genre----+-------------------------------+--------  7 | Star Trek: The Motion Picture | sci-fi

See also​