Mathematical functions

• 1: ABS
• 2: ACOS
• 3: ACOSH
• 4: ASIN
• 5: ASINH
• 6: ATAN
• 7: ATAN2
• 8: ATANH
• 9: CBRT
• 10: CEILING
• 11: COS
• 12: COSH
• 13: COT
• 14: DEGREES
• 15: DISTANCE
• 16: DISTANCEV
• 17: EXP
• 18: FLOOR
• 19: HASH
• 20: LN
• 21: LOG
• 22: LOG10
• 23: MOD
• 24: PI
• 25: POWER
• 26: RADIANS
• 27: RANDOM
• 28: RANDOMINT
• 29: RANDOMINT_CRYPTO
• 30: ROUND
• 31: SIGN
• 32: SIN
• 33: SINH
• 34: SQRT
• 35: TAN
• 36: TANH
• 37: TRUNC
• 38: WIDTH_BUCKET

1 - ABS

Returns the absolute value of the argument. The return value has the same data type as the argument..

Behavior type

Syntax

ABS ( expression )

Arguments

expression

Resolves to a value of type INTEGER or DOUBLE PRECISION.

Examples

SELECT ABS(-28.7);
 abs
------
 28.7
(1 row)

2 - ACOS

Returns a DOUBLE PRECISION value representing the trigonometric inverse cosine of the argument.

Behavior type

Syntax

ACOS ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

SELECT ACOS (1);
 acos
------
    0
(1 row)

3 - ACOSH

Returns a DOUBLE PRECISION value that represents the inverse (arc) hyperbolic cosine of the function argument.

Behavior type

Syntax

ACOSH ( expression )

Arguments

expression

Resolves to a value of type INTEGER or DOUBLE PRECISION ≥ 1.0, otherwise returns NaN.

Examples

=> SELECT acosh(4);
      acosh
------------------
 2.06343706889556
(1 row)

4 - ASIN

Returns a DOUBLE PRECISION value representing the trigonometric inverse sine of the argument.

Behavior type

Syntax

ASIN ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

SELECT ASIN(1);
      asin
-----------------
 1.5707963267949
(1 row)

5 - ASINH

Returns a DOUBLE PRECISION value that represents the inverse (arc) hyperbolic sine of the function argument.

Behavior type

Syntax

ASINH ( expression )

Arguments

expression

Resolves to a value of type INTEGER or DOUBLE PRECISION.

Examples

=> SELECT asinh(2.85);
      asinh
------------------
 1.76991385902105
(1 row)

6 - ATAN

Returns a DOUBLE PRECISION value representing the trigonometric inverse tangent of the argument.

Behavior type

Syntax

ATAN ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

SELECT ATAN(1);
       atan
-------------------
 0.785398163397448
(1 row)

7 - ATAN2

Returns a DOUBLE PRECISION value representing the trigonometric inverse tangent of the arithmetic dividend of the arguments.

Behavior type

Syntax

ATAN2 ( quotient, divisor )

Arguments

quotient

Resolves to a value of type DOUBLE PRECISION representing the quotient.

divisor

Resolves to a value of type DOUBLE PRECISION representing the divisor.

Examples

SELECT ATAN2(2,1);
      ATAN2
------------------
 1.10714871779409
(1 row)

8 - ATANH

Returns a DOUBLE PRECISION value that represents the inverse hyperbolic tangent of the function argument.

Behavior type

Syntax

ATANH ( expression )

Arguments

expression

Resolves to a value of type INTEGER or DOUBLE PRECISION between -1.0 and +1.0, inclusive, otherwise returns NaN.

Examples

=> SELECT atanh(-0.875);
       atanh
-------------------
 -1.35402510055111
(1 row)

9 - CBRT

Returns the cube root of the argument. The return value has the type DOUBLE PRECISION.

Behavior type

Syntax

CBRT ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

SELECT CBRT(27.0);
 cbrt
------
    3
(1 row)

10 - CEILING

Rounds up the returned value up to the next whole number. For example, given arguments of 5.01 and 5.99, CEILING returns 6. CEILING is the opposite of FLOOR, which rounds down the returned value.

Behavior type

Syntax

CEIL[ING] ( expression )

Arguments

expression

Resolves to an INTEGER or DOUBLE PRECISION value.

Examples

=> SELECT CEIL(-42.8);
 CEIL
