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 the 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 that 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:


NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...