Skip to main content
SereneDBSereneDB
897Contact Us

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:

SQL
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_gt('drama')ORDER BY id;
 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':

SQL
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_le('comedy')ORDER BY id;
 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:

SQL
SELECT id, title, genreFROM movies_idxWHERE genre @@ ts_ge('comedy') AND genre @@ ts_le('fantasy')ORDER BY genre, id;
 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:

SQL
SELECT id, title, genreFROM movies_idxWHERE NOT (genre @@ ts_le('drama'))ORDER BY id;
 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:

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

See also​

Edit this page ↗