PostgreSQL provides a built-in Window function named “ROW_NUMBER()” that operates on a set of rows and assigns a unique integer to each row. The set/collection of records is referred to as a “Window”. The ROW_NUMBER() function assigns the consecutive numbering/ranking to the rows which ultimately assists us in data analysis and manipulation.

This write-up presents a comprehensive guide on the usage of the ROW_NUMBER() function using suitable examples.

How to Use the ROW_NUMBER() Function in Postgres?

In Postgres, “ROW_NUMBER()” is used with the “OVER” clause to get the row number, as depicted in the following syntax:

ROW_NUMBER() OVER (
[PARTITION BY col_list ] 
[ORDER BY col_list]
);

- In Postgres, a “Window” is referred to as the set of rows on which ROW_NUMBER() is applied.
- The “PARTITION BY” clause is optional and can be used to split the “window” into partitions or groups.
- If the “PARTITION BY” clause is omitted, then the ROW_NUMBER() considers the whole window as one partition.
- "ORDER BY" specifies the order in which the numbers will be assigned.

Example 1: How Does ROW_NUMBER() Work in Postgres?

In this example, we will utilize a “programming_languages” table that we have already created in our database:

SELECT *,
ROW_NUMBER() OVER (
PARTITION BY language
ORDER BY id DESC
)
FROM programming_languages;

In this code:

- The “ROW_NUMBER()” function is used with the “OVER” clause to get the row number within the associated partition.
- The “PARTITION BY” clause is used to split the table by language.
- The "ORDER BY" clause organizes the partitions descendingly.

From the output, it is clear that the consecutive ranking has been assigned to the given table.

Example 2: ROW_NUMBER() With Subquery

This example explains how to use the ROW_NUMBER() function with the subquery to get the list of unique records:

SELECT DISTINCT *,
ROW_NUMBER() OVER (
ORDER BY language
)
FROM (
SELECT DISTINCT language
FROM programming_languages
) 
programming_languages;

In the above-stated code block:

- A subquery can be used with the DISTINCT operator to get the unique records.
- After that, the ROW_NUMBER() can be utilized in the outer query to assign the numbering to the unique records:

The output shows that the numbering has been assigned to the unique “languages”.

Note: The ROW_NUMBER() works on the result set prior to the DISTINCT clause. Therefore, using a DISTINCT operator with the ROW_NUMBER() wouldn’t exclude the duplicates.

Example 3: ROW_NUMBER() for Pagination

In Postgres, a technique named pagination is used to retrieve chunks of records instead of showing all records of a result set. Usually, the LIMIT clause is used to get the limited data, however, the ROW_NUMBER() can also be used as its alternate. Here is an example:

SELECT * FROM
(SELECT *,
ROW_NUMBER() OVER (
ORDER BY language)
FROM programming_languages
)
programming_languages
WHERE id BETWEEN 4 AND 7;

This query retrieves the records between id 4 and 7 and assigns them consecutive row numbers:

That's all from this Postgres guide on ROW_NUMBER() function.

Conclusion

In PostgreSQL, a built-in Window function named “ROW_NUMBER()” is used with the OVER clause to operate on a set of rows and assigns a unique integer to each row. The “PARTITION BY” clause is optional and can be used with the ROW_NUMBER() to split the “window” into partitions or groups. If the "PARTITION BY" clause is skipped, the whole window will be treated as a single partition. This post provided a thorough guide on how to use the ROW_NUMBER() function in Postgres.