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)


No comments:

Post a Comment

Please do not enter any spam link in the comment box.

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...