Tipos de datos en PostgreSQL

1. Qué es

Cada columna tiene un tipo, y el tipo decide tres cosas: qué valores caben, qué operaciones son legales y cómo se comporta la aritmética. La tercera es la que produce errores caros.

Este tema no es una lista para memorizar. Son cuatro decisiones que se toman una vez por columna y se pagan durante años:

1. Dinero → numeric. Nunca real ni float.
2. Texto → text o varchar, nunca char(n).
3. Fecha → date si no hay hora; timestamptz si la hay.
4. Sí/no → boolean.

El resto del tema es por qué.

2. Los tipos que vas a usar

2.1 Numéricos

Nota: float a secas = double precision, no real.

2.2 Texto

En PostgreSQL los tres se guardan igual por dentro y rinden igual. varchar(n) solo añade una restricción, y como restricción está bien: country varchar(15) documenta una intención. char(n) no: rellena con espacios invisibles que luego rompen comparaciones.

2.3 Fecha y hora

2.4 Otros

boolean (true/false/NULL), uuid, json/jsonb, arrays. Los tres últimos son de PostgreSQL y no tienen equivalente directo en SQL Server.

2.5 Alias

Muchos tipos tienen un nombre corto. Puedes escribir cualquiera de los dos, pero pg_typeof() y \d siempre responden con el nombre largo.

3. Ejemplos sobre Northwind

Qué tipo tiene cada cosa:

SELECT pg_typeof(unit_price)     AS precio,
       pg_typeof(units_in_stock) AS stock,
       pg_typeof(discontinued)   AS baja,
       pg_typeof(product_name)   AS nombre
FROM products LIMIT 1;

 precio  |  stock   |  baja   |      nombre
---------+----------+---------+-------------------
 numeric | smallint | boolean | character varying

pg_typeof() responde "¿de qué tipo es esto?" y sirve también sobre expresiones: es la forma rápida de descubrir por qué una división te dio 3 en vez de 3,5.

Nota: pg_typeof() = solo PostgreSQL

La versión detallada, con precisión y escala:

SELECT column_name, data_type, numeric_precision, numeric_scale
FROM information_schema.columns
WHERE table_name = 'order_details';

 column_name | data_type | numeric_precision | numeric_scale
-------------+-----------+-------------------+---------------
 order_id    | integer   |                32 |             0
 product_id  | integer   |                32 |             0
 unit_price  | numeric   |                10 |             2
 quantity    | smallint  |                16 |             0
 discount    | numeric   |                 4 |             3

numeric(10,2) = hasta 10 dígitos en total, 2 de ellos decimales. discount es numeric(4,3): tres decimales, porque un descuento del 12,5% se guarda como 0.125.

Si quisieras que la tabla acepte descuentos como 54,34%, habría que cambiar el tipo a numeric(5,4): ese porcentaje se guarda como 0.5434, que necesita 4 decimales, y la cifra entera deja sitio al 100% (1.0000).

3.1 Conversión: :: y CAST

SELECT '10'::int + 5      AS texto_a_int,
       '3.7'::numeric::int AS cast_a_int,
       3.5::int            AS medio,
       2.5::int            AS medio2;

 texto_a_int | cast_a_int | medio | medio2
-------------+------------+-------+--------
          15 |          4 |     4 |      3

Tres cosas que aprender de esa fila:

• '10'::int funciona: PostgreSQL convierte texto a número si el texto es un número. 'diez'::int da error.
• Convertir a entero redondea, no trunca. 3.7 → 4.
• Con numeric, redondea al alejarse del cero: 2.5 → 3, no 2. Con double precision no: redondea al par más cercano y da 2. Si el redondeo importa, no lo dejes en manos del CAST: lo vemos en la sección 3.3.

CAST(x AS tipo) es la forma del estándar y x::tipo la de PostgreSQL. Son idénticas. En este curso usamos CAST porque vale en cualquier motor; :: lo verás mucho en código de PostgreSQL.

La misma consulta con CAST:

SELECT CAST('10' AS int) AS texto_a_int,
       CAST(CAST('3.7' AS numeric) AS int) AS cast_a_int,
       CAST(3.5 AS int) AS cast_a_int_2,
       CAST(2.5 AS int) AS cast_a_int_3;

 texto_a_int | cast_a_int | cast_a_int_2 | cast_a_int_3
-------------+------------+--------------+--------------
          10 |          4 |            4 |            3

Fíjate en cast_a_int: la doble conversión '3.7'::numeric::int se convierte en dos CAST anidados. :: es más corto; CAST es más explícito y portable.

Nota: esto muestra cómo se comporta CAST, no cómo redondear: ver 3.3.

