TutorialsArena

PostgreSQL: Limiting Query Results with LIMIT

Control the number of rows returned by a query using PostgreSQL's LIMIT clause. This guide explains its usage for pagination and data sampling.

Limiting Query Results in PostgreSQL with `LIMIT`

Understanding the `LIMIT` Clause

In PostgreSQL, the `LIMIT` clause restricts the number of rows returned by a `SELECT` statement. This is very useful for pagination (displaying data in pages), for retrieving a subset of data for testing or sampling, or for getting only the top or bottom results from a query.

`LIMIT` Clause Syntax

The basic syntax is:

SELECT column1, column2, ... FROM table_name ORDER BY column_name LIMIT count;

Where:

  • count: The maximum number of rows to return. A `NULL` value means no limit is applied; 0 returns an empty set.
  • Optional `ORDER BY` clause: Determines the order of rows when using `LIMIT`. Without `ORDER BY`, the order of returned rows is not guaranteed.

Adding `OFFSET`

To skip a certain number of rows before applying the limit, use the `OFFSET` clause:

SELECT column1, column2, ... FROM table_name ORDER BY column_name LIMIT count OFFSET offset;

Where:

  • offset: The number of rows to skip before returning the `count` rows.

Examples: Using `LIMIT` and `OFFSET`

These examples assume a table named `CAR` with columns `Car_id`, `Car_name`, `Body_Style`, and `Car_Price`. You would need to create and populate this table in your database to run these queries. The queries use `ORDER BY` for consistent results.

1. Creating and Populating the Table

CREATE TABLE Statement

CREATE TABLE CAR (
  Car_id SERIAL PRIMARY KEY,
  Car_name VARCHAR(50),
  Car_Model VARCHAR(50),
  Car_Price INTEGER,
  Car_Color VARCHAR(20),
  BODY_Style VARCHAR(30)
);
INSERT INTO Statement

INSERT INTO CAR (Car_name, Car_Model, Car_Price, Car_Color, BODY_Style) VALUES
('Acura', 'NSX', 47075, 'yellow', 'coupe'),
('Porsche', '911 Carrera', 399999, 'silver', 'coupe'),
('Nissan', 'Armada', 36777, 'blue', 'wagon'),
// ... more rows ...
;

2. Limiting the Number of Rows

SELECT with LIMIT

SELECT Car_id, Car_name, Body_Style
FROM CAR
ORDER BY Car_id
LIMIT 6;

3. Getting Top N Rows

SELECT with LIMIT (Top N)

SELECT Car_id, Car_name, Car_Price
FROM CAR
ORDER BY Car_Price DESC
LIMIT 5;

4. Using OFFSET

SELECT with LIMIT and OFFSET

SELECT Car_id, Car_name, Body_Style
FROM CAR
ORDER BY Car_id
LIMIT 5 OFFSET 2;

Conclusion

The `LIMIT` clause in PostgreSQL is essential for controlling the number of rows returned by a query. Combining it with `OFFSET` enables pagination and retrieving specific subsets of data. Always use `ORDER BY` when using `LIMIT` to ensure consistent and predictable results.