PostgreSQL LIMIT

Summary: in this tutorial, you will learn how to use the PostgreSQL LIMIT clause to retrieve only a subset of rows.

Getting started with PostgreSQL LIMIT clause #

The LIMIT clause allows you to retrieve only a subset of rows from a SELECT statement. It can be handy when working with a large result set.

Here’s the syntax of the LIMIT clause:

SELECT
  column1,
  column2
FROM
  table_name
ORDER BY
  sort_expression
LIMIT
  row_count;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the table name where you want to retrieve data in the FROM clause.
• Second, provide a list of columns to return in the SELECT clause.
• Third, sort the rows using the ORDER BY clause.
• Finally, specify the number of rows (row_count) to return in the LIMIT clause.

PostgreSQL evaluates the clauses in the following order:

1. FROM
2. SELECT
3. ORDER BY
4. LIMIT

It’s crucial that you should always use the LIMIT clause with the ORDER BY clause to get the expected result set.

If you don’t use the LIMIT clause with ORDER BY clause, the SELECT statement returns rows in an unspecified order, and the LIMIT clause will pick the top rows from the unordered rows, potentially leading to unexpected results.

Setting up a sample table #

We’ll create a new table called products, which will store product information including their names and prices. The products table will have some sample data to practice with the LIMIT clause:

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2)
);

INSERT INTO
  products (name, price)
VALUES
  ('iPhone 16 Pro Max', 1649.00),
  ('iPhone 16', 829.00),
  ('Galaxy S24 Ultra', 1299.99),
  ('Galaxy S24 FE', 949.99),
  ('Pixel 9 Pro', 799.00),
  ('Pixel 8a', 399.00),
  ('OnePlus 12', 745.00),
  ('OnePlus Open', 1514.99),
  ('Galaxy Z Fold 6', 2019.99);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL LIMIT clause example #

The following statement uses the LIMIT clause to return the top five most expensive products from the products table:

SELECT
  name,
  price
FROM
  products
ORDER BY
  price DESC
LIMIT 5;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name        |  price
-------------------+---------
 Galaxy Z Fold 6   | 2019.99
 iPhone 16 Pro Max | 1649.00
 OnePlus Open      | 1514.99
 Galaxy S24 Ultra  | 1299.99
 Galaxy S24 FE     |  949.99Code language: plaintext (plaintext)

How it works.

First, the ORDER BY clause sorts the rows returned by the FROM clause by the price from high to low:

Second, the SELECT clause retrieves data from the name and price columns:

Third, the LIMIT clause picks the top five rows and returns them:

PostgreSQL LIMIT OFFSET clause #

To skip some rows before returning a subset of rows, you can use the OFFSET clause:

SELECT
  column1,
  column2
FROM
  table_name
ORDER BY
  sort_expression
LIMIT
  row_count
OFFSET
  skip_count;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The statement works like the one without the OFFSET clause if the skip_count is zero or null.

The OFFSET clause skips several rows (skip_count) before the LIMIT clause returns a subset of rows.

The following example uses the LIMIT OFFSET to skip two rows before returning the next five rows from the products table:

SELECT
  name,
  price
FROM
  products
ORDER BY
  price DESC
LIMIT 5 OFFSET 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name       |  price
------------------+---------
 OnePlus Open     | 1514.99
 Galaxy S24 Ultra | 1299.99
 Galaxy S24 FE    |  949.99
 iPhone 16        |  829.00
 Pixel 9 Pro      |  799.00Code language: plaintext (plaintext)

How it works.

First, the FROM clause returns rows from the products table.

Second, the SELECT clause retrieves data from the name and price columns.

Third, the ORDER BY clause sorts the rows returned by the FROM clause by the price from high to low.

Finally, the OFFSET clause skips two rows and the LIMIT clause returns the next five rows:

Using PostgreSQL LIMIT OFFSET for pagination #

The technique that break down a result set into smaller chunks is known as pagination.

Suppose you want to display the product list on pages; each page contains a limited number of products.

The following statement returns the first page with the first three products:

SELECT
  product_id,
  name,
  price
FROM
  products
ORDER BY
  price DESC
LIMIT 3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id |       name        |  price
------------+-------------------+---------
          9 | Galaxy Z Fold 6   | 2019.99
          1 | iPhone 16 Pro Max | 1649.00
          8 | OnePlus Open      | 1514.99Code language: plaintext (plaintext)

To display the next three products on page two, we can use the OFFSET to skip the first three products and the LIMIT clause to return the following three products:

SELECT
  product_id,
  name,
  price
FROM
  products
ORDER BY
  price DESC
LIMIT 3 OFFSET 3;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id |       name       |  price
------------+------------------+---------
          3 | Galaxy S24 Ultra | 1299.99
          4 | Galaxy S24 FE    |  949.99
          2 | iPhone 16        |  829.00Code language: plaintext (plaintext)

Finally, retrieve the products for the last page using the LIMIT and OFFSET clauses:

SELECT
  product_id,
  name,
  price
FROM
  products
ORDER BY
  price DESC
LIMIT 3 OFFSET 3 * 2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 product_id |    name     | price
------------+-------------+--------
          5 | Pixel 9 Pro | 799.00
          7 | OnePlus 12  | 745.00
          6 | Pixel 8a    | 399.00Code language: plaintext (plaintext)

Summary #

• Use the PostgreSQL LIMIT clause to control the number of rows a query returns.
• Use the OFFSET clause to skip some rows before returning a subset of rows.
• The LIMIT and OFFSET can be helpful for pagination.

Quiz #