> > > LIMIT, OFFSET, ORDER BY and Pagination in PostgreSQL

LIMIT, OFFSET, ORDER BY and Pagination in PostgreSQL

LIMIT-OFFSET-ORDER BY-Pagination in PostgreSQLLIMIT and OFFSET are particularly useful clauses in PostgreSQL. LIMIT, as it’s name suggests, limits the number of rows returned in a SELECT query. For instance, if the limit count is 4, maximum of four rows are returned, or less than 4, if the query itself yield less number of rows.

 

Consider the following table:

CREATE TABLE products
(
 id integer NOT NULL,
 name character varying,
 cost double precision,
 manufacturer character varying,
 warranty character varying,
 category character varying,
 in_stock boolean,
 CONSTRAINT products_pkey PRIMARY KEY (id)
)

LIMIT, OFFSET, ORDER BY and Pagination in PostgreSQL

The following query, returns 4 rows:

SELECT * FROM products LIMIT 4 ;

LIMIT-OFFSET-ORDER BY-Pagination in PostgreSQL_002

 You should never assume the query to return rows in the same order as it appears in the table.

The OFFSET clause is used to skip a fixed number of rows in the result. For instance, OFFSET 5 will, skip the first 5 rows, and return the rest of the rows.

Example:

SELECT * FROM products OFFSET 5;

LIMIT-OFFSET-ORDER BY-Pagination in PostgreSQL

 LIMIT or OFFSET without an ORDER BY clause may return an unpredictable result set. It is always preferred to use ORDER BY clause to make it predictable and consistent across repeated execution of the same query.

An OFFSET of zero is as same as omitting the OFFSET clause.

LIMIT , OFFSET & ORDER BY for Pagination

In most of the real world scenarios, LIMIT and OFFSET together with ORDER BY is used to get the desired result.

As an example the following query returns the products, ordered by category and cost, skipping the first 5 products, limiting the result to 6.

SELECT * FROM products ORDER BY category,cost LIMIT 6 OFFSET 5 ;

 

LIMIT-OFFSET-ORDER BY-Pagination in PostgreSQL

This query would remind us the common pagination requirement found in e-commerce websites. The next set of results can be returned by changing the OFFSET value alone.

Remember, the last “page” returned by OFFSET 10, will return only 5 rows. Because our table only contain 15 rows in total.

A little more about the ORDER clause

The ORDER BY clause not always needs to be used along with a LIMIT or OFFSET. It can be used with any SELECT query. Sort has always two options, descending or ascending.

The default is ascending. To make it explicit one may use the ASC keyword:

SELECT * FROM products ORDER BY warranty ASC;

To sort in descending order use the DESC keyword:

SELECT * FROM products ORDER BY warranty DESC;

LIMIT-OFFSET-ORDER BY-Pagination in PostgreSQL

To learn more about the ORDER BY clause visit this post: [link to be updated]

Performance Considerations

PostgreSQL take into account the LIMIT clause while building the query plan. So, different LIMIT and OFFSET values generate different plans, and the order of returned row may change substantially. Use explicit ORDER BY clause to return a predictable result set.

LIMIT is also useful for DBAs to get some sample rows out of a table with large number of rows. A LIMIT 0 can be used in situations where you just want to know what are the columns available in the table.

A large OFFSET is inherently costly and yield poor performance. This is because the rows skipped still have to be computed internally by the server, even though it is not returned.

paginations_in_postgresql_sample_data_dump.sql

The following two tabs change content below.

Vipin Raj

Vipin Raj is a web developer specialized in PostgreSQL Database and Data Modeling, the man behind technobytz and a tech enthusiast. Having 3+ years of experience in the IT industry, he is currently pursuing his masters in computer science and information security. He spend his free time writing blog posts with the intension of sharing his knowledge to the tech community.

Leave a Reply

Your email address will not be published. Required fields are marked *