PostgreSQL – ROW_NUMBER Function

In PostgreSQL, the ROW_NUMBER() function is used to assign a unique integer to every row that is returned by a query.

Syntax:

ROW_NUMBER() OVER(
    [PARTITION BY column_1, column_2, …]
    [ORDER BY column_3, column_4, …]
)

Let’s analyze the above syntax:

• The set of rows on which the ROW_NUMBER() function operates is called a window.
• The PARTITION BY clause is used to divide the query set results. 
• The ORDER BY clause inside the OVER clause is used to set the order in which the query result will be displayed.

Example 1:

First, create two tables named mammals and Animal_groups:

CREATE TABLE Animal_groups (
    animal_id serial PRIMARY KEY,
    animal_name VARCHAR (255) NOT NULL
);

CREATE TABLE Mammals (
    mammal_id serial PRIMARY KEY,
    mammal_name VARCHAR (255) NOT NULL,
    lifespan DECIMAL (11, 2),
    animal_id INT NOT NULL,
    FOREIGN KEY (animal_id) REFERENCES Animal_groups (animal_id)
);

Now add some data to it:

INSERT INTO Animal_groups (animal_name)
VALUES
    ('Terrestrial'),
    ('Aquatic'),
    ('Winged');

INSERT INTO Mammals(mammal_name, animal_id, lifespan)
VALUES
    ('Cow', 1, 10),
    ('Dog', 1, 7),
    ('Ox', 1, 13),
    ('Wolf', 1, 11),
    ('Blue Whale', 2, 80),
    ('Dolphin', 2, 5),
    ('Sea Horse', 2, 3),
    ('Octopus', 2, 8),
    ('Bat', 3, 4),
    ('Flying Squirrels', 3, 1),
    ('Petaurus', 3, 2);

In the following query, we change the column in the ORDER BY clause to mammal_name, the ROW_NUMBER() function assigns the integer values to each row based on the mammal name order.

SELECT
    mammal_id,
    mammal_name,
    animal_id,
    ROW_NUMBER () OVER (
           ORDER BY mammal_name
        )
FROM
    Mammals;

Output:

Example 2:

The following query uses the ROW_NUMBER() function to assign integers to the distinct prices from the products table:

SELECT DISTINCT
    lifespan,
    ROW_NUMBER () OVER (ORDER BY lifespan)
FROM
    Mammals
ORDER BY
    lifespan;

Output: