Numeric Values

PostgreSQL provides a variety of numeric data types. Of the six numeric types, four are exact (SMALLINT, INTEGER, BIGINT, NUMERIC(p,s)) and two are approximate (REAL, DOUBLE PRECISION).

Three of the four exact numeric types (SMALLINT, INTEGER, and BIGINT) can store only integer values. The fourth (NUMERIC(p,s)) can accurately store any value that fits within the specified number (p) of digits.

The approximate numeric types, on the other hand, cannot store all values exactly. Instead, an approximate data type stores an approximation of a real number. The DOUBLE PRECISION type, for example, can store a total of 15 significant digits, but when you perform calculations using a DOUBLE PRECISION value, you can run into rounding errors. It's easy to see this problem:

movies=# select 2000.3 - 2000.0;
     ?column?
-------------------
 0.299999999999955
(1 row)

Size, Precision, and Range-of-Values

The four exact data types can accurately store any value within a type-specific range. The exact numeric types are described in Table 2.3.

The NUMERIC(p,s) data type can accurately store any number that fits within the specified number of digits. When you create a column of type NUMERIC(p,s), you can specify the total number of decimal digits (p) and the number of fractional digits (s). The total number of decimal digits is called the precision, and the number of fractional digits is called the scale.

Table 2.3 shows that there is no limit to the values that you can store in a NUMERIC(p,s) column. In fact, there is a limit (normally 1,000 digits), but you can adjust the limit by changing a symbol and rebuilding your PostgreSQL server from source code.

The two approximate numeric types are named REAL and DOUBLE PRECISION. Table 2.4 shows the size and range for each of these data types, while Table 2.5 shows alternative names for the data types.

Syntax for Literal Values

When you need to enter a numeric literal, you must follow the formatting rules defined by PostgreSQL. There are two distinct styles for numeric literals: integer and fractional (the PostgreSQL documentation refers to fractional literals as floating-point literals).

Let's start by examining the format for fractional literals. Fractional literals can be entered in any of the following forms^[2]:

^[2] Syntax diagrams are described in detail in Chapter 1.

[-]digits.[digits][E[+|-]digits]
[-][digits].digits[E[+|-]digits]
[-]digits[+|-]digits

Here are some examples of valid fractional literals:

3.14159
2.0e+15
0.2e-15
4e10

A numeric literal that contains only digits is considered to be an integer literal:

[-]digits

Here are some examples of valid integer literals:

-100
55590332
9223372036854775807
-9223372036854775808

A fractional literal is always considered to be of type DOUBLE PRECISION. An integer literal is considered to be of type INTEGER, unless the value is too large to fit into an integer—in which case, it will be promoted first to type BIGINT, then to NUMERIC or REAL if necessary.

Supported Operators

PostgreSQL supports a variety of arithmetic, comparison, and bit-wise operators for the numeric data types. Tables 2.6 and 2.7 give some examples of the arithmetic operators.

You use the comparison operators to determine the relationship between two numeric values. PostgreSQL supports the usual operators: <, <=, <> (not equal), =, >, and >=. You can use the comparison operators with all possible combinations of the numeric data types (some combinations will require type conversion).

PostgreSQL also provides a set of bit-wise operators that you can use with the integer data types. Bit-wise operators work on the individual bits that make up the two operands.

The easiest way to understand the bit-wise operators is to first convert your operands into binary notation-for example:

decimal 12 = binary 00001100
decimal  7 = binary 00000111
decimal 21 = binary 00010101

Next, let's look at each operator in turn.

The AND (&) operator compares corresponding bits in each operand and produces a 1 if both bits are 1 and a 0 otherwise. For example:

00001100 &    00000111 &
00010101      00010101
--------      --------
00000100      00000101

The OR (|) operator compares corresponding bits in each operand and produces a 1 if either (or both) bit is 1 and a 0 otherwise. For example:

00001100 |    00000111 |
00010101      00010101
--------      --------
00011101      00010111

The XOR (#) operator is similar to OR. XOR compares corresponding bits in each operand, and produces a 1 if either bit, but not both bits, is 1, and produces a 0 otherwise.

00001100 #    00000111 #
00010101      00010101
--------      --------
00011001      00010010

PostgreSQL also provides two bit-shift operators.

The left-shift operator (<<) shifts the bits in the first operand n bits to the left, where n is the second operand. The leftmost n bits are discarded, and the rightmost n bits are set to 0. A left-shift by n bits is equivalent to multiplying the first operand by 2ⁿ—for example:

00001100 << 2(decimal) = 00110000
00010101 << 3(decimal) = 10101000

The right-shift operator (>>) >)>shifts the bits in the first operand n bits to the right, where n is the second operand. The rightmost n bits are discarded, and the leftmost n bits are set to 0. A right-shift by n bits is equivalent to dividing the first operand by 2ⁿ:

00001100 >> 2(decimal) = 00000011
00010101 >> 3(decimal) = 00000010

The final bit-wise operator is the binary NOT (~). Unlike the other bit-wise operators, NOT is a unary operator—it takes a single operand. When you apply the NOT operator to a value, each bit in the original value is toggled: ones become zeroes and zeroes become ones. For example:

~00001100 = 11110011
~00010101 = 11101010

Table 2.8 shows the data types that you can use with the bit-wise operators.