------
  -42
(1 row)
SELECT CEIL(48.01);
 CEIL
------
   49
(1 row)

11 - COS

Returns a DOUBLE PRECISION value tat represents the trigonometric cosine of the passed parameter.

Behavior type

Syntax

COS ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

SELECT COS(-1);
       COS
------------------
 0.54030230586814
(1 row)

12 - COSH

Returns a DOUBLE PRECISION value that represents the hyperbolic cosine of the passed parameter.

Behavior type

Syntax

COSH ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

=> SELECT COSH(-1);
       COSH
------------------
 1.54308063481524

13 - COT

Returns a DOUBLE PRECISION value representing the trigonometric cotangent of the argument.

Behavior type

Syntax

COT ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

SELECT COT(1);
        cot
-------------------
 0.642092615934331
(1 row)

14 - DEGREES

Converts an expression from radians to fractional degrees, or from degrees, minutes, and seconds to fractional degrees. The return value has the type DOUBLE PRECISION.

Behavior type

Syntax

DEGREES ( { radians | degrees, minutes, seconds } )

Arguments

radians

Unit of angular measure. 2*π* radians is equal to a full rotation.

degrees

Unit of angular measure, equal to 1/360 of a full rotation.

minutes

Unit of angular measurement, representing 1/60 of a degree.

seconds

Unit of angular measurement, representing 1/60 of a minute.

Examples

SELECT DEGREES(0.5);
     DEGREES
------------------
 28.6478897565412
(1 row)

SELECT DEGREES(1,2,3);
     DEGREES
------------------
 1.03416666666667
(1 row)

15 - DISTANCE

Returns the distance (in kilometers) between two points. You specify the latitude and longitude of the starting point and the ending point. You can also specify the radius of curvature for greater accuracy when using an ellipsoidal model.

Behavior type

Syntax

DISTANCE ( lat0, lon0, lat1, lon1 [, radius-of-curvature ] )

Arguments

lat0

Starting point latitude.

lon0

Starting point longitude.

lat1

Ending point latitude

lon1

Ending point longitude.

radius-of-curvature

Radius of the earth's curvature at the midpoint between the starting and ending points. This argument allows for greater accuracy when using an ellipsoidal earth model. If you omit this argument, DISTANCE uses the WGS-84 average r1 radius, about 6371.009 km.

Examples

This example finds the distance in kilometers for 1 degree of longitude at latitude 45 degrees, assuming earth is spherical.

SELECT DISTANCE(45,0,45,1);
      DISTANCE
----------------------
 78.6262959272162
(1 row)

16 - DISTANCEV

Returns the distance (in kilometers) between two points using the Vincenty formula. Because the Vincenty formula includes the parameters of the WGS-84 ellipsoid model, you need not specify a radius of curvature. You specify the latitude and longitude of both the starting point and the ending point. This function is more accurate, but will be slower, than the DISTANCE function.

Behavior type

Syntax

DISTANCEV (lat0, lon0, lat1, lon1);

Arguments

lat0

Specifies the latitude of the starting point.

lon0

Specifies the longitude of the starting point.

lat1

Specifies the latitude of the ending point.

lon1

Specifies the longitude of the ending point.

Examples

This example finds the distance in kilometers for 1 degree of longitude at latitude 45 degrees, assuming earth is ellipsoidal.

SELECT DISTANCEV(45,0, 45,1);
    distanceV
------------------
 78.8463347095916
(1 row)

17 - EXP

Returns the exponential function, e to the power of a number. The return value has the same data type as the argument.

Behavior type

Syntax

EXP ( exponent )

Arguments

exponent

Resolves to a value of type INTEGER or DOUBLE PRECISION.

Examples

SELECT EXP(1.0);
       exp
------------------
 2.71828182845905
(1 row)

18 - FLOOR

Rounds down the returned value to the previous whole number. For example, given arguments of 5.01 and 5.99, FLOOR returns 5. FLOOR is the opposite of CEILING, which rounds up the returned value.

Behavior type

Syntax

FLOOR ( expression )

Arguments

expression

Resolves to an INTEGER or DOUBLE PRECISION value.

Examples

=> SELECT FLOOR((TIMESTAMP '2005-01-17 10:00' - TIMESTAMP '2005-01-01') / INTERVAL '7');
 FLOOR
-------
     2
