Postgres width_bucket: function width_bucket(bigint, integer[]) does not exist

Question

I run two different queries. This one works well

with t(n) as(
    values
           (1),
           (1),
           (1),
           (2),
           (10)
)
select
       width_bucket(n,array[1,3,6,15]) g,
       count(*)
from t
group by g
order by g

but when I run this (real) query, it reports error on bucket

with data as (
    select
           vendor_id,
           count(distinct pi.id) as cnt
    from payment_invoice_items as pii
    join payment_invoices as pi
        on pii.invoice_id = pi.id
        and pii.deleted_at isnull
    group by vendor_id
)
select
       width_bucket(data.cnt, array[1,10,20,30,40,100,200,400]) as grp,
       count(*)
from data
group by grp
order by grp

The data.cnt is a one column of integer data. why does this report error?

[42883] ERROR: function width_bucket(bigint, integer[]) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Thanks

The data.cnt is a one column of integer data No, COUNT returns a BIGINT, so you will have to explicitly cast it to INTEGER in the width_bucket function. — 404
– 404, Commented Oct 20, 2020 at 9:07

404 · Accepted Answer · 2020-10-20 09:12:41Z

2

The COUNT function returns type BIGINT, not INTEGER. Hence the error on that type:

[42883] ERROR: function width_bucket(bigint, integer[]) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

You will need to cast COUNT's result explicitly to INTEGER in order to use it in your function:

with data as (
    select
           vendor_id,
           count(distinct pi.id) as cnt
    from payment_invoice_items as pii
    join payment_invoices as pi
        on pii.invoice_id = pi.id
        and pii.deleted_at isnull
    group by vendor_id
)
select
       width_bucket(data.cnt::INTEGER, array[1,10,20,30,40,100,200,400]) as grp,
       count(*)
from data
group by grp
order by grp

answered Oct 20, 2020 at 9:12

404

8,7522 gold badges33 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres width_bucket: function width_bucket(bigint, integer[]) does not exist

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related