How to Get Row Number in PostgreSQL

Often PostgreSQL database administrators need to calculate row number in database tables for different purposes and applications. PostgreSQL provides row_number() partition function for this purpose. In this article, we will learn why we need to calculate row number in PostgreSQL, how to get row number in PostgreSQL and its features.

Why to Get Row Number in PostgreSQL

There are several common applications where you need to calculate row number in PostgreSQL database.

1. Ranking – You may need to rank table rows based on one or more criteria. In such cases, it is easy to assign row numbers to each row, based on those criteria, to rank them.
2. Pagination – When you need to display a large number of rows of information on a web page, then web developers tend to paginate the results. For this purpose, it is very useful to assign row numbers to paginate them. Pagination is very useful while working with large datasets.
3. Duplicate Removal – It is also useful in assigning row numbers to identify duplicate rows in a table. You can do this by numbering rows within a group and selecting rows where row_number>1.
4. Data Analysis & Reporting – Row numbers are frequently required for data analysis and reporting system where you need to rank rows based on one or more criteria.

How to Get Row Number in PostgreSQL

Let us say you have the following sales table.

#- create table sales(id int, product varchar(255),orders int);

#- insert into sales(id,product,orders) values
        (1,'A',50),
        (2,'B',60),
        (3,'A',70),
        (4,'B',40),
        (5,'C',50),
        (1,'A',50),
        (4,'B',40);

#- select * from sales;
+------+---------+--------+
| id   | product | orders |
+------+---------+--------+
|    1 | A       |     50 |
|    2 | B       |     60 |
|    3 | A       |     70 |
|    4 | B       |     40 |
|    5 | C       |     50 |
|    1 | A       |     50 |
|    4 | B       |     40 |
+------+---------+--------+

There are 3 ways to assign row number to this table in PostgreSQL.

1. Basic Usage with Order By

In this case, we mention ORDER BY clause within OVER() function. The ORDER BY clause ensures that the row numbers are consistent and predictable. This is one of the most common ways to get row number.

Here is the query to implement this.

#- select id,product,orders,
   row_number() over (order by orders desc) as row_num
   from sales;

+------+---------+--------+---------+
| id   | product | orders | row_num |
+------+---------+--------+---------+
|    3 | A       |     70 |       1 |
|    2 | B       |     60 |       2 |
|    1 | A       |     50 |       3 |
|    5 | C       |     50 |       4 |
|    1 | A       |     50 |       5 |
|    4 | B       |     40 |       6 |
|    4 | B       |     40 |       7 |
+------+---------+--------+---------+

In this query, the entire table is considered as a single group of rows. Its rows are ordered by orders column. Then each row is sequentially numbered starting from 1. In case of a tie in orders column value, row number is sequentially assigned.

This solution is useful if you want to sequentially number all rows in a table, without any partitioning.

2. Using Partition

You can group the rows into different partitions so that the row number restarts from 1, for each partition. Here is an example to rank rows for each product.

#- select id,product,orders,row_number() 
over (partition by product order by orders desc) as row_num
from sales;

+------+---------+--------+---------+
| id   | product | orders | row_num |
+------+---------+--------+---------+
|    3 | A       |     70 |       1 |
|    1 | A       |     50 |       2 |
|    1 | A       |     50 |       3 |
|    2 | B       |     60 |       1 |
|    4 | B       |     40 |       2 |
|    4 | B       |     40 |       3 |
|    5 | C       |     50 |       1 |
+------+---------+--------+---------+

In the above query, rows for each unique value of product column are considered as single group. For each group, the row number begins from 1 and increases sequentially.

This solution is useful if you want to partition the table rows into different groups and assign unique row numbers for each group, starting from 1.

3. Without Using ORDER BY

You can also use row_number() function without using ORDER BY clause. In this case, each row will be assigned a unique row number but its order may not be predictable.

#- select id,product,orders,row_number() over () as row_num
from sales;

+------+---------+--------+---------+
| id   | product | orders | row_num |
+------+---------+--------+---------+
|    1 | A       |     50 |       1 |
|    2 | B       |     60 |       2 |
|    3 | A       |     70 |       3 |
|    4 | B       |     40 |       4 |
|    5 | C       |     50 |       5 |
|    1 | A       |     50 |       6 |
|    4 | B       |     40 |       7 |
+------+---------+--------+---------+

It is advisable to use ORDER BY clause to get stable and predictable row numbers.

Features of Row Number in PostgreSQL

Here are some important features of row_number() function in PostgreSQL.

1. Sequential Numbering – It will assign a sequential and unique integer for each row starting from 1, and incrementing by 1 for each row. There are no gaps in the sequence. In case of ties, each row still gets a unique row number, whose order may or may not be defined using additional columns in order by clause.
2. Partitioning – It supports optional partitioning that splits the result into multiple partitions. For each partition the row number resets to 1.
3. Window Function – It assigns sequence number for a given set of rows related to present row. For each window, the numbering starts from 1.
4. Dynamic Numbering – These row numbers are calculated dynamically on the fly when you run the query, and not stored anywhere.
5. Sorting – You need to use ORDER BY clause within OVER() to correctly determine sequence of row numbers.

Conclusion

In this article, we have learnt why you need to get row number in PostgreSQL database. We have also learnt the different ways to calculate row number in PostgreSQL. Depending on your requirement, you can use any of these methods.

Also read:
How to Create User in PostgreSQL
How to List All Users in PostgreSQL
How to Alter Column from Null to NOT Null