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.

Table 2.3. Exact Numeric Data Types

Type Name	Size in Bytes	Minimum Value	Maximum Value
SMALLINT	2	-32768	+32767
INTEGER	4	-2147483648	[phone]
BIGINT	8	-9223372036854775808	+9223372036854775807
NUMERIC(p,s)	11+(p/2)	No limit	No limit

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.

Table 2.4. Approximate Numeric Data Types

Type Name	Size in Bytes	Range
REAL	4	6 decimal digits
DOUBLE PRECISION	8	15 decimal digits

Table 2.5. Alternate Names for Numeric Data Types

Common Name	Synonyms
SMALLINT	INT2
INTEGER	INT, INT4
BIGINT	INT8
NUMERIC(p,s)	DECIMAL(p,s)
REAL	FLOAT, FLOAT4
DOUBLE PRECISION	FLOAT8

SERIAL, BIGSERIAL, and SEQUENCES

Besides the numeric data types already described, PostgreSQL supports two "advanced" numeric types: SERIAL and BIGSERIAL. A SERIAL column is really an unsigned INTEGER whose value automatically increases (or decreases) by a defined increment as you add new rows. Likewise, a BIGSERIAL is a BIGINT that increases in value. When you create a BIGSERIAL or SERIAL column, PostgreSQL will automatically create a SEQUENCE for you. A SEQUENCE is an object that generates sequence numbers for you. I'll talk more about SEQUENCEs later in this chapter.

 

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 integerin 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.

Table 2.6. Arithmetic Operators for Integers

Data Types	Valid Operators (q)
INT2 q INT2	+ - * / %
INT2 q INT4	+ - * / %
INT4 q INT2	+ - * / %
INT4 q INT4	+ - * / %
INT4 q INT8	+ - * /
INT8 q INT4	+ - * /
INT8 q INT8	+ - * / %

Table 2.7. Arithmetic Operators for Floats

Data Types	Valid Operators (q)
FLOAT4 q FLOAT4	* + - /
FLOAT4 q FLOAT8	* + - /
FLOAT8 q FLOAT4	* + - /
FLOAT8 q FLOAT8	* + - / ^

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 2nfor 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 2n:

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 operatorit 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.

Table 2.8. Bit-Wise Operators for Integers

Data Types	Valid Operators (q)
INT2 q INT2	# & | << >>
INT4 q INT4	# & | << >>
INT8 q INT4	<< >>
INT8 q INT8	# & |