How to show row numbers in PostgreSQL query?

sql

join

performance

best-practices

byAnton Shumikhin·Dec 4, 2024

To include sequential row numbers in your PostgreSQL query results, you can use the ROW_NUMBER() function along with the OVER() clause. If you want the results sorted, add an ORDER BY clause within OVER().

Here's a general template:

SELECT 
    ROW_NUMBER() OVER (ORDER BY column_to_sort) AS row_number,
    other_columns
FROM 
    table_name;

Replace column_to_sort, other_columns, and table_name with your real column names and table name respectively. This will output a new column row_number in your result set, numbering rows in the order you specified.

However, if you don't care about the order and merely want to number the rows as they appear, simply omit the ORDER BY clause:

SELECT 
    ROW_NUMBER() OVER () AS row_number,
    *
FROM 
    table_name;

This will append a row_number column to the query result without any specific sorting.

Dealing with specific scenarios

There are several scenarios that you might encounter while implementing ROW_NUMBER() function:

Taming the NULL Beast

In case you have NULL values in your column_to_sort and you prefer them to be at the tail end, you can use the NULLS LAST option:

SELECT 
    ROW_NUMBER() OVER (ORDER BY column_to_sort NULLS LAST) AS row_number,
    *
FROM 
    table_name;

This way, if any row has a NULL in the column_to_sort, it'll be considered the least of all and appear last.

Acing Performance on Sweaty Datasets

Remember, ROW_NUMBER() could slow your query down when dealing with larger datasets. So, ensure your sort column is indexed properly. Alternatively, consider numbering at the app layer if it offers better performance.

Cultivating Compatibility with older PostgreSQL versions

For Postgres versions before 8.4 where ROW_NUMBER() isn't available, you can use a count on a self-join as a workaround:

SELECT 
    COUNT(t1.id) AS row_number,  -- Where we, um, "invented" our own row_number function
    t2.*
FROM 
    table_name t1
JOIN 
    table_name t2 ON t1.id <= t2.id
GROUP BY 
    t2.id
ORDER BY 
    row_number;

Replace id with your primary key column. This is not as awesome as our ROW_NUMBER() but, sometimes, necessity is indeed the mother of (re)invention.

Running wild when primary keys are absent

When your table doesn't have a primary key to order by, we can use the array_agg function. Although, beware of the Scooby Doo-monster known as performance cost hiding behind that tree!

WITH numbered_rows AS (
    SELECT array_agg(t.*) AS rows  -- Packing all tuples into one big performance-sucking array. Yikes!
    FROM your_table t
)

SELECT 
    row_number() OVER (),
    r.*
FROM 
    numbered_rows,
    LATERAL unnest(rows) as r;

Untangling Complex Queries

When your query has complex window function usage (and even if it resembles a bowl of spaghetti), encapsulate the logic in a subquery or a CTE:

WITH numbered AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY your_sort_column) AS row_number  -- Our neat and tidy row numbering
    FROM 
        your_table
)

SELECT * FROM numbered;  -- Ah! So clean and crisp!