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:

SQL

SELECT id, title, genreFROM movies_idxWHERE genre @@ 'sci-fi'ORDER BY id;

 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:

SQL

SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_any(['comedy', 'drama'])ORDER BY id;

 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:

SQL

SELECT id, title, genreFROM movies_idxWHERE NOT (genre @@ 'sci-fi')ORDER BY id;

 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

Combine with phrase search

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

SQL

SELECT id, titleFROM movies_idxWHERE genre @@ 'sci-fi' AND description @@ ts_phrase('alien spacecraft')ORDER BY id;

 id | title
----+-------------------------------
  7 | Star Trek: The Motion Picture

Combine with analytics

Search and aggregate in the same query:

SQL

SELECT genre, COUNT(*) AS count, AVG(runtime) AS avg_runtimeFROM movies_idxWHERE description @@ ts_phrase('biggest blockbuster')GROUP BY genreORDER BY genre;

 genre     | count | avg_runtime
-----------+-------+-------------
 adventure |     1 |         127
 comedy    |     1 |          88

See also

• Range Queries — compare terms with >, <, >=, <=
• Inverted index query functions — @@, ts_any and more