How to Get Row Number in PostgreSQL

Last updated on May 17th, 2020 at 06:08 am

PostgreSQL allows you to get specific row number for your data for ranking, reporting and data analysis. Here’s how to get row number in PostgreSQL. You can use it for further analysis based on row Id in PostgreSQL.

Table of Contents

How to Get Row Number in PostgreSQL

Here’s how to get specific row number in PostgreSQL. We will look at how to get row number using ROW_NUMBER() window function available in PostgreSQL 8.4+ as well as using plain SQL for PostgreSQL <8.4.

Let’s say you have the following table sales(order_date,sale)

postgres=# create table sales(order_date date,sale int);

postgres=# insert into sales values('2020-04-01',210),
           ('2020-04-02',125),('2020-04-03',150),('2020-04-04',230),
           ('2020-04-05',200),('2020-04-10',220),('2020-04-06',25),
           ('2020-04-07',215),('2020-04-08',300),('2020-04-09',250);

postgres=# select * from sales;
 order_date | sale
------------+------
 2020-04-01 |  210
 2020-04-02 |  125
 2020-04-03 |  150
 2020-04-04 |  230
 2020-04-05 |  200
 2020-04-10 |  220
 2020-04-06 |   25
 2020-04-07 |  215
 2020-04-08 |  300
 2020-04-09 |  250

Bonus Read : How to Calculate Percentile in PostgreSQL

How to Get Row Number in PostgreSQL using ROW_NUMBER()

Since PostgreSQL 8.4, you can easily show row number in PostgreSQL using ROW_NUMBER() function. Here’s the SQL query to get row id in PostgreSQL.

postgres=# select row_number() over(), *
           from   sales;
 row_number | order_date | sale
------------+------------+------
          1 | 2020-04-01 |  210
          2 | 2020-04-02 |  125
          3 | 2020-04-03 |  150
          4 | 2020-04-04 |  230
          5 | 2020-04-05 |  200
          6 | 2020-04-10 |  220
          7 | 2020-04-06 |   25
          8 | 2020-04-07 |  215
          9 | 2020-04-08 |  300
         10 | 2020-04-09 |  250

In the above SQL query, we use row_number() window function to generate row number for each row. We also use over() to tell PostgreSQL to display row number for all rows without any ordering.

Bonus Read : How to Calculate Median in PostgreSQL

If you want to order rows before you generate row numbers, you can add an ORDER BY clause in OVER(), as shown below

postgres=# select row_number() over(order by sale), *
           from   sales;
 row_number | order_date | sale
------------+------------+------
          1 | 2020-04-06 |   25
          2 | 2020-04-02 |  125
          3 | 2020-04-03 |  150
          4 | 2020-04-05 |  200
          5 | 2020-04-01 |  210
          6 | 2020-04-07 |  215
          7 | 2020-04-10 |  220
          8 | 2020-04-04 |  230
          9 | 2020-04-09 |  250
         10 | 2020-04-08 |  300

You cannot use WHERE clause in ROW_NUMBER or OVER() window functions. So if you want to filter data before generating row numbers, you need to use WHERE cause in a subquery, as shown below

postgres=# select row_number() over(order by sale), *
           from  ( select * from sales where sale>100) filtered_sales;
 row_number | order_date | sale
------------+------------+------
          1 | 2020-04-02 |  125
          2 | 2020-04-03 |  150
          3 | 2020-04-05 |  200
          4 | 2020-04-01 |  210
          5 | 2020-04-07 |  215
          6 | 2020-04-10 |  220
          7 | 2020-04-04 |  230
          8 | 2020-04-09 |  250
          9 | 2020-04-08 |  300

You can also get row number in PostgreSQL using generate_series. However, it is advisable not to generate row id using generate_series since it is not scalable.

Bonus Read : How to Fill Missing Dates in PostgreSQL using generate_series

How to get row number in PostgreSQL (<8.4) without ROW_NUMBER()

If you use PostgreSQL <8.4, then row_number() window function may not be available in it. In that case, you have to get row number in PostgreSQL with the help of a self-join. Here’s the query for it.

postgres=# SELECT    count(*) rownum, foo.*
           FROM      sales foo
           JOIN      sales bar
              ON (foo.order_date <= bar.order_date)
           GROUP BY  foo.order_date, foo.sale
           ORDER BY  rownum
           ;
 rownum | order_date | sale
--------+------------+------
      1 | 2020-04-10 |  220
      2 | 2020-04-09 |  250
      3 | 2020-04-08 |  300
      4 | 2020-04-07 |  215
      5 | 2020-04-06 |   25
      6 | 2020-04-05 |  200
      7 | 2020-04-04 |  230
      8 | 2020-04-03 |  150
      9 | 2020-04-02 |  125
     10 | 2020-04-01 |  210

After you get row number in PostgreSQL, you can use a PostgreSQL reporting tool to report your data in a table as shown below, and share with your team.