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:
1 2 3 4 | ROW_NUMBER() OVER( [PARTITION BY col1, col2,…] [ORDER BY col3,col4,…] ); |
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:
1 2 3 4 5 6 | CREATE TABLE student ( id INT PRIMARY KEY, name VARCHAR (50), lastname VARCHAR (50), department VARCHAR (20) ); |
- Next, insert the records into the Postgres table as follows:
1 2 3 4 5 6 7 8 | INSERT INTO student (id, name, lastname, department) VALUES (1, 'james', 'bench', 'ict'), (2, 'leo', 'esteves', 'engineering'), (3, 'luffy', 'mancy', 'engineering'), (4, 'bernard', 'del torro', 'statistics'), (5, 'jackson', 'detroit', 'statistics'), (6, 'risa', 'jensen', 'ict'); |
- The appearance of the table should resemble the following:
1 2 3 4 5 6 7 8 9 10 | studentdb=# select * from student; id | name | lastname | department ----+---------+-----------+------------- 1 | james | bench | ict 2 | leo | esteves | engineering 3 | luffy | mancy | engineering 4 | bernard | del torro | statistics 5 | jackson | detroit | statistics 6 | risa | jensen | ict (6 rows) |
Postgres ROW_NUM example
With the dataset created, this following example shows how to use the ROW_NUM
function in a query:
1 2 3 4 5 6 7 | SELECT id, name, department, ROW_NUMBER () OVER (ORDER BY id) FROM student; |
The results of the query should resemble the following:
1 2 3 4 5 6 7 8 9 | id | name | department | row_number ----+---------+-------------+------------ 1 | james | ict | 1 2 | leo | engineering | 2 3 | luffy | engineering | 3 4 | bernard | statistics | 4 5 | jackson | statistics | 5 6 | risa | ict | 6 (6 rows) |
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:
1 2 3 4 5 6 7 8 9 10 11 | SELECT id, name, department, ROW_NUMBER () OVER ( PARTITION BY department ORDER BY name ) FROM student; |
The results of the above query should look like the following table:
1 2 3 4 5 6 7 8 9 | id | name | department | row_number ----+---------+-------------+------------ 2 | leo | engineering | 1 3 | luffy | engineering | 2 1 | james | ict | 1 6 | risa | ict | 2 4 | bernard | statistics | 1 5 | jackson | statistics | 2 (6 rows) |
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.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started