SERIAL vs IDENTITY

En MySQL escribes AUTO_INCREMENT y listo. En PostgreSQL encontrarás tutoriales con SERIAL, otros con GENERATED ALWAYS AS IDENTITY y otros con UUID. En esta lección entenderás qué son las secuencias, por qué IDENTITY es la opción moderna, cómo obtener el id insertado con RETURNING, cómo reparar una secuencia desincronizada y cuándo conviene usar UUID como clave primaria.

Concepto: las secuencias

Una secuencia es un objeto de la base de datos que entrega números consecutivos, como la máquina de turnos de una carnicería: cada quien toma un número, nunca se repite y, si alguien se va sin ser atendido, ese número se pierde.

CREATE SEQUENCE turno_seq START 1;

SELECT nextval('turno_seq');   -- 1
SELECT nextval('turno_seq');   -- 2
SELECT currval('turno_seq');   -- 2 (el último que tomó ESTA sesión)
SELECT setval('turno_seq', 100);
SELECT nextval('turno_seq');   -- 101

Las secuencias son no transaccionales: si haces ROLLBACK, el número tomado no se devuelve. Por eso los ids pueden tener huecos (1, 2, 4, 5...). Es normal y correcto.

Tanto SERIAL como IDENTITY usan una secuencia por debajo; la diferencia está en cómo se relaciona con la columna.

SERIAL (la forma clásica)

CREATE TABLE categorias (
  id SERIAL PRIMARY KEY,
  nombre TEXT NOT NULL
);

SERIAL no es un tipo real: es un atajo que PostgreSQL expande así:

CREATE SEQUENCE categorias_id_seq;
CREATE TABLE categorias (
  id INTEGER NOT NULL DEFAULT nextval('categorias_id_seq'),
  nombre TEXT NOT NULL
);
ALTER SEQUENCE categorias_id_seq OWNED BY categorias.id;

Variante	Tipo real	Máximo
SMALLSERIAL	SMALLINT	32,767
SERIAL	INTEGER	2,147,483,647
BIGSERIAL	BIGINT	9.2 × 10^18

Problemas de SERIAL:

• Es solo un DEFAULT: cualquiera puede insertar un id manual (INSERT ... VALUES (50, ...)) y la secuencia no se entera. Más tarde, nextval llegará a 50 y el INSERT fallará por clave duplicada.
• Los permisos de la secuencia se administran aparte (GRANT USAGE ON SEQUENCE ...).
• No es SQL estándar.

IDENTITY (la forma moderna, recomendada)

Desde PostgreSQL 10 existe la sintaxis del estándar SQL:

CREATE TABLE productos (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nombre TEXT NOT NULL,
  precio NUMERIC(10,2) NOT NULL
);

Dos variantes:

Variante	Si intentas insertar un id manual
GENERATED ALWAYS AS IDENTITY	Error, salvo que uses OVERRIDING SYSTEM VALUE
GENERATED BY DEFAULT AS IDENTITY	Se acepta (se comporta como SERIAL)

INSERT INTO productos (nombre, precio) VALUES ('Taza térmica', 199.00);   -- id = 1

INSERT INTO productos (id, nombre, precio) VALUES (50, 'Mochila', 650.00);
-- ERROR: cannot insert a non-DEFAULT value into column "id"
-- DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT: Use OVERRIDING SYSTEM VALUE to override.

-- Cuando de verdad lo necesitas (por ejemplo, al migrar datos con sus ids originales):
INSERT INTO productos (id, nombre, precio) OVERRIDING SYSTEM VALUE
VALUES (50, 'Mochila', 650.00);

ALWAYS te protege de errores: nadie mete ids a mano por accidente.

Opciones de la secuencia

CREATE TABLE facturas (
  id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1000 INCREMENT BY 1) PRIMARY KEY,
  total NUMERIC(10,2) NOT NULL
);

INSERT INTO facturas (total) VALUES (1597.00) RETURNING id;   -- 1000

SERIAL vs IDENTITY

	SERIAL	IDENTITY
Estándar SQL	No	Sí
Disponible desde	Siempre	PostgreSQL 10
Evita ids manuales accidentales	No	Sí (con ALWAYS)
Permisos de la secuencia	Aparte	Van con la tabla*
Copiar tabla con CREATE TABLE ... (LIKE ...)	Comparte la secuencia original (confuso)	Opciones claras
Recomendación	Código heredado	Proyectos nuevos

* El rol necesita INSERT en la tabla; en la práctica sigue siendo buena idea otorgar también USAGE en las secuencias del esquema, como viste en Esquemas y roles.

¿INT o BIGINT?

INT llega a 2,147 millones. Suena a muchísimo, pero tablas de logs, eventos o mensajes lo alcanzan, y migrar una columna PK de INT a BIGINT en una tabla enorme es doloroso. Usa BIGINT para tablas que crecerán sin límite y INT para catálogos (categorías, estados).

RETURNING: obtener lo insertado

En MySQL usas LAST_INSERT_ID(). En PostgreSQL, cualquier INSERT, UPDATE o DELETE puede devolver filas con RETURNING:

-- El id generado
INSERT INTO productos (nombre, precio) VALUES ('Lámpara de escritorio', 459.00)
RETURNING id;

-- Varias columnas, incluidos valores por defecto calculados
INSERT INTO productos (nombre, precio) VALUES
  ('Audífonos Bluetooth', 899.00),
  ('Teclado mecánico', 1250.00)
RETURNING id, nombre, precio;

id	nombre	precio
3	Audífonos Bluetooth	899.00
4	Teclado mecánico	1250.00

-- Qué cambió en un UPDATE
UPDATE productos SET precio = precio * 1.10 WHERE precio > 1000
RETURNING id, nombre, precio AS precio_nuevo;

-- Qué se borró
DELETE FROM productos WHERE id = 50 RETURNING *;

RETURNING combinado con CTEs permite operaciones de varios pasos en una sola consulta:

-- Crear un pedido y su detalle en una sola instrucción
WITH nuevo AS (
  INSERT INTO pedidos (cliente_id) VALUES (1)
  RETURNING id
)
INSERT INTO detalle_pedido (pedido_id, producto_id, cantidad, precio_unitario)
SELECT nuevo.id, p.id, 1, p.precio
FROM nuevo, productos p
WHERE p.id IN (1, 3)
RETURNING pedido_id, producto_id, precio_unitario;

-- Mover filas a un histórico: borrar y archivar a la vez
WITH borrados AS (
  DELETE FROM pedidos WHERE estado = 'cancelado' AND creado_en < now() - INTERVAL '1 year'
  RETURNING *
)
INSERT INTO pedidos_archivo SELECT * FROM borrados;

Upsert con ON CONFLICT

CREATE TABLE inventario (
  sku TEXT PRIMARY KEY,
  stock INT NOT NULL
);

-- Si el SKU no existe lo inserta; si existe, suma el stock
INSERT INTO inventario (sku, stock) VALUES ('ELE-001', 10)
ON CONFLICT (sku) DO UPDATE SET stock = inventario.stock + EXCLUDED.stock
RETURNING sku, stock;

-- Ignorar duplicados
INSERT INTO inventario (sku, stock) VALUES ('ELE-001', 5)
ON CONFLICT (sku) DO NOTHING;

EXCLUDED es la fila que intentabas insertar.

Secuencias desincronizadas

Pasa cuando insertas ids manuales (con SERIAL o OVERRIDING SYSTEM VALUE) o restauras datos de otra fuente:

-- Hay productos hasta el id 50, pero la secuencia va en 4
INSERT INTO productos (nombre, precio) VALUES ('Nuevo', 100.00);
-- ... y cuando llegue a 50:
-- ERROR: duplicate key value violates unique constraint "productos_pkey"
-- DETAIL: Key (id)=(50) already exists.

Solución: ajustar la secuencia al máximo actual.

-- Nombre de la secuencia de una columna (funciona con SERIAL e IDENTITY)
SELECT pg_get_serial_sequence('productos', 'id');
-- public.productos_id_seq

-- Sincronizar con el valor máximo
SELECT setval(
  pg_get_serial_sequence('productos', 'id'),
  COALESCE((SELECT MAX(id) FROM productos), 0) + 1,
  false     -- false = el próximo nextval devolverá exactamente ese valor
);

-- Para IDENTITY también existe:
ALTER TABLE productos ALTER COLUMN id RESTART WITH 51;

Reiniciar por completo (tablas de prueba):

TRUNCATE productos RESTART IDENTITY CASCADE;

Error común tras importar datos

Cargaste datos con sus ids originales (desde un CSV o desde MySQL) y la app empieza a fallar con duplicate key al crear registros nuevos. Casi siempre es una secuencia desincronizada: ejecuta el setval para cada tabla. Herramientas como pg_dump sí respaldan el valor de las secuencias, así que con pg_dump/pg_restore no ocurre.

UUID como clave primaria

CREATE TABLE pedidos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  cliente_id INT NOT NULL,
  creado_en TIMESTAMPTZ NOT NULL DEFAULT now()
);

A favor del UUID	En contra
Se puede generar en el cliente o en varios servidores sin coordinarse	16 bytes vs 4/8: índices más grandes
No revela cuántos registros hay ni permite adivinar ids	UUID v4 aleatorio: inserciones dispersas en el índice (más lento en tablas enormes)
Facilita combinar datos de varias bases (fusiones, sincronización offline)	Menos cómodo de leer y depurar (WHERE id = 'a3f1c2d4-...')
Seguro para URLs públicas

Opciones intermedias muy usadas:

1. PK entera + UUID público: id BIGINT IDENTITY para JOINs internos y uuid UUID UNIQUE para la API. Lo mejor de ambos.
2. UUID v7: ordenado por tiempo, evita la dispersión del v4. PostgreSQL 18 trae uuidv7(); en versiones anteriores se genera en la aplicación (por ejemplo, con el paquete uuid de Node: v7()).

Recomendación

Para la mayoría de apps: BIGINT GENERATED ALWAYS AS IDENTITY como PK y una columna uuid única si necesitas exponer identificadores. Usa UUID como PK cuando generes ids fuera de la base (apps offline, sistemas distribuidos) o tu plataforma lo use por defecto (Supabase Auth usa UUID para los usuarios).

Columnas generadas

Relacionado con IDENTITY, PostgreSQL permite columnas calculadas a partir de otras:

CREATE TABLE detalle_pedido (
  pedido_id INT NOT NULL,
  producto_id INT NOT NULL,
  cantidad INT NOT NULL CHECK (cantidad > 0),
  precio_unitario NUMERIC(10,2) NOT NULL,
  subtotal NUMERIC(12,2) GENERATED ALWAYS AS (cantidad * precio_unitario) STORED,
  PRIMARY KEY (pedido_id, producto_id)
);

INSERT INTO detalle_pedido (pedido_id, producto_id, cantidad, precio_unitario)
VALUES (1, 4, 2, 349.00)
RETURNING subtotal;   -- 698.00

No puedes escribir en subtotal: siempre se calcula. Ideal para datos derivados que consultas mucho (y se puede indexar).

Ejercicios

1. Crea la tabla clientes con id BIGINT GENERATED ALWAYS AS IDENTITY, nombre, email único y creado_en TIMESTAMPTZ.
2. Inserta tres clientes en una sola instrucción y devuelve sus ids y fechas de creación.
3. Inserta un cliente con id 100 a propósito. Luego deja la secuencia lista para que el siguiente sea el 101.
4. Escribe un upsert que inserte un cliente por email o, si ya existe, actualice su nombre.
5. ¿Qué ventaja tiene GENERATED ALWAYS sobre SERIAL en un equipo de varios desarrolladores?

Ver solución

-- 1
CREATE TABLE clientes (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  nombre TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  creado_en TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 2
INSERT INTO clientes (nombre, email) VALUES
  ('Ana López', 'ana@correo.com'),
  ('Carlos Ruiz', 'carlos@correo.com'),
  ('María Pérez', 'maria@correo.com')
RETURNING id, creado_en;

-- 3
INSERT INTO clientes (id, nombre, email) OVERRIDING SYSTEM VALUE
VALUES (100, 'Jorge Díaz', 'jorge@correo.com');
ALTER TABLE clientes ALTER COLUMN id RESTART WITH 101;

-- 4
INSERT INTO clientes (nombre, email) VALUES ('Ana L. López', 'ana@correo.com')
ON CONFLICT (email) DO UPDATE SET nombre = EXCLUDED.nombre
RETURNING id, nombre;

5. Que impide que alguien inserte ids manuales por descuido (en un script, un seed o una prueba), lo que desincronizaría la secuencia y causaría errores de clave duplicada más adelante. Si de verdad se necesita, hay que escribir OVERRIDING SYSTEM VALUE, lo que hace la intención explícita.

Resumen

• Las secuencias generan números únicos; no son transaccionales, así que los huecos son normales.
• SERIAL es un atajo antiguo (secuencia + DEFAULT); GENERATED ALWAYS AS IDENTITY es el estándar moderno.
• RETURNING devuelve lo insertado, actualizado o borrado; combinado con CTEs hace operaciones multi-paso.
• ON CONFLICT ... DO UPDATE / DO NOTHING implementa el upsert.
• Repara secuencias con setval(pg_get_serial_sequence(...), MAX(id) + 1, false).
• UUID como PK tiene pros y contras; una PK entera + UUID público suele ser el mejor equilibrio.