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)
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.
Type Name |
Size in Bytes |
Minimum Value |
Maximum Value |
---|---|---|---|
SMALLINT |
2 |
?32768 |
+32767 |
INTEGER |
4 |
?2147483648 |
+2147483647 |
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.
Type Name |
Size in Bytes |
Range |
---|---|---|
REAL |
4 |
6 decimal digits |
DOUBLE PRECISION |
8 |
15 decimal digits |
The numeric data types are also known by other names. For example, INT2 is synonymous with SMALLINT. Alternate names for the numeric data types are shown in Table 2.5.
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 SequencesBesides 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. |
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 to type NUMERIC or REAL.
PostgreSQL supports a variety of arithmetic, comparison, and bit-wise operators for the numeric data types.
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 |
+ - * / % |
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 2^{n}?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^{n}:
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.
Data Types |
Valid Operators (q) |
---|---|
INT2 q INT2 |
# & | << >> |
INT4 q INT4 |
# & | << >> |
INT8 q INT4 |
<< >> |
INT8 q INT8 |
# & | |