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:

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:

Which returns the following records:

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?

Execution of the above SQL gives us the following:

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:

Returns the following rows from Postgres:

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.

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:

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:

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:

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:

When we add our “BETWEEN” clause, we get:

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:

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

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

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

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

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.