PostgreSQL width_bucket() Function

The PostgreSQL width_bucket() function returns the position of a specified operand in some specified buckets.

`width_bucket()` Syntax

This is the syntax of the PostgreSQL width_bucket() function:

width_bucket(operand, low, high, count) -> integer

width_bucket(operand, thresholds) -> numeric

Parameters

operand: Required. The operand.
low: Required. The minimum bound (inclusive) of all buckets.
high: Required. The maximum bound (exclusive) of all buckets.
count: Required. The number of buckets.
thresholds: Required. All buckets are defined through this array.

Return value

The PostgreSQL width_bucket() function returns the position of the operand operand in some specified bucket.

According to the two syntaxes above, you have two ways to specify buckets:

The range from low to high is divided to count equal-width buckets.
Buckets are defined by adjacent elements in the array thresholds.

If the operand operand does not fall in any bound, and the operand is less than the minimum bound low, the width_bucket() function returns 0.

If the operand operand does not fall in any bound, and the operand is greater than or eqaul to the minimum bound high, the width_bucket() function returns count + 1.

`width_bucket()` Examples

Basic usage

The following example demonstrates the basic usage of the width_bucket() function.

SELECT width_bucket(6, 2, 8, 3);

 width_bucket
--------------
            3

Here, we found that width_bucket(6, 2, 8, 3) returned 3. Its calculation steps are as follows:

First, divide the range [2, 8) into 3 equal-width buckets:
- [2, 4)- 1st bucket
- [4, 6)- 2nd bucket
- [6, 8)- 3rd bucket
Note that the low boundary here 2 is included in the bucket, and the high boundary 8 is not included in the bucket.
Second, Check which bucket 6 falls in. Obviously, 6 falls into the third bucket [6, 8).
Last, width_bucket(6, 2, 8, 3) back 3.

We can use some other values to verify that the above calculation is correct, for example:

SELECT
    width_bucket(3, 2, 8, 3) AS "width_bucket(3, 2, 8, 3)",
    width_bucket(5, 2, 8, 3) AS "width_bucket(5, 2, 8, 3)";

 width_bucket(3, 2, 8, 3) | width_bucket(5, 2, 8, 3)
--------------------------+--------------------------
                        1 |                        2

Here, the operand 3 falls into the first bucket [2, 4), and the operand 5 falls into the second bucket [4, 6).

We can also provide a value that is less than the lower bound, like:

SELECT width_bucket(1, 2, 8, 3) AS "width_bucket(1, 2, 8, 3)";

 width_bucket(1, 2, 8, 3)
--------------------------
                        0

Here, since 1 is less than the lower bound 2, it does not belong to any bucket, so it is width_bucket(1, 2, 8, 3) returns 0.

We can also provide a value greater than the high bound, like:

SELECT
    width_bucket(8, 2, 8, 3) AS "width_bucket(8, 2, 8, 3)",
    width_bucket(9, 2, 8, 3) AS "width_bucket(9, 2, 8, 3)";

 width_bucket(8, 2, 8, 3) | width_bucket(9, 2, 8, 3)
--------------------------+--------------------------
                        4 |                        4

Here, because the operand does not belong to any bucket and is greater than or equal to the high bound 8, it returns 4 (the number of buckets plus 1).

Array bucket

We can also define buckets using an array.

For example, you can define the same buckets as in the above example using the array array[2, 4, 6, 8]::int[]:

[2, 4)- 1st bucket
[4, 6)- 2nd bucket
[6, 8)- 3rd bucket

Let’s rewrite the above example with the array:

SELECT
    width_bucket(6, array[2, 4, 6, 8]::int[]) AS "width_bucket(6, array[2, 4, 6, 8]::int[])",
    width_bucket(3, array[2, 4, 6, 8]::int[]) AS "width_bucket(3, array[2, 4, 6, 8]::int[])",
    width_bucket(5, array[2, 4, 6, 8]::int[]) AS "width_bucket(5, array[2, 4, 6, 8]::int[])",
    width_bucket(1, array[2, 4, 6, 8]::int[]) AS "width_bucket(1, array[2, 4, 6, 8]::int[])",
    width_bucket(8, array[2, 4, 6, 8]::int[]) AS "width_bucket(8, array[2, 4, 6, 8]::int[])",
    width_bucket(9, array[2, 4, 6, 8]::int[]) AS "width_bucket(9, array[2, 4, 6, 8]::int[])";

-[ RECORD 1 ]-----------------------------+--
width_bucket(6, array[2, 4, 6, 8]::int[]) | 3
width_bucket(3, array[2, 4, 6, 8]::int[]) | 1
width_bucket(5, array[2, 4, 6, 8]::int[]) | 2
width_bucket(1, array[2, 4, 6, 8]::int[]) | 0
width_bucket(8, array[2, 4, 6, 8]::int[]) | 4
width_bucket(9, array[2, 4, 6, 8]::int[]) | 4

The result is the same as before.

width_bucket() Syntax