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-ValuesThe 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
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. Table 2.4. Approximate Numeric Data Types
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. Table 2.5. Alternate Names for Numeric Data Types
Syntax for Literal ValuesWhen 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] :
[-] 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 . Supported OperatorsPostgreSQL supports a variety of arithmetic, comparison, and bit-wise operators for the numeric data types. Table 2.6. Arithmetic Operators for Integers
Table 2.7. Arithmetic Operators for Floats
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 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 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 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. Table 2.8. Bit-Wise Operators for Integers
|
EAN: 2147483647
Pages: 220