Martin J. Hsu

better arbitrary buckets in postgresql

how to better bucket sequences into arbitrary buckets in postgresql This method is more declarative and easier to maintain. The 2nd cte computes the non-inclusive end values and a reasonable human friendly name for the bucket. int4range would have been nice, but it can't handle null end values.

with cte_buckets_starts(start_) as (
    select ARRAY[
        1,
        2,
        3,
        4,
        5,
        6,
        7,
        8,
        9,
        10,
        11,
        12,
        13,
        16,
        19,
        22,
        25,
        31,
        37
    ]
), cte_buckets(name, start_, end_) as (
    select
        case
            when length_ = 1 then start_::text
            when length_ is null then concat(start_, '+')
            else concat(start_, '-', start_+length_-1)
        end as name_,
        start_,
        start_ + length_ as end_

    from
        (
            select
                start_,
                lead(start_) over (order by start_) - start_ as length_
            from
                (select unnest(start_) from cte_buckets_starts) as b(start_)
        ) as x
)



select
        coalesce(cte_b.name, cte_b2.name) as name,
        x.number
from
        (
                select generate_series(1, 60) as number
        ) as x left join cte_buckets cte_b on (
                1=1
                and x.number>=cte_b.start_
                and x.number<cte_b.end_
        ) join cte_buckets cte_b2 on (
                1=1
                and cte_b2.end_ is null
        )
        ;
post, sql, postgresql, recipe, better
more...