Question Details

No question body available.

Tags

sql postgresql gaps-and-islands

Answers (2)

Accepted Answer Available
Accepted Answer
March 11, 2026 Score: 6 Rep: 8,493 Quality: Expert Completeness: 100%

This is "gaps and islands" case.

  • Each change in "name" (considering order by "index") is start of new group.
  • Number of (sum) "new group" is groupNum.
  • Then we can GROUP BY groupNum values for this group ("island").
with src as(
SELECT 
FROM unnest(ARRAY[
    NULL, NULL,
    'foo', 'foo', 'foo',
    NULL, NULL, NULL,
    'bar', 'bar',
    NULL,
    'foo', 'foo'
]) WITH ORDINALITY _(name, index)
)
SELECT groupNum,coalesce(min(name),'null') name,min(index) startI,max(index) endI
FROM(
  SELECT 
    ,sum(isGap)OVER(order by index) groupNum
  FROM(
    SELECT 
      ,CASE WHEN name IS DISTINCT FROM lag(name)OVER(order by index) THEN 1 ELSE 0 END isGap
    FROM src
  )a
)b
GROUP BY groupNum
ORDER BY groupNum
  • coalesce(min(name),'null') is used only for clarity. Use min(name) as name in real query.

Output

 groupnum | name | startI | endI 
----------+------+--------+------
        0 | null |      1 |    2
        1 | foo  |      3 |    5
        2 | null |      6 |    8
        3 | bar  |      9 |   10
        4 | null |     11 |   11
        5 | foo  |     12 |   13

If first "name" is not null (and lead(...) is null), groupNum starts from 1.
This does not affect the result.

If you need fixed range num (groupNum) use lead(...) with additional parameters

CASE WHEN name IS DISTINCT FROM lag(name,1,name)OVER(order by index) THEN 1 ELSE 0 END

Fiddle

There used comparing as "IS DISTINCT FROM" to compare nulls and notnull values.
Examples in doc

Example for IS DISTINCT FROM

select null IS DISTINCT FROM null as null_null,
   null IS DISTINCT FROM 'foo' as null_foo,
  'bar' IS DISTINCT FROM 'foo' as bar_foo,
  'bar' IS DISTINCT FROM 'bar' as bar_bar;

Result is

IS DISTINCT FROM
 null_null | null_foo | bar_foo | bar_bar 
-----------+----------+---------+---------
 f         | t        | t       | f

Update1 As @Charlieface recommended, it is useful to provide another way to output the final data.

with src as(
SELECT 
FROM unnest(ARRAY[NULL, NULL, 'foo', 'foo', 'foo',  NULL, NULL, NULL, 'bar', 'bar', NULL, 'foo', 'foo']
           ) WITH ORDINALITY _(name, index)
)
SELECT groupNum,min(name)as name,int8range(min(index),max(index),'[]') range
FROM(
  SELECT  ,sum(isGap)OVER(order by index) groupNum
  FROM(
    SELECT ,CASE WHEN name IS DISTINCT FROM lag(name)OVER(order by index) THEN 1 ELSE 0 END isGap
    FROM src
  )a
)b
GROUP BY groupNum
ORDER BY groupNum

Output is

groupnum | name |  range  
----------+------+---------
        0 | null | [1,3)
        1 | foo  | [3,6)
        2 | null | [6,9)
        3 | bar  | [9,11)
        4 | null | [11,12)
        5 | foo  | [12,14)

Note that although the query specifies the inclusion of range boundaries in the form of '[]', PostgreSQL shows us what it will store in the form of '[)' the same range.

March 12, 2026 Score: 2 Rep: 30,791 Quality: Medium Completeness: 80% 
with gaps as(select,name is distinct from lag(name)over w1 as gap
             from dat
             window w1 as(order by index)
),islands as(select,count(*)filter(where gap)over w2 as island
             from gaps
             window w2 as(order by index))
select name,concatws('-',min(index),nullif(max(index),min(index))) as range
from islands
group by name,island
order by min(index);
name range
null 1-2
foo 3-5
null 6-8
bar 9-10
null 11
foo 12-13

Actual logic and performance is no different from ValNik's, but it's made to generate the desired result shown in the question. Also, I'm naming my windows and use a native conditional count() with a filter clause, in contrast to emulating it with an int-yielding case that feeds a sum().