Nota: en una migración, cuántos decimales conservar = decisión de negocio, no de sintaxis.

3.2 Cuándo falla convertir texto a número

El caso típico: una API te manda los precios como texto y tú los necesitas como número. CAST(x AS numeric), sin paréntesis, conserva todos los decimales de la fuente: no redondea ni decide nada por ti.

Nota: en SQL Server, decimal sin paréntesis = cero decimales.

SELECT x AS texto, CAST(x AS numeric) AS convertido
FROM (VALUES ('2.7'), ('2.4445'), ('  2.7  '), ('-3.5'), ('+3.5'), ('.5'), ('5.'), ('1e3')) t(x);

  texto  | convertido
---------+------------
 2.7     |        2.7
 2.4445  |     2.4445
   2.7   |        2.7
 -3.5    |       -3.5
 +3.5    |        3.5
 .5      |        0.5
 5.      |          5
 1e3     |       1000

Espacios alrededor, signos y notación científica pasan sin problema. Lo que no pasa:

Todos dan el mismo error:

ERROR:  invalid input syntax for type numeric: "2,7"

Un NULL de verdad (no el texto 'null') no falla: se convierte en NULL.

Lo grave es que una sola fila mala tumba la consulta entera:

CREATE TEMP TABLE api_precios (precio text);
INSERT INTO api_precios VALUES ('2.7'), ('2.4445'), ('2,7834'), ('2.9034');

SELECT sum(CAST(precio AS numeric)) FROM api_precios;

ERROR:  invalid input syntax for type numeric: "2,7834"

Tres filas buenas y una mala: no hay resultado parcial, no hay nada.

Para encontrarlas antes de convertir, pg_input_is_valid (PostgreSQL 16+) comprueba cada valor sin romper. Comprueba si un texto concreto se puede convertir al tipo que tú le indicas en el segundo argumento:

SELECT precio, pg_input_is_valid(precio, 'numeric') AS valido
FROM api_precios;

 precio | valido
--------+--------
 2.7    | t
 2.4445 | t
 2,7834 | f
 2.9034 | t

Dos casos más que conviene conocer:

• El destino con (p,s) añade otro fallo. En numeric(p,s), p (precisión) son las cifras en total y s (escala), cuántas de ellas van después de la coma. numeric(10,2) admite 10 cifras, 2 decimales, así que quedan 8 para la parte entera. CAST('123456789.99' AS numeric(10,2)) da numeric field overflow: tiene 9 cifras enteras y solo caben 8.
• 'NaN' e 'Infinity' se convierten sin error. Si la API los manda, se cuelan en tus sumas.

3.3 Cómo redondear

La regla: round redondea, CAST convierte. Cada uno hace su trabajo.

round(x, d) redondea x a d decimales. El segundo argumento decide cuántos:

SELECT avg(unit_price)            AS sin_redondear,
       round(avg(unit_price), 2)  AS dos,
       round(avg(unit_price), 1)  AS uno,
       round(avg(unit_price))     AS entero,
       round(avg(unit_price), -1) AS decenas
FROM products;

    sin_redondear    |  dos  | uno  | entero | decenas
---------------------+-------+------+--------+---------
 28.8338961038961039 | 28.83 | 28.8 |     29 |      30

• ¿Por qué tantos decimales? avg(unit_price) = 2220.21 / 77, una división de numeric, y PostgreSQL le pone unos 16 decimales.
• Sin segundo argumento, round(x) es lo mismo que round(x, 0): redondea a entero.
• Con un número negativo, redondea a la izquierda de la coma: -1 a decenas, -2 a centenas.
• Si faltan decimales, los rellena: round(2.5, 2) da 2.50.

Aquí no hay ningún CAST porque unit_price ya es numeric. El CAST solo aparece cuando el dato no es numeric, por ejemplo cuando llega como texto.

Si el número viene como texto, son dos pasos y en este orden:

SELECT round(CAST('2.4445' AS numeric), 2) AS dos,
       round(CAST('2.4445' AS numeric), 1) AS uno,
       round(CAST('2.4445' AS numeric))    AS entero;

 dos  | uno | entero
------+-----+--------
 2.44 | 2.4 |      2

1. CAST(... AS numeric), sin (p,s): convierte el texto sin perder ningún decimal.
2. round(..., d): redondea a los decimales que tú decides.

Si además necesitas un tipo concreto, por ejemplo porque va a una columna, el CAST final va después del round:

SELECT CAST(round(CAST('2.4445' AS numeric), 2) AS numeric(10,2)) AS a_decimal,
       CAST(round(CAST('3.7' AS numeric)) AS int)                 AS a_entero;

 a_decimal | a_entero
-----------+----------
      2.44 |        4

Convertir, redondear y, si hace falta, fijar el tipo. Lo que no hay que hacer:

Y si el texto puede venir sucio, revísalo antes con pg_input_is_valid (sección 3.2): round(CAST('abc' AS numeric), 2) falla igual que el CAST solo.

3.4 round contra CAST

Los dos pueden dejarte un número con menos decimales, pero no hacen lo mismo:

• CAST cambia el tipo. Si el tipo nuevo tiene menos decimales, redondea como efecto secundario.
• round(x, d) redondea a propósito y deja el tipo como estaba.

SELECT CAST(2.4445 AS numeric(10,2)) AS con_cast,
       round(2.4445, 2)              AS con_round;

 con_cast | con_round
----------+-----------
     2.44 |      2.44

Mismo resultado. La diferencia aparece en los bordes:

Dos lecturas:

1. CAST a un tipo con (p,s) pone un límite de tamaño, y si el valor no cabe, la consulta falla. round solo quita decimales.
2. Ninguno de los dos te salva del tipo de origen. Con double precision, los dos dan 2. Si el redondeo importa, el dato tiene que ser numeric antes de redondear: round(CAST(x AS numeric), 2).

La regla: CAST para cambiar de tipo, round para redondear. Así quien lea la consulta ve dónde se pierden los decimales. round se ve a fondo en funciones-dentro-del-select.

3.5 Por qué nunca real para dinero

La demostración corta:

SELECT sum(CAST(0.01 AS real))    AS suma_real,
       sum(CAST(0.01 AS numeric)) AS suma_numeric
FROM order_details;

 suma_real | suma_numeric
-----------+--------------
 21.550398 |        21.55

Lo que hace esta consulta:

1. FROM order_details recorre las 2.155 filas de la tabla.
2. En cada fila, 0.01 es el mismo número: no lee ninguna columna.
3. sum() suma ese 0.01 una vez por fila: 2.155 × 0.01 = 21,55.

numeric da exactamente eso. real da 21,550398. Hay dos causas, y conviene tenerlas separadas:

1. La mayoría de decimales no existen exactos en binario. 0.01, 0.1 o 0.59 se guardan como una aproximación, igual que 1/3 no se puede escribir exacto en decimal (0,3333…). Solo las mitades (0,5, 0,25, 0,125…) se guardan exactas. Cada suma arrastra un poco de error y 2.155 sumas lo hacen visible.
2. real tiene unas 7 cifras para todo el número, y la parte entera y los decimales se las reparten. double precision tiene unas 15: el error es más pequeño, pero la causa 1 sigue ahí.

Con los datos reales de Northwind:

SELECT sum(unit_price * quantity)               AS numeric_exacto,
       sum(CAST(unit_price * quantity AS real)) AS con_real
FROM order_details;

 numeric_exacto |   con_real
----------------+---------------
     1354458.59 | 1.3544585e+06

Antes de mirar el resultado, fíjate en los tipos. unit_price es numeric(10,2): 10 cifras en total, 2 de ellas decimales (máximo 99999999.99). quantity es integer. Al multiplicarlos:

• El 10 deja de ser límite. El resultado es numeric sin tope de cifras, porque la operación agranda el número.
• Los 2 decimales se mantienen. Un precio con céntimos por una cantidad entera sigue teniendo céntimos.

Así que cualquier total correcto tiene que tener 2 decimales. numeric los conserva: 1354458.59.

real da 1354458.5: perdió los 9 céntimos. La parte entera ya ocupa 7 cifras y no queda sitio para los decimales. Según el cliente lo verás como 1354458.5 (Neon, DBeaver) o como 1.3544585e+06 (psql): es el mismo número escrito de dos formas.

Los dos ejemplos parecen opuestos, pero son el mismo defecto:

No es que salgan muchos o pocos decimales: en ningún caso cuadra. Un total de facturación que no sabe decirte los céntimos no es un total de facturación.

Esto no es teórico: fue uno de los defectos que había que arreglar al preparar esta base. El port original de Northwind a PostgreSQL traía unit_price como real. Está documentado en postgresql-northwind.

real sí es correcto para latitud/longitud, temperaturas, sensores: cosas que ya vienen con error de medición y donde el rendimiento importa. La regla es: si el valor se suma y alguien lo cuadra contra una factura, numeric.

3.6 La división entera

SELECT
    7/2                  AS entera,
    7.0/2                AS decimal_,
    CAST(7 AS numeric)/2 AS casteada;

 entera |      decimal_      |      casteada
