PostgreSQL query: obtain concise string of numbers

Obtain a concise string of the numbers, for example, given 1,2,3,6,7,8,9,12,14, we expect 1-3,6-9,12,14.

Here is the Table:

create table tt8 (c1 numeric);
insert into tt8 values
(1),(2),(3),(6),(7),(8),(9),(12),(14);

Use Table tt8, Here is what the result should look like:

numbers
---------------
1-3,6-9,12,14

Here is what I have so far, but it’s giving me type error. I don’t think this is the correct way.

select c1,
case
when c1 = 1|2|3 then '1-3'
when c1 = 6|7|8|9 then '6-9'
else c1
end
from tt8;

Answer

You can use a gaps-and-islands approach and then aggregation. The following gets the groups:

select min(c1) || (case when count(*) = 1 then '' else '-' || max(c1) end)
from (select tt8.*, row_number() over (order by c1) as seqnum
      from tt8
     ) t
group by (c1 - seqnum);

You can then put them into a single string:

select string_agg(val, ',' order by min_c1)
from (select min(c1) || (case when count(*) = 1 then '' else '-' || max(c1) end) as val, min(c1) as min_c1
      from (select tt8.*, row_number() over (order by c1) as seqnum
            from tt8
           ) t
      group by (c1 - seqnum)
     ) t;

Here is a db<>fiddle.