# PostgreSQL desde cero (II): tipos, restricciones y relaciones

*Segunda entrega de la serie **[PostgreSQL desde cero a pro](/search?tag=postgresql-desde-cero)**. Tiempo de lectura estimado: 11 minutos.*

En la [entrega anterior](/post/postgresql-desde-cero-i-instalacion-psql-y-primeros-pasos) levantamos PostgreSQL, creamos una base de datos y metimos las primeras filas. En este post nos centramos en lo que convierte PostgreSQL en una base de datos seria: **tipos ricos** y **restricciones** que garantizan que los datos sean correctos desde la base de datos, no desde la capa de aplicación.

El principio subyacente: si puedes hacer que un dato imposible sea imposible de insertar, **hazlo**. Las validaciones en la aplicación están muy bien; las restricciones en la base de datos son la última línea de defensa y la única que no se salta con un backfill manual por psql a las tres de la mañana.

## El sistema de tipos

PostgreSQL tiene uno de los sistemas de tipos más ricos entre las bases de datos relacionales.

### Enteros, decimales, floats

```sql
SMALLINT       -- 2 bytes, ±32k
INTEGER        -- 4 bytes, ±2.100M
BIGINT         -- 8 bytes, ±9 trillones

NUMERIC(p, s)  -- precisión arbitraria. Para dinero: SIEMPRE
REAL           -- 4 bytes float
DOUBLE PRECISION  -- 8 bytes float
```

Regla universal: **dinero = `NUMERIC`**. Nunca `REAL` ni `DOUBLE PRECISION` para importes. El redondeo de los float te hace llorar el día de la auditoría.

### Texto

```sql
TEXT          -- variable, sin límite de longitud
VARCHAR(n)    -- variable, con longitud máxima
CHAR(n)       -- fijo (padding con espacios)
```

Al contrario que en otras bases de datos, en PostgreSQL **`TEXT` y `VARCHAR` tienen el mismo rendimiento**. No hay razón para usar `VARCHAR(n)` salvo que quieras imponer un límite. `CHAR(n)` casi nunca es lo que quieres.

### Fechas y tiempos

```sql
DATE
TIME           -- solo hora, sin fecha
TIMESTAMP      -- fecha y hora, SIN zona horaria
TIMESTAMPTZ    -- fecha y hora, CON zona horaria
INTERVAL
```

Usa **siempre `TIMESTAMPTZ`** a menos que sepas exactamente por qué no. `TIMESTAMPTZ` guarda el instante en UTC y lo presenta en el huso horario de la sesión. `TIMESTAMP` te dejará clavado en un huso sin más contexto y ese bug aparecerá cuando viajes a otro país.

### UUID

```sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ...
);
```

Los UUID como PK son útiles cuando generas IDs en el cliente o quieres evitar adivinar enumeraciones. Son más grandes que los enteros (16 bytes vs 8) y se fragmentan más en los índices: para una clave primaria muy caliente, sigue siendo mejor `BIGINT` con secuencia.

### Tipos propios: enums

```sql
CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived');

ALTER TABLE posts ADD COLUMN status post_status NOT NULL DEFAULT 'draft';
```

Los enums son rápidos y ocupan poco. El contra: añadir un valor requiere `ALTER TYPE`, que en versiones antiguas podía ser molesto. En PostgreSQL moderno es trivial:

```sql
ALTER TYPE post_status ADD VALUE 'scheduled' AFTER 'draft';
```

Alternativa idiomática: una columna `TEXT` con un `CHECK` que limita los valores. Más flexible que el enum, más lento por fila (muy poco).

### Arrays

```sql
CREATE TABLE posts (
  ...
  tags TEXT[]
);

INSERT INTO posts (..., tags) VALUES (..., ARRAY['postgresql', 'tutorial']);

SELECT * FROM posts WHERE 'postgresql' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['postgresql', 'tutorial'];
SELECT tag, count(*) FROM posts, unnest(tags) AS tag GROUP BY tag;
```

Los arrays son geniales para datos que no merecen una tabla dedicada: tags, identificadores secundarios, opciones de una config simple. Si estás manipulando un array con mucha frecuencia o necesitas atributos por elemento, es señal de que toca modelo relacional normal.

### JSON y JSONB

PostgreSQL tiene dos tipos para JSON:

