Skip to main content

Edit this page

Range Queries

Match terms that are above, below, or between reference values using lexicographic comparison. Useful for filtering categories, tags, or any indexed string values by order.

See Setup for the shared dataset used in all examples.

Greater than​

Find genres that come after 'drama' lexicographically:

Query
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_gt('drama')ORDER BY id;
Result
 id | title                                     | genre----+-------------------------------------------+---------  1 | The Matrix                                | sci-fi  2 | The Matrix Reloaded                       | sci-fi  3 | The Matrix Revolutions                    | sci-fi  5 | Harry Potter and the Order of the Phoenix | fantasy  7 | Star Trek: The Motion Picture             | sci-fi  8 | Alien                                     | sci-fi

Less than or equal​

Find genres up to and including 'comedy':

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

Combining range conditions​

Use AND to define a range:

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

Negation with range​

Find everything outside a range:

Query
SELECT id, title, genreFROM movies_idxWHERE NOT (genre @@ ts_le('drama'))ORDER BY id;
Result
 id | title                                     | genre----+-------------------------------------------+---------  1 | The Matrix                                | sci-fi  2 | The Matrix Reloaded                       | sci-fi  3 | The Matrix Revolutions                    | sci-fi  5 | Harry Potter and the Order of the Phoenix | fantasy  7 | Star Trek: The Motion Picture             | sci-fi  8 | Alien                                     | sci-fi

Range query on genre plus full-text search on description:

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

See also​