Skip to main content

LIMIT and OFFSET Clause in PostgreSQL

It can be troublesome to obtain records from a table that contains large amounts of data. Obtaining large amounts of data from a table via a PostgreSQL query can be a reason for poor performance. Using LIMIT and OFFSET we can shoot that type of trouble. LIMIT Clause is used to limit the data amount returned by the SELECT statement while OFFSET allows retrieving just a portion of the rows that are generated by the rest of the query.

Syntax: SELECT statement with LIMIT clause


SELECT Col_1, Col_2, ...Col_3
FROM table_name
LIMIT number_of_rows

Syntax: SELECT statement with LIMIT clause and OFFSET clause


SELECT Col_1, Col_2, ...Col_3
FROM table_name
LIMIT number_of_rows OFFSET row_number



Examples

Suppose we have a customer table which has a large amount of data, using the following the query you can create the customer table schema.

CREATE TABLE public."Customer"
(
    "Id" serial NOT NULL,
    "FName" text COLLATE pg_catalog."default",
    "LName" text COLLATE pg_catalog."default",
    "UserName" text COLLATE pg_catalog."default",
    "Password" text COLLATE pg_catalog."default",
    "Contact" text COLLATE pg_catalog."default",
    "Address" text NOT NULL DEFAULT 0.0,
    CONSTRAINT "pk_Customer_Id" PRIMARY KEY ("Id")
);

Insert some records in the customer table


INSERT INTO public."Customer"(
    "FName", "LName", "UserName", "Password", "Contact", "Address")
                VALUES ( 'Dilip Kumar', 'Singh', 'dilip@123', 'xyz', '9098876676', 'Noida');

INSERT INTO public."Customer"(
                "FName", "LName", "UserName", "Password", "Contact", "Address")
                VALUES ( 'Dilshad', 'Ahmad', 'dilshad@123', 'xyz', '8898646427', 'Delhi');

INSERT INTO public."Customer"(
                "FName", "LName", "UserName", "Password", "Contact", "Address")
                VALUES ( 'Ashish', 'Singh', 'ashish@123', 'xyz', '9087778765', 'Ghaziabad');

INSERT INTO public."Customer"(
                "FName", "LName", "UserName", "Password", "Contact", "Address")
                VALUES ( 'Mr Dilip', 'Singh', 'dk@123', 'xyz', '9087876654', 'Gorakhpur');

INSERT INTO public."Customer"(
                "FName", "LName", "UserName", "Password", "Contact", "Address")
                VALUES ( 'Permanand', 'Tripathi', 'peram@123', 'xyz', '98988876676', 'Gorakhpur');

INSERT INTO public."Customer"(
                "FName", "LName", "UserName", "Password", "Contact", "Address")
                VALUES ( 'Naina', 'Singh', 'naina@123', 'xyz', '9998875756', 'Gr Noida');

Examples of PostgreSQL LIMIT Clause

If I want to select three records only from the customer table then use the following query.

SELECT "Id", "FName", "LName", "UserName", "Password", "Contact", "Address"
                FROM public."Customer" LIMIT 3;

Limit and Offset clause in postgresql

Example of the PostgreSQL LIMIT Clause with OFFSET clause

If you want to select the three records from a particular offset then use the following query.

SELECT "Id", "FName", "LName", "UserName", "Password", "Contact", "Address"
                FROM public."Customer" LIMIT 3 OFFSET 3;



This concept is also very useful in the pagination of the records, suppose in UI table grid I want to bind only 20 records at a time then we have to use pagination concept here. The value of the limit will be 20 and the OFSETET value will have some part of whatever record you want to get. You can see the following example, I am creating a function which will retrieve the records as per PageSize and PageIndex.

CREATE OR REPLACE FUNCTION public.fn_getcustomer(
                pagesize integer,
                pageindex integer)
    RETURNS TABLE(fname text, lname text, username text, password text, contact text, address text)
    LANGUAGE 'sql'
AS $BODY$
SELECT
                "FName",
                "LName",
                "UserName",
                "Password",
                "Contact",
                "Address"
FROM public."Customer"   
LIMIT pageSize OFFSET pageSize*pageIndex;
$BODY$;

Here pagesize and pageindex are two parameters, pagesize means the number of records you want to fetch and pageindex means number of page index like 0,1,2,3, etc.

For example


SELECT *  FROM public.fn_getcustomer(20, 0);

Results:


SELECT * FROM public.fn_getcustomer(20, 1)

Results: