Type Affinity en SQLite: cómo se comportan los tipos

La afinidad es una preferencia, no una regla

SQLite usa tipado dinámico, así que conviene entender desde el principio cómo funcionan los tipos de datos en SQLite. Cada valor lleva consigo su propia clase de almacenamiento (NULL, INTEGER, REAL, TEXT, BLOB), y el tipo declarado de una columna no restringe de forma estricta lo que puedes guardar en ella. Lo que sí hace ese tipo declarado es asignarle a la columna una afinidad de tipo: la clase de almacenamiento preferida a la que SQLite intentará convertir los valores que reciba.

Mira lo que pasa cuando la afinidad no basta para evitar un desajuste:

CREATE TABLE items (
    id INTEGER,
    name TEXT
);

INSERT INTO items VALUES (1, 'manzana');
INSERT INTO items VALUES ('two', 'plátano');

SELECT id, typeof(id), name FROM items;

La segunda fila guarda la cadena 'two' dentro de una columna INTEGER. SQLite intentó convertir 'two' a número, no pudo (no es numérico) y aun así lo almacenó como TEXT. La función typeof() te muestra la clase de almacenamiento real de cada valor, y no siempre coincide con lo que declara la columna.

A quien viene de Postgres o MySQL esto le choca, pero está hecho a propósito.

Las cinco afinidades de tipos en SQLite

Toda columna de una tabla que no sea STRICT recibe exactamente una de estas afinidades:

• TEXT — prefiere cadenas.
• NUMERIC — prefiere números, pero acepta texto si no logra convertirlo.
• INTEGER — igual que NUMERIC, salvo que guarda como entero todo valor sin parte decimal.
• REAL — prefiere números de punto flotante.
• BLOB — sin preferencia, guarda tal cual lo que le pases.

A la afinidad BLOB también se le dice "sin afinidad": es la que te toca cuando no declaras ningún tipo.

CREATE TABLE samples (
    a,                 -- Afinidad BLOB (sin tipo declarado)
    b TEXT,            -- TEXT
    c INTEGER,         -- INTEGER
    d REAL,            -- REAL
    e NUMERIC          -- NUMERIC
);

INSERT INTO samples VALUES ('42', '42', '42', '42', '42');
SELECT typeof(a), typeof(b), typeof(c), typeof(d), typeof(e) FROM samples;

La misma entrada — la cadena '42' — y cinco tipos almacenados distintos. Cada columna convirtió (o no) el valor según su afinidad.

Cómo decide SQLite la afinidad a partir de tu declaración

Aquí está el detalle que confunde a mucha gente: SQLite no tiene una lista cerrada de tipos "válidos". Puedes escribir casi cualquier cosa después del nombre de una columna, y SQLite deduce la afinidad buscando subcadenas en el texto, siguiendo este orden:

1. Contiene INT → INTEGER
2. Contiene CHAR, CLOB o TEXT → TEXT
3. Contiene BLOB, o no tiene tipo declarado → BLOB
4. Contiene REAL, FLOA o DOUB → REAL
5. Cualquier otra cosa → NUMERIC

Ese es todo el algoritmo. Y explica un montón de rarezas:

CREATE TABLE quirks (
    a VARCHAR(255),       -- TEXT (contiene CHAR)
    b BIGINT,             -- INTEGER (contiene INT)
    c DOUBLE PRECISION,   -- REAL (contiene DOUB)
    d DECIMAL(10,2),      -- NUMERIC (no coincide con ninguno de los anteriores)
    e BOOLEAN,            -- NUMERIC (no coincide con ninguno de los anteriores)
    f DATETIME,           -- NUMERIC (no coincide con ninguno de los anteriores)
    g FLOATING_POINTS,    -- INTEGER (contiene INT — ¡sorpresa!)
    h GIBBERISH           -- NUMERIC (no coincide con ninguno de los anteriores)
);

SELECT name, type FROM pragma_table_info('quirks');

FLOATING_POINTS termina con afinidad INTEGER porque la subcadena INT aparece dentro de POINTS. Gana la primera regla que coincida, empezando por arriba. Por eso, copiar tipos a ciegas desde otra base de datos puede darte resultados distintos a los que esperabas.

La afinidad de tipos en acción: conversiones al insertar

La afinidad de tipos en SQLite cobra importancia cuando el motor decide si convertir tu valor o guardarlo tal cual. Estas son las reglas:

• Afinidad TEXT: los números y los BLOB se convierten a texto.
• Afinidades NUMERIC, INTEGER y REAL: el texto que parece un número se convierte; el que no, se queda como texto.
• Afinidad BLOB: no se convierte nada.

CREATE TABLE conv (
    n NUMERIC,
    t TEXT,
    b BLOB
);

INSERT INTO conv VALUES ('123', 456, 789);
INSERT INTO conv VALUES ('12.5', 7.5, 'hello');
INSERT INTO conv VALUES ('hello', 'world', 'world');

SELECT n, typeof(n), t, typeof(t), b, typeof(b) FROM conv;

Fila por fila:

• '123' en una columna NUMERIC se convierte en el entero 123. La conversión de texto a número se hizo sin perder información.
• '12.5' pasa a ser el real 12.5.
• 'hello' en NUMERIC se queda como texto, porque no hay ningún número al que convertirlo.
• La columna TEXT convierte los números a su forma de cadena.
• La columna BLOB guarda todo tal cual se lo pasas, tipo incluido.

El detalle entre INTEGER y REAL

La afinidad INTEGER se comporta casi igual que NUMERIC, salvo por un matiz: un valor como 3.0, que en realidad no tiene parte fraccionaria, se almacena como el entero 3 para ahorrar espacio.

CREATE TABLE nums (
    i INTEGER,
    n NUMERIC
);

INSERT INTO nums VALUES (3.0, 3.0);
INSERT INTO nums VALUES (3.5, 3.5);

SELECT i, typeof(i), n, typeof(n) FROM nums;

3.0 se guarda como INTEGER en ambas columnas — la optimización también ocurre con NUMERIC. 3.5 conserva su parte decimal y se queda como REAL. La moraleja: no confíes en typeof() para saber si una columna fue declarada como INTEGER o REAL. Lo que te devuelve es lo que hay almacenado de verdad, y eso puede cambiar fila por fila.

Cuándo la afinidad de tipos en SQLite te juega en contra

Esa flexibilidad es cómoda… hasta que deja de serlo. En código real aparecen dos problemas típicos:

1. Se cuelan datos malos. Si tu aplicación tiene un bug y manda 'N/A' a una columna INTEGER, SQLite lo acepta sin chistar. Luego, las consultas que hacen aritmética sobre esa columna devuelven resultados raros o NULL. Ni un error, ni un aviso: corrupción silenciosa y a otra cosa.

2. Las comparaciones se vuelven raras. El orden y las comprobaciones de igualdad tratan los valores de forma distinta según su clase de almacenamiento:

CREATE TABLE mixed (val INTEGER);
INSERT INTO mixed VALUES (10), (2), ('100'), ('20'), (3);

SELECT val, typeof(val) FROM mixed ORDER BY val;

Los enteros se ordenan numéricamente, luego los textos se ordenan lexicográficamente — y aparecen después de todos los números. Por eso obtienes 2, 3, 10 (los enteros en orden numérico) y a continuación '20', '100' (las cadenas en orden alfabético). No es lo que la mayoría espera.

Si controlas los INSERT y validas con cuidado, las tablas normales te sirven. Si no — o simplemente prefieres que la base de datos te imponga los tipos — hay una opción mejor.

Lo que viene: tablas STRICT

SQLite 3.37 incorporó las tablas STRICT, que desactivan la afinidad de tipos y rechazan los valores que no coincidan con el tipo declarado. Te dan el tipado dinámico de SQLite cuando lo quieres, y una validación al estilo Postgres cuando no. Eso lo vemos en la próxima página.