Limit Postgres Rows
Introduction
In the following tutorial, we will teach how to limit Postgres rows by using “BETWEEN” and other PostgreSQL strategies, including limit rows, limit offset, select top, and inner join. We’ll conduct an in-depth examination of between, along with a study of the other methods mentioned above.
Prerequisites
- How to use SQL in your PostgreSQL database, using PG Admin or some other database administration tool, or by using code in your application to connect to your Postgres database and use SQL to query it.
- Understanding of how “WHERE” and “AND” work in Postgres SQL.
PostgreSQL between
In PostgreSQL, “BETWEEN” is often used within a WHERE clause; returning a boolean value (true or false). We use this to filter a recordset to return rows that meet values found between x and y, as seen here:
1 2 3 | SELECT t_product FROM Products WHERE c_costy BETWEEN a AND b |
Postgres limit with between
We’ll start learning how to use between and understand how inclusion works, by creating a real world situation. We want to find a product that fits our budget (and other) constraints from a table called “tbl_Products”. First, let’s show the records in our “tbl_Products” table by executing the following query against our PostgreSQL database:
1 2 | SELECT t_product, c_costy FROM tbl_Products |
Which returns the following records:
t_product | c_costy |
---|---|
2007 Ford Fiesta w 120,892 miles | $2891 |
Edison Vers 2 – RWD – Mid Range | $43000 |
Edison Vers 2 – AWD – Long Range | $51000 |
Edison Vers 2 – AWD – Performance | $62000 |
Edison Vers 1 – Qx1 AWD | $80000 |
Edison Vers 1 – Qx2 AWD | $100000 |
Edison Vers 1 – Qx3 AWD | $135000 |
Now let’s insert a “WHERE” clause with the “BETWEEN” operator to our “SELECT” statement. Our goal is to limit the recordset to the car(s) within a certain price range. Our budget max is $3,000 but we don’t want to buy a piece of junk. Can you guess what the new line of SQL will look like?
1 2 3 | SELECT t_product, c_costy FROM tbl_Products WHERE c_costy BETWEEN 2499 AND 3500 |
Execution of the above SQL gives us the following:
t_product | c_costy |
---|---|
2007 Ford Fiesta w 130,892 miles | $2891 |
Can you see why the results were reduced from seven rows down to one?
If you are looking at your Postgres database and thinking, “I don’t want to measure by price only because miles are important.” So we will add more rows to our table, as well as a column called “i_miles” to keep track of how many miles each car has on it.
Our new table with all records looks like:
1 2 | SELECT t_product, i_miles, c_costy FROM tbl_Products |
Returns the following rows from Postgres:
t_product | i_miles | c_costy |
---|---|---|
2007 Ford Fiesta | 130892 | $2991 |
2010 Mazda 3 | 78000 | $9998 |
Edison Vers 2 – RWD – Mid Range | 0 | $43000 |
Edison Vers 2 – AWD – Long Range | 0 | $51000 |
Edison Vers 2 – AWD – Performance | 0 | $62000 |
Edison Vers 1 – Qx1 AWD | 0 | $80000 |
Edison Vers 1 – Qx2 AWD | 0 | $100000 |
Edison Vers 1 – Qx3 AWD | 0 | $135000 |
Now we can filter our “products” table by both the “i_miles” and “c_costy” fields to find the car(s) within our budget range AND have the number of miles we are comfortable with.
1 2 3 4 | SELECT t_product, i_miles, c_costy FROM tbl_Products WHERE c_costy =< 2999 AND i_miles BETWEEN 0 AND 145000 |
What to notice about the SQL code above:
- We changed the filtering of “c_costy” to use “=<” operator instead of “BETWEEN” because we don’t have a lower limit on the price we are looking for when shopping.
- We are using “BETWEEN” to filter our recordset to show us rows where miles are between 0 and 145000 but this is not really necessary. We could have also used “=<” here, too.
So. Let’s fix this by exposing more of our database structure:
1 2 | SELECT t_product, i_seats, i_miles, c_costy FROM Products |
t_product | i_seats | i_miles | c_costy |
---|---|---|---|
2007 Ford Fiesta | 4 | 130892 | $2991 |
2008 Mazda Miata | 2 | 120000 | $8000 |
2010 Mazda 3 | 5 | 80000 | $9800 |
2011 Fried Mustard | 4 | 120000 | $9999 |
Edison Vers 2 – RWD – Mid Range | 5 | 0 | $43000 |
Edison Vers 2 – AWD – Long Range | 5 | 0 | $51000 |
Edison Vers 2 – AWD – Performance | 5 | 0 | $62000 |
Edison Vers 1 – Qx1 AWD | 7 | 0 | $80000 |
Edison Vers 1 – Qx2 AWD | 7 | 0 | $100000 |
Edison Vers 1 – Qx3 AWD | 7 | 0 | $135000 |
OK. Let’s say our purchase constraints are:
- Our budget is $12,500.
- We want a car with less than 190000 miles.
- We want the car to have a certain number of seats. We need a back seat for the kids, which removes 2-seat cars from our options. We don’t want a wagon, so 4-5 seats is our upper limit. The Edison Vers 1, though not a wagon, does allow for two extra (small) seats in the very back. But fortunately, Edisons are far out of our price range. We’re assuming here a limitation of 2 people in the front area and 3 in the back seat.
So we will re-create the query to reflect the above requirements:
1 2 3 4 5 | SELECT t_product, n_mpg, i_miles, c_costy FROM tbl_Products WHERE c_costy =< 10000 AND i_miles =< 150000 AND n_seata BETWEEN 4 AND 5 |
Postgres between and inclusion
This brings up an important question: Is the “BETWEEN” operator inclusive? In other words, does “BETWEEN 5 and 8” give us only 6 and 7 OR does it give us 5, 6, 7, and 8? Yes. It is inclusive in that way where “BETWEEN 5 and 6” returns 5 and 6. Let’s see what the new SQL above returns:
t_product | i_seats | i_miles | c_costy |
---|---|---|---|
2007 Ford Fiesta | 4 | 130892 | $2991 |
2008 Mazda Miata | 2 | 120000 | $8000 |
2010 Mazda 3 | 5 | 80000 | $9800 |
2011 Fried Mustard | 4 | 120000 | $9999 |
Learning to use “BETWEEN”
One more scenario for learning to use between in Postgres: We work at a school and want to get a list of students who are older or equal to 14 while being younger than or equal to 15. So yeah, 14 and 15 year olds. Here’s our unfiltered list of all students:
1 2 | SELECT t_name_student, n_age FROM tbl_students |
t_name_student | n_age |
---|---|
Georgio | 13.5 |
Beronica | 14 |
Jimmie | 14.5 |
Conica | 15 |
Jemes | 15.5 |
Kib | 16 |
When we add our “BETWEEN” clause, we get:
1 2 3 | SELECT t_name_student, n_age FROM tbl_students WHERE n_age BETWEEN 14 AND 15 |
t_name_student | n_age |
---|---|
Beronica | 14 |
Jimmie | 14.5 |
Conica | 15 |
Notice how Georgio was left out of our returned dataset because he is 13.5 years old, which is below our lower threshold of 14? Jemes and Kib were also left out because their ages are above our upper threshold of 15. By now you have a basic understanding of the syntax, but I’ll show it here:
1 | BETWEEN [LOWER threshold] AND [UPPER threshold] |
Now that we have a deeper understanding of how we can limit Postgres rows using the between operator, let’s look at some other popular methods for limiting rows returned from a Postgres table.
PostgreSQL limit rows
We can use LIMIT followed by an integer in order to return a specific number of rows.
Limit rows SQL example
1 2 3 | SELECT t_name_columnn FROM tbl_name_table LIMIT [i_number_records]; |
Analysis
The above SQL will return a number of rows equal to i_number_records.
PostgreSQL limit offset
Here wee use LIMIT and OFFSET to return a specific number of rows determined by an integer we provide.
Limit with offset example
1 2 3 | SELECT t_name_columnn FROM tbl_name_table LIMIT [i_number_records] OFFSET [i_skip]; |
Analysis
The above PostgreSQL query will return a number of rows equal to i_number_records and begin by skipping a number of rows equal to the integer after the OFFSET clause.
PostgreSQL Select Top
Use SELECT TOP to return a number of rows determined by an integer we provide after “TOP”.
Limit with Select Top example
1 2 3 | SELECT TOP [i_number_records] t_name_columnn FROM tbl_name_table ORDER BY t_name_column; |
Analysis
The above Postgres SQL will return a number of rows equal to i_number_records at the top of the recordset, based on how the ORDER BY clause orders the recordset.
PostgreSQL Inner Join
In Postgres, you can use a join, specifically “inner join” to combine rows from multiple tables by using values that are common in each PostgreSQL table.
Inner Join example
1 2 3 4 | SELECT tbl_a.column_a, tbl_b.column_b, etc. FROM tbl_a INNER JOIN tbl_b ON tbl_a.column_in_common = tbl_b.column_in_common; |
Analysis
- SELECT: Determines which columns are retrieved from the tables in Postgres.
- FROM: One of the tables we are combining.
- INNER JOIN: The other table we are combining.
- ON: Tells PostgreSQL which columns are the common link between tables.
Conclusion
We learned how to limit Postgres rows using various methods, including BETWEEN, LIMIT ROWS, LIMIT OFFSET, SELECT TOP, and INNER JOIN.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started