(1 row)

=> SELECT FLOOR(-42.8);
 FLOOR
-------
   -43
(1 row)

=> SELECT FLOOR(42.8);
 FLOOR
-------
    42
(1 row)

Although the following example looks like an INTEGER, the number on the left is 2^49 as an INTEGER, while the number on the right is a FLOAT:

=> SELECT 1<<49, FLOOR(1 << 49);
    ?column?     |      floor
-----------------+-----------------
 562949953421312 | 562949953421312
(1 row)

Compare the previous example to:

=> SELECT 1<<50, FLOOR(1 << 50);
     ?column?     |        floor
------------------+----------------------
 1125899906842624 | 1.12589990684262e+15
(1 row)

19 - HASH

Calculates a hash value over the function arguments, producing a value in the range 0 <= x < 263.

The HASH function is typically used to segment a projection over a set of cluster nodes. The function selects a specific node for each row based on the values of the row columns. The HASH function distributes data evenly across the cluster, which facilitates optimal query execution.

Behavior type

Syntax

HASH ( { * | expression[,...] } )

Arguments

* | expression[,...]

One of the following:

• * (asterisk)

  Specifies to hash all columns in the queried table.

• expression

  An expression of any data type. Functions that are included in expression must be deterministic. If specified in a projection's hash segmentation clause, each expression typically resolves to a column reference.

Examples

=> SELECT HASH(product_price, product_cost) FROM product_dimension
     WHERE product_price = '11';
        hash
---------------------
 4157497907121511878
 1799398249227328285
 3250220637492749639
(3 rows)

See also

20 - LN

Returns the natural logarithm of the argument. The return data type is the same as the argument.

Behavior type

Syntax

LN ( expression )

Arguments

expression

Resolves to a value of type INTEGER or DOUBLE PRECISION.

Examples

SELECT LN(2);
        ln
-------------------
 0.693147180559945
(1 row)

21 - LOG

Returns the logarithm to the specified base of the argument. The data type of the return value is the same data type as the passed parameter.

Behavior type

Syntax

LOG ( [ base, ] expression )

Arguments

base

Specifies the base (default is base 10)

expression

Resolves to a value of type INTEGER or DOUBLE PRECISION.

Examples

=> SELECT LOG(2.0, 64);
 LOG
-----
   6
(1 row)
SELECT LOG(100);
 LOG
-----
   2
(1 row)

22 - LOG10

Returns the base 10 logarithm of the argument, also known as the common logarithm. The data type of the return value is the same as the data type of the passed parameter.

Behavior type

Syntax

LOG10 ( expression )

Arguments

expression

Resolves to a value of type INTEGER or DOUBLE PRECISION.

Examples

=> SELECT LOG10(30);
       LOG10
------------------
 1.47712125471966
(1 row)

23 - MOD

Returns the remainder of a division operation.

Behavior type

Syntax

MOD( expression1, expression2 )

Arguments

expression1

Resolves to a numeric data type that specifies the dividend.

expression2

Resolves to a numeric data type that specifies the divisor.

Computation rules

When computing MOD(expression1, expression2), the following rules apply:

• If either expression1 or expression2 is the null value, then the result is the null value.

• If expression2 is zero, then an exception condition is raised: data exception — division by zero.

• Otherwise, the result is the unique exact numeric value R with scale 0 (zero) such that all of the following are true:

  • R has the same sign as expression2.

  • The absolute value of R is less than the absolute value of expression1.

  • expression2 = expression1 * K + R for some exact numeric value K with scale 0 (zero).

Examples

SELECT MOD(9,4);
 mod
-----
   1
(1 row)

SELECT MOD(10,3);
 mod
-----
   1
(1 row)

SELECT MOD(-10,3);
 mod
-----
  -1
(1 row)

SELECT MOD(-10,-3);
 mod
-----
  -1
(1 row)

SELECT MOD(10,-3);
 mod
-----
   1
(1 row)

=> SELECT MOD(6.2,0);
ERROR 3117:  Division by zero

24 - PI

Returns the constant pi (P), the ratio of any circle's circumference to its diameter in Euclidean geometry The return type is DOUBLE PRECISION.

Behavior type

Syntax

PI()

Examples

SELECT PI();
        pi
------------------
 3.14159265358979
(1 row)

25 - POWER

