{"id":126,"date":"2024-11-19T13:43:41","date_gmt":"2024-11-19T06:43:41","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=126"},"modified":"2025-01-01T16:34:05","modified_gmt":"2025-01-01T09:34:05","slug":"postgresql-between","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-between\/","title":{"rendered":"PostgreSQL BETWEEN: Performing Range Tests"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the PostgreSQL BETWEEN<\/code> operator in the WHERE<\/code> clause to check if a value is between two values.<\/p>\n\n\n\n

PostgreSQL BETWEEN operator #<\/a><\/h2>\n\n\n\n

The BETWEEN<\/code> operator is a comparison operator that returns true if a value is between two values.<\/p>\n\n\n\n

Here’s the syntax of the BETWEEN<\/code> operator:<\/p>\n\n\n

value<\/span> BETWEEN<\/span> low AND<\/span> high<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The BETWEEN<\/code> operator returns true if the value<\/code> is between low<\/code> and high<\/code>:<\/p>\n\n\n
low <= value<\/span> <= high<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In SQL, the BETWEEN operator is a shorthand of writing the following expression:<\/p>\n\n\n
value<\/span> >= low AND<\/span> value<\/span> <= high<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Hence, the BETWEEN<\/code> operator simplifies the above range test.<\/p>\n\n\n\n
PostgreSQL BETWEEN operator example #<\/a><\/h2>\n\n\n\n
Suppose we have the following inventories<\/code> table:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> inventories (\n  name<\/span> VARCHAR<\/span>(255<\/span>),\n  brand VARCHAR<\/span>(50<\/span>),\n  quantity INT<\/span>,\n  price DECIMAL<\/span>(19<\/span>, 2<\/span>)\n);\n\nINSERT<\/span> INTO<\/span>\n  inventories (name<\/span>, brand, quantity, price)\nVALUES<\/span>\n  ('iPhone 14 Pro'<\/span>, 'Apple'<\/span>, 10<\/span>, 999.99<\/span>),\n  ('Galaxy S23 Ultra'<\/span>, 'Samsung'<\/span>, 15<\/span>, 1199.99<\/span>),\n  ('Pixel 7 Pro'<\/span>, 'Google'<\/span>, 8<\/span>, 899.99<\/span>),\n  ('Xperia 1 IV'<\/span>, 'Sony'<\/span>, 7<\/span>, 1299.99<\/span>);<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Data:<\/p>\n\n\n
       name       |  brand  | quantity |  price\n------------------+---------+----------+---------\n iPhone 14 Pro    | Apple   |       10 |  999.99\n Galaxy S23 Ultra | Samsung |       15 | 1199.99\n Pixel 7 Pro      | Google  |        8 |  899.99\n Xperia 1 IV      | Sony    |        7 | 1299.99<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
The following example uses the BETWEEN<\/code> operator to find the products in the inventories<\/code> table with  prices are between 899.99<\/code> and 999.99<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  price\nFROM<\/span>\n  inventories\nWHERE<\/span>\n  price BETWEEN<\/span> 899.99<\/span> AND<\/span> 999.99<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
     name      | price\n---------------+--------\n iPhone 14 Pro | 999.99\n Pixel 7 Pro   | 899.99<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
NOT BETWEEN operator #<\/a><\/h2>\n\n\n\n
The NOT<\/code> operator negates the BETWEEN<\/code> operator:<\/p>\n\n\n
value<\/span> NOT<\/span> BETWEEN<\/span> low AND<\/span> high<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
The NOT BETWEEN<\/code> returns true if the value is less than low<\/code> or greater<\/code> than high:<\/p>\n\n\n
value<\/span> < low OR<\/span> value<\/span> > high<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
For example, the following statement uses the NOT BETWEEN<\/code> operator to find products whose prices are not between 899.99<\/code> and 999.99<\/code>:<\/p>\n\n\n
SELECT<\/span>\n  name<\/span>,\n  price\nFROM<\/span>\n  inventories\nWHERE<\/span>\n  price NOT<\/span> BETWEEN<\/span> 899.99<\/span> AND<\/span> 999.99<\/span>;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
       name       |  price\n------------------+---------\n Galaxy S23 Ultra | 1199.99\n Xperia 1 IV      | 1299.99<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
In practice, you’ll use the BETWEEN<\/code> operator in the WHERE<\/a><\/code> clause to filter rows based on a range of values.<\/p>\n\n\n\n