- `JSON`: almacena el texto tal cual. Preserva formato y duplicados de claves.
- `JSONB`: parseado a estructura binaria. Más rápido para consultar, permite índices GIN.

**Usa `JSONB` siempre**, salvo que necesites preservar el formato exacto (casi nunca).

```sql
CREATE TABLE events (
  id    BIGSERIAL PRIMARY KEY,
  type  TEXT NOT NULL,
  data  JSONB NOT NULL
);

INSERT INTO events (type, data) VALUES
  ('signup', '{"email":"a@b.com","plan":"free","referrer":"google"}');

-- Acceso
SELECT data->>'email', data->'plan' FROM events;
SELECT * FROM events WHERE data @> '{"plan":"free"}';
SELECT * FROM events WHERE data ? 'referrer';

-- Índice GIN para consultas de contención
CREATE INDEX events_data_gin ON events USING GIN (data jsonb_path_ops);
```

Operadores clave:
- `->` devuelve JSONB
- `->>` devuelve TEXT
- `@>` "contiene"
- `?` "tiene la clave"
- `#>` ruta (igual que `->` encadenado)

`JSONB` no reemplaza al modelo relacional: es para lo que de verdad es semi-estructurado (payloads de webhooks, propiedades de evento de analítica, configuraciones flexibles). Si vas a consultar siempre los mismos cinco campos, crea columnas.

## Restricciones

### NOT NULL

Obligatorio pensar en qué columnas pueden ser nulas y cuáles no. `NOT NULL` es la restricción más fundamental y la más olvidada.

```sql
CREATE TABLE authors (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL,
  name  TEXT NOT NULL
);
```

### UNIQUE

```sql
CREATE TABLE authors (
  id    BIGSERIAL PRIMARY KEY,
  email TEXT NOT NULL UNIQUE
);

-- Único compuesto
CREATE TABLE memberships (
  user_id  BIGINT NOT NULL,
  group_id BIGINT NOT NULL,
  UNIQUE (user_id, group_id)
);

-- Único parcial (solo para filas activas)
CREATE UNIQUE INDEX users_email_active_unique
  ON users(lower(email))
  WHERE deleted_at IS NULL;
```

El índice único **parcial** es una herramienta infravalorada: permite tener "único entre los activos" sin sacrificar historicidad.

### PRIMARY KEY vs IDENTITY

`BIGSERIAL` sigue siendo válido, pero el estándar SQL moderno es:

```sql
CREATE TABLE authors (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ...
);
```

Diferencias prácticas:

- `GENERATED ALWAYS`: el cliente no puede insertar un valor explícito (a menos que use `OVERRIDING SYSTEM VALUE`).
- `GENERATED BY DEFAULT`: se genera si no lo das, pero puedes darlo.
- Encaja mejor con migraciones entre SGBDs.

Si empiezas un proyecto hoy, usa `IDENTITY`. Si mantienes uno viejo, `BIGSERIAL` sigue funcionando perfectamente.

### CHECK

Cualquier expresión booleana vale como restricción:

```sql
CREATE TABLE products (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name     TEXT NOT NULL,
  price    NUMERIC(12,2) NOT NULL CHECK (price >= 0),
  sku      TEXT NOT NULL CHECK (sku ~ '^[A-Z0-9-]{4,20}$'),
  discount NUMERIC(4,2) CHECK (discount IS NULL OR (discount >= 0 AND discount <= 1))
);
```

Los `CHECK` son rápidos, se ejecutan en inserts/updates y sobreviven a cualquier cliente buggy. Úsalos sin miedo.

### Foreign keys

```sql
CREATE TABLE posts (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id BIGINT NOT NULL REFERENCES authors(id) ON DELETE RESTRICT,
  ...
);
```

Opciones en `ON DELETE` y `ON UPDATE`:

- `NO ACTION` (default): error si quedan referencias.
- `RESTRICT`: igual que NO ACTION pero se evalúa inmediatamente.
- `CASCADE`: propaga el borrado/update.
- `SET NULL`: pone NULL en la referencia.
- `SET DEFAULT`: pone el default de la columna.

`CASCADE` es cómodo pero peligroso: un borrado accidental puede vaciar media base de datos. Yo uso `RESTRICT` salvo que el modelo de dominio claramente pida cascada (por ejemplo, "al borrar un pedido, sus líneas").

### Exclusion constraints

