Postgres Row_Number Function

The row_number() function in PostgreSQL is a window function that assigns a unique integer value to each row in the result set.

For example, we can use this function to assign a unique ID or rank for each row or to partition the data into subgroups. We can then perform specific calculations on the resulting subgroups.

In this post, we will explore the fundamentals of working with the row_number() function in PostgreSQL.

Prerequisites:

To follow along with the examples in this post, ensure that you have the following:

Installed PostgreSQL on your system
Basic understanding of SQL syntax and PostgreSQL commands
A PostgreSQL database for demonstration purposes

Let us begin!

PostgreSQL Row_Number() Function

The following shows the syntax of the row_number() function in PostgreSQL:

ROW_NUMBER() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)

There are two main components when working with the row_number() function. These include:

The PARTITION BY clause is optional which allows us to divide the result set into partitions and assign a unique number to each row within each partition. The function treats the whole result set as a single partition if not specified.
Finally, the ORDER BY clause specifies the order of the rows in each partition. If we do not specify the order parameter, the function assigns the row numbers arbitrarily.

PostgreSQL Row_Number Function Example

Let us start with a basic example of using the row_number function. Suppose we have a table that stores the product information as shown in the following:

create table products (
id serial primary key not null,
product_name varchar(100) not null,
price int
);

insert into products(product_name, price)
values ('Product A', 10),
('Product B', 20),
('Product C', 30),
('Product D', 40);

select * from products;

Output:

Using the row_number function, we can assign a unique row number to each row as shown in the following query:

SELECT ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num, product_name, price
FROM products;

Output:

Row_Number Function with Partition By

Suppose we have another column in the products table called “category_id” which is mapped to the category of each product as shown in the following:

Suppose we want to assign a unique row number within each category. Then, we can use the row_number function with a PARTITION BY clause as shown in the following:

SELECT ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS row_num, product_name, price, category_id
FROM products;

Resulting Table:

Conclusion

There you have it! A fundamental knowledge of how to work with the PostgreSQL row_number() function. This function is handy when you need to rank the items, split a table into ranked partitions, and more.