EnterpriseDB: NUMERIC Data
Oracle Tips by Burleson Consulting
EnterpriseDB, we have three primary numeric data types: integer,
number and floating point. OID is a special numeric type.
NUMBER stores optionally scaled, optionally precise numbers.
That means that as the programmer, you can define the precision and
scale of the number
Precision is the number of important digits in the entire number.
Scale is the number of important digits to the right of the decimal.
example, you can declare a number as:
This would allow any precision of data, precision and scale are
This would allow numbers like 123.45, precision is 5 and scale
This would allow numbers like 123 or 456, precision is 3 and
scale is 0
Same as NUMBER(3,0)
This would allow numbers like .123 or .456, precision and scale
are both 3
really is very little difference between Oracle's NUMBER and
EnterpriseDB's NUMBER. EnterpriseDB's NUMBER does allow for
greater precision but it does not allow a negative scale.
Synonyms for NUMBER are:
integer is a whole number between -2 billion and +2 billion.
An integer maps to an Oracle BINARY_INTEGER and a PLS_INTEGER. In
Oracle, these two data types are only available in PL/SQL and not in
SQL (which means you cannot have them as a column data type).
EnterpriseDB supports two additional integers that do not map to any
Oracle data type. A TINYINT (or SMALLINT) can be between -32k
and +32k. A BIGINT can be huge! A TINYINT is 2 bytes, an
INTEGER is 4 bytes and a BIGINT is 8 bytes. That means it can
store whole numbers between -a gazillion and +a gazillion. Or
something like that anyway.
integer would be used for columnar data like an ID, a primary key,
an age, or other information that you wish to store as a whole
number. In Oracle, most developers would put this kind of data
in a NUMBER field. For compatibility, I would recommend using
a NUMBER in EnterpriseDB also.
said that, an INTEGER is much faster than a NUMBER. If you
have a performance critical piece of code or table that uses whole
numbers, you may want to use INTEGER instead of NUMBER.
Make a note though that you reduce your cross platform compatibility
by doing so.
Synonyms for INTEGER are:
Synonyms for TINYINT are:
Synonyms for BIGINT are:
provides two very efficient floating-point numbers, BINARY_FLOAT and
BINARY_DOUBLE. BINARY_FLOAT is a 32-bit binary number and
BINARY_DOUBLE is a 64-bit floating-point number.
EnterpriseDB, we do not have these data types as of yet. What
we do have are REAL and DOUBLE PRECISION. These data types are
not as precise as Oracle's floating-point implementation and are not
nearly as precise as a NUMBER.
and DOUBLE PRECISION, like Oracle's BINARY_FLOAT and BINARY_DOUBLE,
are much faster than a NUMBER. If you have a performance
critical piece of code or table that uses floating point numbers,
you may want to use one of these instead of NUMBER. The
important thing to remember is that these data types are not as
precise and may use approximations. These are not suitable for
EnterpriseDB and Oracle both offer the FLOAT data type. A
float is declared as FLOAT(n) with n being the number of digits of
precision required or as FLOAT. FLOAT without an (n) is
assumed to mean the maximum allowed. In EnterpriseDB, (n) can
be between (1) and (53). Anything over (53) generates an
error. EnterpriseDB will decide whether to implement the FLOAT
as a REAL
DOUBLE PRECISION based on the value of (n).
maximum compatibility, I would recommend using a FLOAT or FLOAT(n)
for floating point numbers when speed is critical and precision is
not. For all other cases, I would recommend using NUMBER.
Synonyms for FLOAT are:
* DOUBLE PRECISION
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.