PostgreSQL 9.6.2 문서 | |||
---|---|---|---|

이전 | 위로 | 장 9. Functions and Operators | 다음 |

Mathematical operators are provided for many PostgreSQL types. For types without standard mathematical conventions (e.g., date/time types) we describe the actual behavior in subsequent sections.

표 9-4 shows the available mathematical operators.

**표 9-4. Mathematical Operators**

Operator | Description | Example | Result |
---|---|---|---|

+ | addition | 2 + 3 | 5 |

- | subtraction | 2 - 3 | -1 |

* | multiplication | 2 * 3 | 6 |

/ | division (integer division truncates the result) | 4 / 2 | 2 |

% | modulo (remainder) | 5 % 4 | 1 |

^ | exponentiation (associates left to right) | 2.0 ^ 3.0 | 8 |

|/ | square root | |/ 25.0 | 5 |

||/ | cube root | ||/ 27.0 | 3 |

! | factorial | 5 ! | 120 |

!! | factorial (prefix operator) | !! 5 | 120 |

@ | absolute value | @ -5.0 | 5 |

& | bitwise AND | 91 & 15 | 11 |

| | bitwise OR | 32 | 3 | 35 |

# | bitwise XOR | 17 # 5 | 20 |

~ | bitwise NOT | ~1 | -2 |

<< | bitwise shift left | 1 << 4 | 16 |

>> | bitwise shift right | 8 >> 2 | 2 |

The bitwise operators work only on integral data types, whereas
the others are available for all numeric data types. The bitwise
operators are also available for the bit
string types `bit` and `bit varying`, as
shown in 표 9-13.

표 9-5 shows the available
mathematical functions. In the table, `dp`
indicates `double precision`. Many of these functions
are provided in multiple forms with different argument types.
Except where noted, any given form of a function returns the same
data type as its argument.
The functions working with `double precision` data are mostly
implemented on top of the host system's C library; accuracy and behavior in
boundary cases can therefore vary depending on the host system.

**표 9-5. Mathematical Functions**

Function | Return Type | Description | Example | Result |
---|---|---|---|---|

`abs(` | (same as input) | absolute value | abs(-17.4) | 17.4 |

`cbrt(` | dp | cube root | cbrt(27.0) | 3 |

`ceil(` | (same as input) | nearest integer greater than or equal to argument | ceil(-42.8) | -42 |

`ceiling(` | (same as input) | nearest integer greater than or equal to argument (same as `ceil` ) | ceiling(-95.3) | -95 |

`degrees(` | dp | radians to degrees | degrees(0.5) | 28.6478897565412 |

`div(` | numeric | integer quotient of y/x | div(9,4) | 2 |

`exp(` | (same as input) | exponential | exp(1.0) | 2.71828182845905 |

`floor(` | (same as input) | nearest integer less than or equal to argument | floor(-42.8) | -43 |

`ln(` | (same as input) | natural logarithm | ln(2.0) | 0.693147180559945 |

`log(` | (same as input) | base 10 logarithm | log(100.0) | 2 |

`log(` | numeric | logarithm to base b | log(2.0, 64.0) | 6.0000000000 |

`mod(` | (same as argument types) | remainder of y/x | mod(9,4) | 1 |

`pi()` | dp | "π" constant | pi() | 3.14159265358979 |

`power(` | dp | a raised to the power of b | power(9.0, 3.0) | 729 |

`power(` | numeric | a raised to the power of b | power(9.0, 3.0) | 729 |

`radians(` | dp | degrees to radians | radians(45.0) | 0.785398163397448 |

`round(` | (same as input) | round to nearest integer | round(42.4) | 42 |

`round(` | numeric | round to s decimal places | round(42.4382, 2) | 42.44 |

`scale(` | numeric | scale of the argument (the number of decimal digits in the fractional part) | scale(8.41) | 2 |

`sign(` | (same as input) | sign of the argument (-1, 0, +1) | sign(-8.4) | -1 |

`sqrt(` | (same as input) | square root | sqrt(2.0) | 1.4142135623731 |

`trunc(` | (same as input) | truncate toward zero | trunc(42.8) | 42 |

`trunc(` | numeric | truncate to s decimal places | trunc(42.4382, 2) | 42.43 |

`width_bucket(` | int | return the bucket number to which operand would
be assigned in a histogram having count equal-width
buckets spanning the range b1 to b2;
returns 0 or for
an input outside the rangecount+1 | width_bucket(5.35, 0.024, 10.06, 5) | 3 |

`width_bucket(` | int | return the bucket number to which operand would
be assigned in a histogram having count equal-width
buckets spanning the range b1 to b2;
returns 0 or for
an input outside the rangecount+1 | width_bucket(5.35, 0.024, 10.06, 5) | 3 |

`width_bucket(` | int | return the bucket number to which operand would
be assigned given an array listing the lower bounds of the buckets;
returns 0 for an input less than the first lower bound;
the thresholds array must be sorted,
smallest first, or unexpected results will be obtained | width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[]) | 2 |

표 9-6 shows functions for generating random numbers.

**표 9-6. Random Functions**

Function | Return Type | Description |
---|---|---|

`random()` | dp | random value in the range 0.0 <= x < 1.0 |

`setseed(` | void | set seed for subsequent random() calls (value between -1.0 and
1.0, inclusive) |

The characteristics of the values returned by
` random()` depend
on the system implementation. It is not suitable for cryptographic
applications; see pgcrypto module for an alternative.

Finally, 표 9-7 shows the
available trigonometric functions. All trigonometric functions
take arguments and return values of type `double
precision`. Each of the trigonometric functions comes in
two variants, one that measures angles in radians and one that
measures angles in degrees.

**표 9-7. Trigonometric Functions**

Function (radians) | Function (degrees) | Description |
---|---|---|

`acos(` | `acosd(` | inverse cosine |

`asin(` |
`asind(` | inverse sine |

`atan(` |
`atand(` | inverse tangent |

`atan2(` |
`atan2d(` | inverse tangent of
/yx |

`cos(` |
`cosd(` | cosine |

`cot(` |
`cotd(` | cotangent |

`sin(` |
`sind(` | sine |

`tan(` |
`tand(` | tangent |

참고:Another way to work with angles measured in degrees is to use the unit transformation functionsand`radians()`

shown earlier. However, using the degree-based trigonometric functions is preferred, as that way avoids roundoff error for special cases such as`degrees()`

sind(30).