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;
|
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:
