Tipos Numéricos

Los tipos numéricos consisten en enteros de 2, 4 u 8 bytes y flotantes de 4 u 8, y un número de presición decimal a elección.  Table 8-2 lists the available types.

Tabla 8-2. Tipos Numéricos

NombreTamaño de almacenamientodescripciónRango
smallint2 bytessmall-range integer-32768 to +32767
integer4 bytestypical choice for integer-2147483648 to +2147483647
bigint8 byteslarge-range integer-9223372036854775808 to 9223372036854775807
decimalvariableuser-specified precision, exactno limit
numericvariableuser-specified precision, exactno limit
real4 bytesvariable-precision, inexact6 decimal digits precision
double precision8 bytesvariable-precision, inexact15 decimal digits precision
serial4 bytesautoincrementing integer1 to 2147483647
bigserial8 byteslarge autoincrementing integer1 to 9223372036854775807

La sintaxis de las constantes de los tipos numéricos es descripta en  Section 4.1.2. Los tipos numéricos poseen un conjunto completo de operadores aritméticos y funciones  Chapter 9 . Las secciones siguientes describen los tipos en detalle:

Tipos enteros

Los tipos smallint, integer y bigint almacenan todos los números, esto es, números sin los components fraccionales, de varios rangos. Querer almacenar valores exediendo el rango, resultará en error.

EL tipo integer es el más común, ofreciendo el mejor balance entre rando, tamaño y performance. El tipo smallint es utilizado solo cuando el espcio de disco apremia. El bigint solo debe ser usado cuando el rango de los anteriores es insuficiente, degradándose algo de performance.

En instalaciones mínimas de sistemas operativos, el tipo bigint puede no funcionar correctamnete, poruqe requiere una compilación para enteros de 8 bytes. En esas máquinas, bigint trabaja como entero pero sigue ocupando 8 bytes de almacenamiento. (No nos preocuparemos de las nuevas plataformas en este caso.)

El estándar SQL solo especifica tipos enteros int o integer, smallint y bigint. Existen extensiones utilizadas por otros sistemas de bases de datos llamados int2, int4, y int8.

Números de Presición Arbitraria

El tipo numeric puede almacenar numeros de hasta 1000 dígitos de presición y realizar cálculos exactamente. Es recomendable para almacenar tipos monetarios y otras cantidades que requieran exactitud. Como sea, la artimética de los cálculos en estos tipos es muy lenta comparada a los enteros o a los de punto flotante, descriptas en la sección anterior.

Usaremos los siguientes térimnos: La escala de un tipo numeric es el conteo de los dígitos decimales de la parte fraccional, a la derecha del divisor de decimales. La presición es la cuenta total de todos los dígitos significantes de todo el númerom esto es, el número de dígitos a ambos lados del divisor decimal. Por lo que el número 23.5141 tiene una presición de 6 y una escalca de 4. Los enteros se consideran con una escala de cero.

El máximo de presición y de escala puede ser configurado. Para declarar una columna de tipo numérico, utilice la siguiente sintaxis:

NUMERIC(''`precision`'', ''`scale`'')

La presición debe ser positiva, la escala debe ser 0 o mayor. Alternativa:

NUMERIC(''`precision`'')

Por defecto la escala es de 0. Especificando:

NUMERIC

sin ninguna presición o escala, se creará una columna en la cual los valores podrán almacenarse en cualquier presición y escala, hasta el límite de presición. Una columna de este tipo no coaccionará a los valores de entrada a cualquier escala particular, mientras que columnas numéricas con una escala de la voluntad declarada podrá coaccionar a los valores de entrada a dicha escala. (El estándar SQL requiere una escala de 0 por defecto, es decir, la coacción a un entero de precisión. Nos parece un poco inútil. Si usted está preocupado acerca de la portabilidad, siempre especificar la precisión y la escala de forma explícita.)

Si la escala de un valor que será almacenada es mayor que la declarada en la columna, el sistema no redondeará el valor al específico número de digitos fraccionales. Luegom si el número de dígitos a la izquierda del punto divisor, exede la presición declarada, un error será mostrado.

Los valores numéricos serán físicamente almacenados sin ceros a la izquierda ni derecha.

Numeric values are physically stored without any extra leading or trailing zeroes. Así pues, la precisión y la escala declaradas son máximos, y no asignaciones fijas. (En este sentido, el «tipo» numérico es más parecido a n varchar () que a un char (n).) El requisito real de almacenamiento es de dos bytes para cada grupo de cuatro dígitos decimales, sumándo de 5 a 8 bytes adicionales.