--------+--------------------+--------------------
      3 | 3.5000000000000000 | 3.5000000000000000

7/2 da 3. No 3,5 y no un error: integer / integer devuelve integer, y trunca. Es la causa número uno de porcentajes que pierden los decimales.

-- Ipoh Coffee: 10 unidades pedidas de 17 en stock

-- MAL: da 58
SELECT
    100 * units_on_order / units_in_stock AS pct
FROM products
WHERE product_name = 'Ipoh Coffee';

-- BIEN: da 58.82...
SELECT
    100.0 * units_on_order / units_in_stock AS pct
FROM products
WHERE product_name = 'Ipoh Coffee';

-- BIEN también: da 58.82...
SELECT
    100 * CAST(units_on_order AS numeric) / units_in_stock AS pct
FROM products
WHERE product_name = 'Ipoh Coffee';

El truco: multiplica por 100.0 (con decimal) al principio. Eso arrastra toda la expresión a numeric. El CAST hace lo mismo y deja más clara la intención: basta con que uno de los dos números sea numeric.

Nota: entero / entero = entero, siempre (smallint, integer o bigint).

3.7 El desbordamiento

SELECT CAST(32767 AS smallint);   -- 32767, el máximo
SELECT CAST(32768 AS smallint);

ERROR:  smallint out of range

Da error, que es lo correcto: nunca te devuelve un número equivocado en silencio. Pero es la razón de no usar smallint para ahorrar: el día que la tabla crece, la aplicación deja de insertar. units_in_stock es smallint en Northwind — con 39 unidades de té va sobrado, con un almacén real no.

Los tres enteros y hasta dónde llegan:

Lo habitual es integer. Pero hay datos que lo superan: la población mundial (~8.200 millones) no cabe en integer, y lo mismo pasa con los IDs de una tabla de eventos o de logs que crece sin parar. Ahí va bigint.

3.8 El tipo de la columna no siempre es el tipo del resultado

quantity es smallint, que llega hasta 32.767. Pero su suma pasa de ese límite, y no da error:

SELECT
    sum(quantity)            AS total,
    pg_typeof(sum(quantity)) AS tipo
FROM order_details;

 total |  tipo
-------+--------
 51317 | bigint

sum() elige un tipo más grande de antemano, sin mirar el total. Cuando empieza a sumar no sabe cuántas filas hay, así que usa un tipo que seguro aguanta:

Con la población pasa lo mismo: cada país cabe en integer, y la suma de todos sale como bigint.

En la división no pasa: integer / integer sigue siendo integer (sección 3.6). Si el tipo se agranda o no depende de la operación.

3.9 Texto: char(n) y por qué no

SELECT concat(CAST('abc' AS char(5)), '|')    AS con_char,
       concat(CAST('abc' AS varchar(5)), '|') AS con_varchar;

 con_char | con_varchar
----------+-------------
 abc  |   | abc|

char(5) rellenó con dos espacios invisibles. Y length() no los ve:

SELECT length(CAST('abc' AS char(5)))       AS caracteres,
       octet_length(CAST('abc' AS char(5))) AS bytes;

 caracteres | bytes
------------+-------
          3 |     5

length ignora el relleno de char; octet_length cuenta lo que realmente se guarda. Esos espacios rompen comparaciones, concatenaciones y LIKE de formas que cuesta mucho depurar. char(n) existe por compatibilidad histórica; no lo elijas nunca.

Y el detalle de longitud con acentos:

SELECT length('café') AS caracteres, octet_length('café') AS bytes;

 caracteres | bytes
------------+-------
          4 |     5

length() cuenta caracteres, octet_length() cuenta bytes. La é ocupa dos bytes en UTF-8. Si alguna vez ves un varchar(50) quedarse corto con 47 caracteres, esta es la razón — aunque en PostgreSQL varchar(n) cuenta caracteres, no bytes, así que aquí no pasa. En otros motores sí.

3.10 Fechas

SELECT pg_typeof(order_date)   AS fecha,
       pg_typeof(current_date) AS hoy,
       pg_typeof(now())        AS ahora
FROM orders LIMIT 1;

 fecha | hoy  |          ahora
-------+------+--------------------------
 date  | date | timestamp with time zone

Restar dos date da un entero de días; age() da un interval legible:

SELECT CAST('2026-08-20' AS date) - CAST('2026-01-01' AS date) AS dias,
       age(CAST('2026-08-20' AS date), CAST('1990-05-01' AS date)) AS edad;

 dias |          edad
------+-------------------------
  231 | 36 years 3 mons 19 days

interval no es texto: es una duración con la que se calcula. Sabe que un mes no tiene un número fijo de días:

SELECT CAST('1996-01-31' AS date) + CAST('1 month' AS interval) AS mes_siguiente,
       CAST('1996-01-31' AS date) + 30                          AS mas_30_dias;

    mes_siguiente    | mas_30_dias
---------------------+-------------
 1996-02-29 00:00:00 | 1996-03-01

+ 1 month cae en el último día de febrero (1996 fue bisiesto). + 30 suma días a ciegas y se pasa a marzo.

Que order_date sea date y no timestamp es lo que hace seguro el BETWEEN de fechas del tema where-y-operadores. Con timestamp dejaría de serlo.

El filtro que pierde un día. Seis ventas con hora, y te piden las del 1 al 4 de octubre. Lo correcto son las cinco primeras: 5 ventas, 1.500.

SELECT
    count(*)     AS ventas,
    sum(importe) AS total
FROM (VALUES
        (1, TIMESTAMP '2026-10-01 09:15', 100.00),
        (2, TIMESTAMP '2026-10-03 18:40', 200.00),
        (3, TIMESTAMP '2026-10-04 00:00', 300.00),
        (4, TIMESTAMP '2026-10-04 11:20', 400.00),
        (5, TIMESTAMP '2026-10-04 22:05', 500.00),
        (6, TIMESTAMP '2026-10-05 08:00', 600.00)
     ) AS ventas (venta_id, vendida_en, importe)
WHERE vendida_en BETWEEN '2026-10-01' AND '2026-10-04';

 ventas | total
--------+--------
      3 | 600.00

Faltan 900. '2026-10-04' significa 4 de octubre a las 00:00, así que el filtro corta justo cuando empieza el día. Entra la venta 3 porque fue exactamente a medianoche; la 4 y la 5 se pierden. Ningún error, solo un total equivocado.

La forma correcta es el rango medio abierto: desde el día 1, hasta antes del día 5.

WHERE vendida_en >= '2026-10-01'
  AND vendida_en <  '2026-10-05'

 ventas |  total
--------+---------
      5 | 1500.00

CAST(vendida_en AS date) BETWEEN '2026-10-01' AND '2026-10-04' también da 5 ventas, pero aplica una función a la columna, y eso normalmente impide usar un índice sobre vendida_en: en una tabla grande es mucho más lento.

Nota: columna con hora = >= el primer día y < el día siguiente al último. Nunca BETWEEN.

4. Errores comunes

1. real/float para dinero. El error se acumula y aparece cuando alguien cuadra contra contabilidad.
2. División entera. 7/2 = 3. Multiplica por 100.0 antes de dividir.
3. Guardar números o fechas como texto. Se ordenan alfabéticamente ('10' < '9'), no se pueden sumar y no aprovechan índices bien.
4. char(n). Espacios invisibles.
5. smallint para ahorrar. Ahorras 2 bytes y compras un incidente.
6. timestamp sin zona en un sistema con usuarios en varios países. Guarda timestamptz.
7. Confiar en el redondeo implícito de un cast. CAST(2.5 AS int) es 3 con numeric, 2 con double precision, y en SQL Server CAST(3.7 AS int) es 3. Escribe round().

5. Diferencias con SQL Server

Dos avisos:

• money de SQL Server no es lo que parece. Tiene solo 4 decimales fijos y se comporta raro al dividir. La recomendación general, incluso dentro del mundo SQL Server, es usar decimal. En PostgreSQL existe un tipo money y también hay que evitarlo: depende de la configuración regional del servidor.
• CONVERT con estilos numéricos (CONVERT(varchar, fecha, 103)) no tiene equivalente. En PostgreSQL se usa to_char(fecha, 'DD/MM/YYYY') — ver funciones-dentro-del-select.

Las diferencias que cambian resultados (CAST a int que trunca, decimal sin (p,s), avg de enteros, ROUND) tienen tema propio: tipos-de-datos-postgresql-vs-sql-server.

6. Ejercicios

1. Averigua el tipo de todas las columnas de orders sin usar \d.
2. Calcula qué porcentaje de su stock tiene pedido Outback Lager. Hazlo primero mal (sin decimales) y luego bien, con dos decimales.
3. ¿Cuántos días pasaron entre el primer y el último pedido de la base?
4. Convierte unit_price a entero en products y explica qué pasa con 21.35.
5. Sin ejecutarla, di qué devuelve y por qué: sql SELECT '100' + 5;

7. Pregunta de negocio

Vas a diseñar una tabla nueva para registrar los pagos de los clientes: identificador, cliente, fecha y hora del pago, importe, moneda, y si está confirmado. Elige el tipo de cada columna y defiende dos de las elecciones.