Returns a DOUBLE PRECISION value representing one number raised to the power of another number.

Behavior type

Syntax

POW[ER] ( expression1, expression2 )

Arguments

expression1

Resolves to a DOUBLE PRECISION value that represents the base.

expression2

Resolves to a DOUBLE PRECISION value that represents the exponent.

Examples

SELECT POWER(9.0, 3.0);
 power
-------
   729
(1 row)

26 - RADIANS

Returns a DOUBLE PRECISION value representing an angle expressed in radians. You can express the input angle in DEGREES, and optionally include minutes and seconds.

Behavior type

Syntax

RADIANS (degrees [, minutes, seconds])

Arguments

degrees

Unit of angular measurement, representing 1/360 of a full rotation.

minutes

Unit of angular measurement, representing 1/60 of a degree.

seconds

Unit of angular measurement, representing 1/60 of a minute.

Examples

SELECT RADIANS(45);
      RADIANS
-------------------
 0.785398163397448
(1 row)

SELECT RADIANS (1,2,3);
      RADIANS
-------------------
 0.018049613347708
(1 row)

27 - RANDOM

Returns a uniformly-distributed random DOUBLE PRECISION value x, where 0 <= x < 1.

Typical pseudo-random generators accept a seed, which is set to generate a reproducible pseudo-random sequence. Vertica, however, distributes SQL processing over a cluster of nodes, where each node generates its own independent random sequence.

Results depending on RANDOM are not reproducible because the work might be divided differently across nodes. Therefore, Vertica automatically generates truly random seeds for each node each time a request is executed and does not provide a mechanism for forcing a specific seed.

Behavior type

Syntax

RANDOM()

Examples

In the following example, RANDOM returns a float ≥ 0 and < 1.0:

SELECT RANDOM();
      random
-------------------
 0.211625560652465
(1 row)

28 - RANDOMINT

Accepts and returns an integer between 0 and the integer argument expression-1.

Typical pseudo-random generators accept a seed, which is set to generate a reproducible pseudo-random sequence. Vertica, however, distributes SQL processing over a cluster of nodes, where each node generates its own independent random sequence.

Results depending on RANDOM are not reproducible because the work might be divided differently across nodes. Therefore, Vertica automatically generates truly random seeds for each node each time a request is executed and does not provide a mechanism for forcing a specific seed.

Behavior type

Syntax

RANDOMINT ( expression )

Arguments

expression

Resolves to a positive INTEGER between 1 and 2⁶³ − 1, inclusive. If you supply a negative value or expression > 1, Vertica returns an error.

Examples

In the following example, the result is an INTEGER ≥ 0 and < expression, randomly chosen from the set {0,1,2,3,4}.

=> SELECT RANDOMINT(5);
RANDOMINT
----------
        3
(1 row)

29 - RANDOMINT_CRYPTO

Accepts and returns an INTEGER value from a set of values between 0 and the specified function argument -1. For this cryptographic random number generator, Vertica uses RAND_bytes to provide the random value.

Behavior type

Syntax

RANDOMINT_CRYPTO ( expression )

Arguments

expression

Resolves to a positive integer between 1 and 2⁶³ − 1, inclusive.

Examples

In the following example, RANDOMINT_CRYPTO returns an INTEGER >= 0 and less than the specified argument 5, randomly chosen from the set {0,1,2,3,4}.

=> SELECT RANDOMINT_crypto(5);
RANDOMINT_crypto
----------------
               3
(1 row)

30 - ROUND

Rounds a value to a specified number of decimal places, retaining the original precision and scale. Fractions greater than or equal to .5 are rounded up. Fractions less than .5 are rounded down (truncated).

Behavior type

Syntax

ROUND ( expression [, places ] )

Arguments

expression

Resolves to a value of type NUMERIC or DOUBLE PRECISION (FLOAT).

places

An INTEGER value. When places is a positive integer, Vertica rounds the value to the right of the decimal point using the specified number of places. When places is a negative integer, Vertica rounds the value on the left side of the decimal point using the specified number of places.

Notes

Using ROUND with a NUMERIC datatype returns NUMERIC, retaining the original precision and scale.

=> SELECT ROUND(3.5);
 ROUND
-------
   4.0
(1 row)

Examples

=> SELECT ROUND(2.0, 1.0) FROM dual;
 ROUND
-------
   2.0
