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

Question

I expected results when I run a query passing an array of integers as the second argument to width_bucket such as this:

select width_bucket(5, array[0, 1, 2, 3, 4, 5, 6, 7, 8] )

But if this array contains a decimal number such as this:

select width_bucket( 5, array[0, 1.1, 2, 3, 4, 5, 6, 7, 8] )

I get the error:

ERROR:  function width_bucket(integer, numeric[]) does not exist
LINE 1: select width_bucket( 5, array[0, 1.1, 2, 3, 4, 5, 6, 7, 8] )
               ^

My use case is I'm using this function to calculate my data's breaks, which won't always be integers (and I've seen used elsewhere used with width_bucket in that codebase so I'm not sure why it's not working here).

How can I get width_bucket to accept an array of non-integers?

[Edited to add link to the Postgres docs for the width_bucket function, which says that it accepts "anycompatiblearray" although I don't see where the list of "compatible" array types are defined.]

can you try function overload: postgresql.org/docs/current/xfunc-overload.html — jian
– jian, Commented Apr 25, 2022 at 3:34
@Mark do you mean to declare different arguments types when creating the width_bucket function? width_bucket is a built-in function so I'm not creating it and thus can't define it with different parameters. — mhkeller
– mhkeller, Commented Apr 25, 2022 at 22:03
array is container type, means contain homogeneous subtype. numeric array can only contain numeric value. int array can only contain integer. Yes, create the same function name with different argument types. — jian
– jian, Commented Apr 26, 2022 at 3:53
I tried searching the source code for width_bucket but it's not defined in PSQL: doxygen.postgresql.org/… So I don't see how I can run something like CREATE FUNCTION width_bucket(APPROPRIATE ARGUMENTS HERE). I understand what you're saying in principle but I don't see how I can implement it. If you have any guidance that would be great. — mhkeller
– mhkeller, Commented Apr 26, 2022 at 15:14

mhkeller · Accepted Answer · 2022-04-26 16:01:14Z

0

The error message is misleading here. The issue is not that width_bucket does not support a numeric array, it's that it does not seem to support a mismatch of types between the operand and the thresholds values.

All of these work and return the same value:

select width_bucket( 5.0, array[0, 1.1, 2, 3, 4, 5, 6, 7, 8] )
select width_bucket( 5::numeric, array[0, 1.1, 2, 3, 4, 5, 6, 7, 8] )

answered Apr 26, 2022 at 16:01

mhkeller

7021 gold badge8 silver badges20 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related