Enumerating Table Partitions In Postgres Table

Suppose I have a table like this: id | part | value ----+-------+------- 1 | 0 | 8 2 | 0 | 3 3 | 0 | 4 4 | 1 | 6 5 | 0 | 13 6 | 0 | 4 7 | 1

Solution 1:

Yes, that is simple:

SELECT id, part, value,
       row_number() OVER (PARTITION BY grp ORDER BY id) - 1 AS number
FROM (SELECT id, part, value,
             sum(part) OVER (ORDER BY id) AS grp
      FROM mytable
     ) AS q;

 id | part | value | number 
----+------+-------+--------
  1 |    0 |     8 |      0
  2 |    0 |     3 |      1
  3 |    0 |     4 |      2
  4 |    1 |     6 |      0
  5 |    0 |    13 |      1
  6 |    0 |     4 |      2
  7 |    1 |     2 |      0
  8 |    0 |    11 |      1
  9 |    0 |    15 |      2
 10 |    0 |     3 |      3
 11 |    0 |     2 |      4
(11 rows)