相同的查询在修复语法错误和正确的强制转换后在postgres中工作：

SELECT DISTINCT
       CASE WHEN c1 BETWEEN 1 AND 5 THEN '1-5'
            WHEN c1 BETWEEN 6 AND 9 THEN '6-10'
            ELSE c1::text END AS c1 
FROM   tab1;

结果必须是字符串类型- text 是postgres的首选类型。但按字符串表示法排序对数字没有好处。因此，我建议采用这种方法进行适当的分类：

SELECT CASE WHEN c1 BETWEEN 1 AND 5 THEN '1-5'
            WHEN c1 BETWEEN 6 AND 9 THEN '6-10'
            ELSE c1::text END AS new_c1   -- avoid duplicate of source name
FROM   tab1 t
GROUP  BY new_c1
ORDER  BY min(t.c1);

db<>在这里摆弄

如果我理解正确，您将使用两个聚合级别。第一个得到范围：

select min(c1), max(c1)
from (select t.*, row_number() over (order by c1) as seqnum
      from t
     ) t
group by c1 - seqnum;

然后，第二种方法重新聚合：

select string_agg(range, ',' order by min_c1)
from (select (case when min(c1) <> max(c1) then min(c1) || '-' || max(c1) else min(c1)::text end) as range, min(c1) as min_c1
      from (select t.*, row_number() over (order by c1) as seqnum
            from t
           ) t
      group by c1 - seqnum
     ) x

这里有一个db<>小提琴说明了这个逻辑的工作原理。