Skip to main content

Edit this page

Exact Value Matching

Match precise token values in indexed columns. This is the simplest form of search — find documents that contain an exact term.

See Setup for the shared dataset used in all examples.

Match a single value​

Match a single exact token with the @@ operator and a bare term:

Query
SELECT id, title, genreFROM movies_idxWHERE genre @@ 'sci-fi'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

Match multiple alternatives​

Use ts_any to match any of several values — more efficient than chaining OR:

Query
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_any(['comedy', 'drama'])ORDER BY id;
Result
 id | title                    | genre----+--------------------------+--------  6 | Scary Movie              | comedy  9 | Café Society             | drama 10 | The Grand Budapest Hotel | comedy

Negation​

Combine NOT with term operations to exclude matches:

Query
SELECT id, title, genreFROM movies_idxWHERE NOT (genre @@ 'sci-fi')ORDER BY id;
Result
 id | title                                     | genre----+-------------------------------------------+-----------  4 | Jurassic Park                             | adventure  5 | Harry Potter and the Order of the Phoenix | fantasy  6 | Scary Movie                               | comedy  9 | Café Society                              | drama 10 | The Grand Budapest Hotel                  | comedy

Search for an exact genre and a phrase in the description:

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

Combine with analytics​

Search and aggregate in the same query:

Query
SELECT genre, COUNT(*) AS count, AVG(runtime) AS avg_runtimeFROM movies_idxWHERE description @@ ts_phrase('biggest blockbuster')GROUP BY genreORDER BY genre;
Result
 genre     | count | avg_runtime-----------+-------+------------- adventure |     1 |         127 comedy    |     1 |          88

See also​