El pariente menos conocido y más potente. Impide que dos filas "se solapen" según cualquier operador:

```sql
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id  BIGINT NOT NULL,
  period   TSTZRANGE NOT NULL,
  EXCLUDE USING GIST (
    room_id WITH =,
    period  WITH &&
  )
);
```

Traducción: no puede haber dos reservas de la misma habitación cuyos periodos se solapen. Imposible. Sin triggers, sin locks manuales, sin código de aplicación defensivo. Esto es PostgreSQL luciéndose.

## Relaciones: uno a muchos, muchos a muchos

Uno a muchos: una foreign key en el lado "muchos".

```sql
authors 1 ----< N posts
```

```sql
CREATE TABLE authors (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ...
);
CREATE TABLE posts (
  id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id BIGINT NOT NULL REFERENCES authors(id),
  ...
);
```

Muchos a muchos: una tabla de unión con dos FKs.

```sql
posts N >----< M tags  (vía post_tags)
```

```sql
CREATE TABLE tags (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  BIGINT NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);
```

La tabla de unión con PK compuesta da unicidad y el índice necesario para consultas en ambos sentidos.

## Migraciones seguras

PostgreSQL es transaccional también para DDL: `CREATE TABLE`, `ALTER TABLE`, etc. pueden envolverse en `BEGIN ... COMMIT`. Esto significa que una migración que falla a mitad **no deja el esquema a medio aplicar**. Es una de sus mejores cualidades.

Algunas operaciones, sin embargo, toman **locks exclusivos** en la tabla que pueden bloquear tráfico:

- `ALTER TABLE ... ADD COLUMN col TYPE NOT NULL DEFAULT 'x'` en versiones antiguas reescribía la tabla.
- `CREATE INDEX` bloquea escrituras hasta que termina.
- `ALTER TYPE` con cambios de representación reescribe datos.

Alternativas seguras:

```sql
CREATE INDEX CONCURRENTLY posts_slug_idx ON posts(slug);
```

`CONCURRENTLY` construye el índice sin bloquear escrituras, a cambio de ser más lento y no poder ejecutarse dentro de una transacción. Las herramientas modernas de migración (Flyway, sqitch, la CLI de tu ORM) suelen tener soporte explícito.

Para añadir columnas nuevas con NOT NULL en tablas grandes, el patrón moderno es:

1. Añadir columna **sin** NOT NULL.
2. Hacer backfill por lotes.
3. Añadir el NOT NULL (validación barata si no hay nulos).

## Un esquema completo

Uniendo piezas de los dos posts:

```sql
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS citext;

CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived');

CREATE TABLE authors (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      CITEXT NOT NULL UNIQUE,
  name       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE posts (
  id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id    BIGINT NOT NULL REFERENCES authors(id) ON DELETE RESTRICT,
  title        TEXT NOT NULL,
  slug         TEXT NOT NULL UNIQUE,
  body         TEXT NOT NULL,
  status       post_status NOT NULL DEFAULT 'draft',
  metadata     JSONB NOT NULL DEFAULT '{}',
  published_at TIMESTAMPTZ,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (status != 'published' OR published_at IS NOT NULL)
);

CREATE TABLE tags (
  id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name CITEXT NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id  BIGINT NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX posts_published_at_idx ON posts(published_at DESC)
  WHERE status = 'published';
```

Dos detalles nuevos:

- `CITEXT` (extensión): texto case-insensitive. Para emails y nombres de tag, ahorra `lower()` por todos lados.
- El `CHECK` en `posts` impone invariante: si el estado es `published`, `published_at` no puede ser NULL.

## Por dónde seguir

- **[III: CTEs, window functions y consultas avanzadas](/post/postgresql-desde-cero-iii-ctes-window-functions-y-consultas-avanzadas)** — ya tienes buenos datos, ahora toca consultarlos como un adulto.
- **[IV: índices, EXPLAIN y rendimiento](/post/postgresql-desde-cero-iv-indices-explain-y-rendimiento)** — cuando el modelo crece y las consultas ya no son instantáneas.
- **[V: replicación, backups y producción](/post/postgresql-desde-cero-v-replicacion-backups-y-produccion)** — operar PostgreSQL sin perder datos ni sueño.

¿Llegas tarde a la serie? [I: instalación, psql y primeros pasos](/post/postgresql-desde-cero-i-instalacion-psql-y-primeros-pasos).