(1 row)
=> SELECT ROUND(12.345, 2.0);
 ROUND
--------
 12.350
(1 row)
=> SELECT ROUND(3.444444444444444);
       ROUND
-------------------
 3.000000000000000
(1 row)
=> SELECT ROUND(3.14159, 3);
  ROUND
---------
 3.14200
(1 row)
=> SELECT ROUND(1234567, -3);
  ROUND
---------
 1235000
(1 row)
=> SELECT ROUND(3.4999, -1);
 ROUND
--------
 0.0000
(1 row)

The following example creates a table with two columns, adds one row of values, and shows sample rounding to the left and right of a decimal point.

=> CREATE TABLE sampleround (roundcol1 NUMERIC, roundcol2 NUMERIC);
CREATE TABLE

=> INSERT INTO sampleround VALUES (1234567, .1234567);
 OUTPUT
--------
      1
(1 row)

=> SELECT ROUND(roundcol1,-3) AS pn3, ROUND(roundcol1,-4) AS pn4, ROUND(roundcol1,-5) AS pn5 FROM sampleround;

           pn3           |           pn4           |           pn5
-------------------------+-------------------------+-------------------------
 1235000.000000000000000 | 1230000.000000000000000 | 1200000.000000000000000
(1 row)

=> SELECT ROUND(roundcol2,3) AS p3, ROUND(roundcol2,4) AS p4, ROUND(roundcol2,5) AS p5 FROM sampleround;

        p3         |        p4         |        p5
-------------------+-------------------+-------------------
 0.123000000000000 | 0.123500000000000 | 0.123460000000000
(1 row)

31 - SIGN

Returns a DOUBLE PRECISION value of -1, 0, or 1 representing the arithmetic sign of the argument.

Behavior type

Syntax

SIGN ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

SELECT SIGN(-8.4);
 sign
------
   -1
(1 row)

32 - SIN

Returns a DOUBLE PRECISION value that represents the trigonometric sine of the passed parameter.

Behavior type

Syntax

SIN ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

SELECT SIN(30 * 2 * 3.14159 / 360);
        SIN
-------------------
 0.499999616987256
(1 row)

33 - SINH

Returns a DOUBLE PRECISION value that represents the hyperbolic sine of the passed parameter.

Behavior type

Syntax

SINH ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

=> SELECT SINH(30 * 2 * 3.14159 / 360);
        SINH
-------------------
 0.547852969600632

34 - SQRT

Returns a DOUBLE PRECISION value representing the arithmetic square root of the argument.

Behavior type

Syntax

SQRT ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

SELECT SQRT(2);
      sqrt
-----------------
 1.4142135623731
(1 row)

35 - TAN

Returns a DOUBLE PRECISION value that represents the trigonometric tangent of the passed parameter.

Behavior type

Syntax

TAN ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

=> SELECT TAN(30);
        TAN
-------------------
 -6.40533119664628
(1 row)

36 - TANH

Returns a DOUBLE PRECISION value that represents the hyperbolic tangent of the passed parameter.

Behavior type

Syntax

TANH ( expression )

Arguments

expression

Resolves to a value of type DOUBLE PRECISION.

Examples

=> SELECT TANH(-1);
        TANH
-------------------
  -0.761594155955765

37 - TRUNC

Returns the expression value fully truncated (toward zero). Supplying a places argument truncates the expression to the number of decimal places you indicate.

Behavior type

Syntax

TRUNC ( expression [, places ] )

Arguments

expression

Resolves to a value of type NUMERIC or DOUBLE PRECISION (FLOAT).

places

INTEGER value:

• Positive: Vertica truncates the value to the right of the decimal point.
• Negative: Vertica truncates the value on the left side of the decimal point.

Notes

Using TRUNC with a NUMERIC datatype returns NUMERIC, retaining the original precision and scale.

=> SELECT TRUNC(3.5);
 TRUNC
-------
   3.0
(1 row)

Examples

=> SELECT TRUNC(42.8);
 TRUNC
-------
  42.0
(1 row)
=> SELECT TRUNC(42.4382, 2);
  TRUNC
---------
 42.4300
(1 row)

The following example creates a table with two columns, adds one row of values, and shows sample truncating to the left and right of a decimal point.

=> CREATE TABLE sampletrunc (truncol1 NUMERIC, truncol2 NUMERIC);
CREATE TABLE

