Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE mytable (id, seq) AS
VALUES
(8, 1),(3, 2),(2, 3),(null, 4),(324, 5),(82, 6),(89, 7),(null, 8),(1, 9),(2, 10),(90, 10),(null, 11);

SELECT * FROM mytable

SELECT 12




id
seq


8
1

3
2

2
3

null
4

324
5

82
6

89
7

null
8

1
9

2
10

90
10

null
11



SELECT 12


SELECT
    CASE WHEN id IS NULL THEN 1 ELSE 0 END as grp_index,
    id
FROM mytable


grp_index
id


0
8

0
3

0
2

1
null

0
324

0
82

0
89

1
null

0
1

0
2

0
90

1
null



SELECT 12


SELECT
    1+COUNT(CASE WHEN id IS NULL THEN 1 END) OVER (ORDER BY seq ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as grp,
    id
FROM mytable
   
    


grp
id


1
8

1
3

1
2

1
null

2
324

2
82

2
89

2
null

3
1

3
2

3
90

3
null



SELECT 12

id	seq
8	1
3	2
2	3
null	4
324	5
82	6
89	7
null	8
1	9
2	10
90	10
null	11

grp_index	id
0	8
0	3
0	2
1	null
0	324
0	82
0	89
1	null
0	1
0	2
0	90
1	null

grp	id
1	8
1	3
1	2
1	null
2	324
2	82
2	89
2	null
3	1
3	2
3	90
3	null