PostgreSQL Data Pagination Query (Using LIMIT and OFFSET)

Data pagination queries are a common method used to meet user requirements by displaying a given number of data records instead of all the data. In PostgreSQL, you can use the LIMIT and OFFSET keywords to implement data pagination queries.

What is LIMIT and OFFSET?

  • LIMIT: Used to limit the quantity of returned data.
  • OFFSET: Used to skip a specified number of records from the set of returned data.

LIMIT

The function of LIMIT is to restrict the quantity of returned data. When using the command SELECT * FROM table_name LIMIT num, PostgreSQL will select data from the table based on the set quantity.

-- Select the first 10 pieces of data
SELECT * FROM table_name LIMIT 10;

OFFSET

The function of OFFSET is to skip a specified number of records calculated starting from the table. When using the command SELECT * FROM table_name LIMIT num OFFSET m, PostgreSQL will start selecting data from the (m+1)-th record.

-- Stop at the 10-th piece of data (Note: The original text says "Stop", but logically it means select starting from offset)
SELECT * FROM table_name LIMIT 5 OFFSET 5;

Precautions for Using LIMIT and OFFSET

  • If commands with LIMIT and OFFSET are used, PostgreSQL will ignore other parameters in the SQL statement (such as ORDER BY, etc.).
  • The smaller the value of LIMIT, the faster the speed to retrieve data, but it may result in more pages being returned.

Examples

-- Create table
CREATE TABLE table_name (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

-- Select all data and use LIMIT and OFFSET
SELECT * FROM table_name LIMIT 5 OFFSET 3;

This SQL command will select 5 pieces of data from the table instead of starting to calculate from the third record.