=> INSERT INTO sampletrunc VALUES (1234567, .1234567);
 OUTPUT
--------
      1
(1 row)

=> SELECT TRUNC(truncol1,-3) AS p3, TRUNC(truncol1,-4) AS p4, TRUNC(truncol1,-5) AS p5 FROM sampletrunc;

           p3            |           p4            |           p5
-------------------------+-------------------------+-------------------------
 1234000.000000000000000 | 1230000.000000000000000 | 1200000.000000000000000
(1 row)

=> SELECT TRUNC(truncol2,3) AS p3, TRUNC(truncol2,4) AS p4, TRUNC(truncol2,5) AS p5 FROM sampletrunc;

        p3         |        p4         |        p5
-------------------+-------------------+-------------------
 0.123000000000000 | 0.123400000000000 | 0.123450000000000
(1 row)

38 - WIDTH_BUCKET

Constructs equiwidth histograms, in which the histogram range is divided into intervals (buckets) of identical sizes. In addition, values below the low bucket return 0, and values above the high bucket return bucket-count +1. Returns an integer value.

Behavior type

Syntax

WIDTH_BUCKET ( expression, hist-min, hist-max, bucket-count )

Arguments

expression

The expression for which the histogram is created. This expression must resolve to a numeric or datetime value or a value that can be implicitly converted to a numeric or datetime value. If *expression *evaluates to null, then the *expression *returns null.

hist-min

Resolves to the low boundary of bucket-count, a non-null numeric or datetime value.

hist-max

Resolves to the high boundary of bucket-count, a non-null numeric or datetime value.

bucket-count

Resolves to an INTEGER constant that indicates the number of buckets.

Notes

• WIDTH_BUCKET divides a data set into buckets of equal width. For example, Age = 0–20, 20–40, 40–60, 60–80. This is known as an equiwidth histogram.

• When using WIDTH_BUCKET pay attention to the minimum and maximum boundary values. Each bucket contains values equal to or greater than the base value of that bucket, so that age ranges of 0–20, 20–40, and so on, are actually 0–19.99 and 20–39.999.

• WIDTH_BUCKET accepts the following data types: (FLOAT and/or INTEGER), (TIMESTAMP and/or DATE and/or TIMESTAMPTZ), or (INTERVAL and/or TIME).

Examples

The following example returns five possible values and has three buckets: 0 [Up to 100), 1 [100–300), 2 [300–500), 3 [500–700), and 4 [700 and up):

SELECT product_description, product_cost, WIDTH_BUCKET(product_cost, 100, 700, 3);

The following example creates a nine-bucket histogram on the annual_income column for customers in Connecticut who are female doctors. The results return the bucket number to an Income column, divided into eleven buckets, including an underflow and an overflow. Note that if customers had annual incomes greater than the maximum value, they would be assigned to an overflow bucket, 10:

SELECT customer_name, annual_income, WIDTH_BUCKET (annual_income, 100000, 1000000, 9) AS "Income"
FROM public.customer_dimension WHERE customer_state='CT'
AND title='Dr.' AND customer_gender='Female' AND household_id < '1000'
ORDER BY "Income";

In the following result set, the reason there is a bucket 0 is because buckets are numbered from 1 to bucket_count. Anything less than the given value of hist_min goes in bucket 0, and anything greater than the given value of hist_max goes in the bucket bucket_count+1. In this example, bucket 9 is empty, and there is no overflow. The value 12,283 is less than 100,000, so it goes into the underflow bucket.

   customer_name    | annual_income | Income
--------------------+---------------+--------
 Joanna A. Nguyen   |         12283 |      0
 Amy I. Nguyen      |        109806 |      1
 Juanita L. Taylor  |        219002 |      2
 Carla E. Brown     |        240872 |      2
 Kim U. Overstreet  |        284011 |      2
 Tiffany N. Reyes   |        323213 |      3
 Rebecca V. Martin  |        324493 |      3
 Betty . Roy        |        476055 |      4
 Midori B. Young    |        462587 |      4
 Martha T. Brown    |        687810 |      6
 Julie D. Miller    |        616509 |      6
 Julie Y. Nielson   |        894910 |      8
 Sarah B. Weaver    |        896260 |      8
 Jessica C. Nielson |        861066 |      8
(14 rows)

See also

• NTILE [analytic]