In addition to ordinary numeric values, the numeric type allows the special value NaN, meaning "not-a-number". Any operation on NaN yields another NaN. When writing this value as a constant in an SQL command, you must put quotes around it, for example UPDATE table SET x = 'NaN'. On input, the string NaN is recognized in a case-insensitive manner.

Note: In most implementations of the "not-a-number" concept, NaN is not considered equal to any other numeric value (including NaN). In order to allow numeric values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.

The types decimal and numeric are equivalent. Both types are part of theSQLstandard.

Floating-Point Types

The data types real and double precision are inexact, variable-precision numeric types. In practice, these types are usually implementations ofIEEEStandard 754 for Binary Floating-Point Arithmetic (single and double precision, respectively), to the extent that the underlying processor, operating system, and compiler support it.

Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies. Managing these errors and how they propagate through calculations is the subject of an entire branch of mathematics and computer science and will not be discussed here, except for the following points:

  • If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.
  • If you want to do complicated calculations with these types for anything important, especially if you rely on certain behavior in boundary cases (infinity, underflow), you should evaluate the implementation carefully.
  • Comparing two floating-point values for equality might not always work as expected.

On most platforms, the real type has a range of at least 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits. Values that are too large or too small will cause an error. Rounding might take place if the precision of an input number is too high. Numbers too close to zero that are not representable as distinct from zero will cause an underflow error.

In addition to ordinary numeric values, the floating-point types have several special values:

Infinity
-Infinity
NaN

These represent the IEEE 754 special values "infinity", "negative infinity", and "not-a-number", respectively. (On a machine whose floating-point arithmetic does not follow IEEE 754, these values will probably not work as expected.) When writing these values as constants in an SQL command, you must put quotes around them, for example UPDATE table SET x = 'Infinity'. On input, these strings are recognized in a case-insensitive manner. Note: IEEE754 specifies that NaN should not compare equal to any other floating-point value (including NaN). In order to allow floating-point values to be sorted and used in tree-based indexes, PostgreSQL treats NaN values as equal, and greater than all non-NaN values.

PostgreSQL also supports the SQL-standard notations float and float(p) for specifying inexact numeric types. Here, p specifies the minimum acceptable precision in binary digits. PostgreSQL accepts float(1) to float(24) as selecting the real type, while float(25) to float(53) select double precision. Values of p outside the allowed range draw an error. float with no precision specified is taken to mean double precision.

Note: Prior to PostgreSQL 7.4, the precision in float(p) was taken to mean so many decimal digits. This has been corrected to match the SQL standard, which specifies that the precision is measured in binary digits. The assumption that real and double precision have exactly 24 and 53 bits in the mantissa respectively is correct for IEEE-standard floating point implementations. On non-IEEE platforms it might be off a little, but for simplicity the same ranges of p are used on all platforms.

Serial Types

The data types serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE ''`tablename`'' (
    ''`colname`'' SERIAL
);

is equivalent to specifying:

CREATE SEQUENCE ''`tablename`''_''`colname`''_seq;
CREATE TABLE ''`tablename`'' (
    ''`colname`'' integer NOT NULL DEFAULT nextval('''`tablename`''_''`colname`''_seq')
);
ALTER SEQUENCE ''`tablename`''_''`colname`''_seq OWNED BY ''`tablename`''.''`colname`'';

Thus, we have created an integer column and arranged for its default values to be assigned from a sequence generator. A NOT NULL constraint is applied to ensure that a null value cannot be inserted. (In most cases you would also want to attach a UNIQUE or PRIMARY KEY constraint to prevent duplicate values from being inserted by accident, but this is not automatic.) Lastly, the sequence is marked as "owned by" the column, so that it will be dropped if the column or table is dropped.

Note: Prior to PostgreSQL 7.3, serial implied UNIQUE. This is no longer automatic. If you wish a serial column to have a unique constraint or be a primary key, it must now be specified, just like any other data type.

To insert the next value of the sequence into the serial column, specify that the serial column should be assigned its default value. This can be done either by excluding the column from the list of columns in the INSERT statement, or through the use of the DEFAULT key word.

The type names serial and serial4 are equivalent: both create integer columns. The type names bigserial and serial8 work the same way, except that they create a bigint column. bigserial should be used if you anticipate the use of more than 231 identifiers over the lifetime of the table.

The sequence created for a serial column is automatically dropped when the owning column is dropped. You can drop the sequence without dropping the column, but this will force removal of the column default expression.