Introduction to PostgreSQL ROW_NUMBER
The PostgreSQL ROW_NUMBER() function is a windows function. The ROW_NUMBER() function manipulates the set of rows and the row’s set is termed as a window. We can use the PARTITION BY clause with the ROW_NUMBER() function which is optional, if we have defined then it handles the set of rows or window like splitting the set of rows into subsets. If the PARTITION BY clause is not defined then the entire result set of rows considered as the single partition. Also, we can use the ORDER BY clause with the ROW_NUMBER() function to order the rows.
Syntax
Consider the following syntax:
ROW_NUMBER()
OVER
(
[PARTITION BY column_name_1, column_name_2,…]
[ORDER BY column_name_3,column_name_4,…]
)
Explanation:
PARTITION BY: This is an optional clause in case of the ROW_NUMBER() function. The PostgreSQL ROW_NUMBER() divides set of rows into partitions or smaller sets.
ORDER BY: This defines how the order of the numbers should be assigned in the OVER clause.
How does PostgreSQL ROW_NUMBER function work?
The ROW_NUMBER() function operates on a set of rows which are termed s a window.mIf the PARTITION BY clause is specified then the row number will increment by one and will start with one.
If we have not specified then PARTITION BY clause then the ROW_NUMBER function will consider the entire window or set of results as a single partition.
We will create tables named ‘category’ and ‘items’ in order to understand the examples of the PostgreSQL ROW_NUMBER() function in detail.
Consider the following CREATE TABLE statement in order to create the category and items tables.
CREATE TABLE category
Code:
(
category_id serial PRIMARY KEY,
category_name VARCHAR(80) NOT NULL
);
CREATE TABLE items
Code:
(
item_id serial PRIMARY KEY,
item_name VARCHAR(80) NOT NULL,
item_price numeric,
category_id numeric
);
Now, we will insert some data in the ‘category’ and ‘items’ tables by using INSERT TABLE statement:
Code:
INSERT INTO category(category_name) values
('furniture'),
('Electronics'),
('Cloths');
Output:
select * from category;
Illustrate the result of the above statement by using the following snapshot and SQL statement.
Code:
INSERT INTO items(item_name,item_price,category_id)
VALUES ('Chair',800,1),
('Table',1200,1),
('Sofa',8000,1),
('Bed',8000,1),
('Mobile',12000,2),
('Laptop',30000,2),
('Charger',1200,2),
('Cable',1200,2),
('Pant',1700,3),
('Shirt',1300,3),
('Inner wear',300,3),
('Top wear',300,3);
Output:
select * from items;
Illustrate the result of the above statement by using the following snapshot and SQL statement.
Examples to implement ROW_NUMBER function in PostgreSQL
Below are the examples mentioned:
Example #1
Code:
SELECT
item_id,
item_name,
category_id,
ROW_NUMBER () OVER (ORDER BY item_id)
FROM
items;
Output:
Explanation: Here in the above example, we have not defined the PARTITION BY clause which results in the entire result as a single PARTITION in the ROW_NUMBER() function. We have defined the ORDER BY clause which in result sorts the result set by item_id. For each row, the PostgreSQL ROW_NUMBER() function assigns numeric values based on the item_id order.
Example #2
Consider the following statement where we will use the item_name in the ORDER BY clause.
So for each row as per the item name order, the numbers are assigned.
Code:
SELECT
item_id,
item_name,
category_id,
ROW_NUMBER () OVER (
ORDER BY item_name
)
FROM
items;
Output:
Example #3
Consider the following statement where we will use the PARTITION BY clause on the category_id column which will divide the result set into partitions based on the values of the category_id column. For each row of the partition is starts with one and then increase by one for remaining rows in the same partition. As per the values in the item_name column, the PostgreSQL ORDER BY clause sorts the rows in every partition.
Code:
SELECT
item_id,
item_name,
category_id,
ROW_NUMBER () OVER (
PARTITION BY category_id
ORDER BY item_name
)
FROM
items;
Output:
Example #4
DISTINCT operator with the PostgreSQL ROW_NUMBER() function
For each distinct row in the items table the ROW_NUMBER() function assigns a number.
Code:
SELECT DISTINCT
item_price,
ROW_NUMBER () OVER (ORDER BY item_price)
FROM
items
ORDER BY
item_price;
Output:
Example #5
We can use the pagination technique to display the subset of rows. There are various methods to achieve the Pagination like the use of LIMIT clause or use of the ROW_NUMBER() function. Consider the following statement to select the 4 rows starting at row index 5:
Code:
SELECT
*
FROM
(
SELECT
item_id,
item_name,
item_price,
ROW_NUMBER () OVER (ORDER BY item_name)
FROM
items
) x
WHERE
ROW_NUMBER BETWEEN 5 AND 8;
Output:
Advantages of using ROW_NUMBER function in PostgreSQL
- This function is used to generate the sequential numbers on the fly.
- This function is used to perform the pagination.
- We can find out the duplicate rows by using the this function.
- We can find out rows from a range of row by using the PostgreSQL ROW_NUMBER function.
- This function is used to sort rows.
Conclusion
We hope from the above article you have understood how to use the PostgreSQL ROW_NUMBER() function and how the PostgreSQL ROW_NUMBER() function works. Also, we have added some examples of this function to understand it in detail.
Recommended Articles
This is a guide to PostgreSQL ROW_NUMBER. Here we discuss an introduction to PostgreSQL ROW_NUMBER with appropriate syntax, working, and respective sample code for better understanding. You can also go through our other related articles to learn more –
