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:

SELECT id, title, genre
FROM movies_idx
WHERE TERM_GT(genre, 'drama')
ORDER BY id;
 id | title                           | genre
----+---------------------------------+-----------
  4 | Jurassic Park                   | fantasy
  5 | Harry Potter...                 | fantasy
  1 | The Matrix                      | sci-fi
  ...

Less than or equal​

Find genres up to and including 'comedy':

SELECT id, title, genre
FROM movies_idx
WHERE TERM_LTE(genre, '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:

SELECT id, title, genre
FROM movies_idx
WHERE TERM_GTE(genre, 'comedy') AND TERM_LTE(genre, 'fantasy')
ORDER BY genre, id;
 id | title                                       | genre
----+---------------------------------------------+---------
  6 | Scary Movie                                 | comedy
 10 | The Grand Budapest Hotel                    | comedy
  9 | Café Society                                | drama
  4 | Jurassic Park                               | fantasy
  5 | Harry Potter and the Order of the Phoenix   | fantasy

Negation with range​

Find everything outside a range:

SELECT id, title, genre
FROM movies_idx
WHERE NOT TERM_LTE(genre, 'drama')
ORDER BY id;

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

SELECT id, title, genre
FROM movies_idx
WHERE TERM_GTE(genre, 'sci-fi') AND PHRASE(description, 'galaxy')
ORDER BY id;
 id | title                           | genre
----+---------------------------------+--------
  7 | Star Trek: The Motion Picture   | sci-fi
  8 | Alien                           | sci-fi

Available functions​

FunctionDescription
TERM_GT(column, value)Greater than
TERM_GTE(column, value)Greater than or equal
TERM_LT(column, value)Less than
TERM_LTE(column, value)Less than or equal

All comparisons are lexicographic (string ordering), not numeric.

See also​