Group rows by an incrementing column in PostgreSQL

Question

I have the table shown below, with only one column. What I want to achieve is to separate all rows that have no gap in x, for example the numbers 1-3, 5-6 and 8-9 (because the gaps are 4 and 7).

+---+
| x |
+---+
| 1 |
| 2 |
| 3 |
| 5 |
| 6 |
| 8 |
| 9 |
+---+

I would like to make it look like this: a table with two columns (a and b), indicating the ranges where there are no gaps in the previous column x. For every gap a new record is inserted. How would I go about it in PostgreSQL?

+---+---+
| a | b |
+---+---+
| 1 | 3 |
| 5 | 6 |
| 8 | 9 |
+---+---+

dnoeth · Accepted Answer · 2018-11-07 20:56:56Z

1

You can compare the sequence with gaps to a sequence without gaps:

select min(x), max(x)
from
 (
   select x, 
      x-row_number() over (order by x) as dummy
   from tab
 ) as dt
group by dummy

  x | row_number | x - row_number 
| 1 |   1        |   0             -- same value for consecutive values without gaps
| 2 |   2        |   0
| 3 |   3        |   0
| 5 |   4        |   1
| 6 |   5        |   1
| 8 |   6        |   2
| 9 |   7        |   2

answered Nov 7, 2018 at 20:56

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Group rows by an incrementing column in PostgreSQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related