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:

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:

SELECT t_product, c_costy
FROM tbl_Products

Which returns the following records:

t_productc_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?

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_productc_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:

SELECT t_product, i_miles, c_costy
FROM tbl_Products

Returns the following rows from Postgres:

t_producti_milesc_costy
2007 Ford Fiesta130892$2991
2010 Mazda 378000$9998
Edison Vers 2 – RWD – Mid Range0$43000
Edison Vers 2 – AWD – Long Range0$51000
Edison Vers 2 – AWD – Performance0$62000
Edison Vers 1 – Qx1 AWD0$80000
Edison Vers 1 – Qx2 AWD0$100000
Edison Vers 1 – Qx3 AWD0$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.

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:

SELECT t_product, i_seats, i_miles, c_costy
FROM Products
t_producti_seatsi_milesc_costy
2007 Ford Fiesta4130892$2991
2008 Mazda Miata2120000$8000
2010 Mazda 3580000$9800
2011 Fried Mustard4120000$9999
Edison Vers 2 – RWD – Mid Range50$43000
Edison Vers 2 – AWD – Long Range50$51000
Edison Vers 2 – AWD – Performance50$62000
Edison Vers 1 – Qx1 AWD70$80000
Edison Vers 1 – Qx2 AWD70$100000
Edison Vers 1 – Qx3 AWD70$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:

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_producti_seatsi_milesc_costy
2007 Ford Fiesta4130892$2991
2008 Mazda Miata2120000$8000
2010 Mazda 3580000$9800
2011 Fried Mustard4120000$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:

SELECT t_name_student, n_age
FROM tbl_students
t_name_studentn_age
Georgio13.5
Beronica14
Jimmie14.5
Conica15
Jemes15.5
Kib16

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

SELECT t_name_student, n_age
FROM tbl_students
WHERE n_age BETWEEN 14 AND 15
t_name_studentn_age
Beronica14
Jimmie14.5
Conica15

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:


GeSHi Error: GeSHi could not find the language code (using path /nas/content/live/orkbprod/wp-content/plugins/codecolorer/lib/geshi/) (code 2)

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

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

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

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

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

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.