How to select the nth row of the table in PostgreSQL?

Here we will discuss the technique to fetch nth row of the table in PostgreSQL. I will use the concept of the LIMIT and OFFSET Clause.

Syntax to fetch nth row of the table in PostgreSQL

 

SELECT col_1, col_2,...   FROM table_name   [ ORDER BY ... ]   [ LIMIT { number | ALL } ] [ OFFSET number ]

LIMIT Clause is used to limit the data amount returned by the SELECT statement. 

OFFSET allows retrieving just a portion of the rows that are generated by the rest of the query.

I will create a table to understand the concept. 

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")   );   --Inserted some records   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');

Get the Nth row of the table in PostgreSQL

If you want to fetch the record of the 5th row then execute the following query.

 

SELECT * FROM public."Customer" LIMIT 1 OFFSET 4;

 If you want to fetch the record of 3th row then execute the following query.

 

SELECT * FROM public."Customer" LIMIT 1 OFFSET 2;

 

Now we will create a function that will get the nth row of the "Customer" table.

 

CREATE OR REPLACE FUNCTION get_nth_row_customer(nNumber integer) RETURNS TABLE (        "Id" int,     "FName" text,     "LName" text,     "UserName" text,     "Password" text,     "Contact" text,     "Address" text )      AS $BODY$ BEGIN          RETURN QUERY SELECT * FROM public."Customer" LIMIT 1 OFFSET nNumber-1; END; $BODY$ LANGUAGE plpgsql;

 

Get the Nth row of the table throw function get_nth_row_customer

 

SELECT * FROM get_nth_row_customer(4);

 

Result:

 Id |   FName   |  LName   | UserName  | Password |   Contact   |  Address

----+-----------+----------+-----------+----------+-------------+-----------

  5 | Permanand | Tripathi | peram@123 | xyz      | 98988876676 | Gorakhpur

(1 row)