row_num in Postgres

Introduction

The Postgres row_number function is a command used to assign a unique integer value to every row in a given result set. The set of rows the rownum in Postgres function operates on is called a window. This function can be very useful as it can be employed to divide the window into a defined subset in relation to the values in a column. This tutorial will explain how the rownum in Postgres function works along with providing working examples.

Prerequisite

• PostgreSQL must be properly installed and configured on the local system for the rownum in Postgres function to work.

What is Postgres ROW_NUM Function

The Postgres ROW_NUMBER function allows for assigning a unique integer value to every row within a specified result set.

The basic form of the ROW_NUM() function is as follows:

Following is a breakdown of the above function:

• The Postgres ROW_NUMBER() command acts on a window, or a set of rows.
• The PARTITION BY clause will divide the window into multiple subsets as defined by the query. However, if this is omitted, the set of rows, or the window, will be treated as a single partition.
• The ORDER BY clause dictates the way the order of the results will be presented.

Creating the Sample dataset

• Create a sample dataset by first generating a table named “student” with the following structure:

• Next, insert the records into the Postgres table as follows:

• The appearance of the table should resemble the following:

Postgres ROW_NUM example

With the dataset created, this following example shows how to use the ROW_NUM function in a query:

The results of the query should resemble the following:

Note that the PARTITION BY clause was not used in the above query. This results in the ROW_NUMBER function treating the above result set as the partition.

Following is another query example with the PARTITION BY clause:

The results of the above query should look like the following table:

Note that in this second query example that the PARTITION BY clause was used. Here it divides the window into a defined subset in relation to the values in the ‘department’ column. The ROW_NUMBER function in this example assigns the number “1” to each partition, or row, with increments of one for the next row within the same partition.

Conclusion

This tutorial explained how the rownum in Postgres function works to assign a unique integer value to rows in a result set. The tutorial specifically covered what the rownum in Postgres function is, how to creating a sample dataset and insert the records into the created Postgres table. The tutorial provided a breakdown of the ROW_NUM function and gave two working examples, one with and one without using the PARTITION BY clause. Remember that the PARTITION BY clause will divide the window into multiple subsets as defined by the query. However, if the clause is omitted, then the set of rows, or the window, will be treated as a single partition.