Типы данных: числа, строки, даты и NULL

Типы данных: числа, строки, даты и NULL

Главная идея

Как это выглядит на практике

1. Начнём с проектирования таблицы users для реального приложения. Думай о каждом столбце: что за данные, какой диапазон значений, может ли быть NULL, нужна ли точность?
2. id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY: используй BIGINT (8 байт, до 9.2 × 10^18), а не INTEGER (4 байта, максимум 2.1 млрд). В 2012 году Twitter переполнил INTEGER для tweet_id и провёл экстренную миграцию. GENERATED ALWAYS AS IDENTITY — SQL-стандарт, замена устаревшего SERIAL.
3. email TEXT NOT NULL: в PostgreSQL TEXT и VARCHAR хранятся одинаково, VARCHAR(n) только добавляет проверку длины. Для email теоретический максимум по RFC 5321 — 320 символов, но TEXT проще: не нужно помнить ограничение, не нужно его менять если стандарт изменится.
4. price NUMERIC(10, 2) NOT NULL: NUMERIC(precision, scale) — точный десятичный тип. precision=10 означает максимум 10 значащих цифр, scale=2 — два знака после запятой. NUMERIC(10,2) хранит до 99999999.99. Никогда не используй FLOAT для денег: 0.1 + 0.2 в IEEE 754 = 0.30000000000000004.
5. created_at TIMESTAMPTZ NOT NULL DEFAULT now(): TIMESTAMPTZ (timestamp with time zone) хранит момент времени как UTC внутри и конвертирует при выводе в текущий timezone сессии. Это позволяет правильно работать с пользователями из разных часовых поясов. DEFAULT now() автоматически проставляет время создания.
6. updated_at TIMESTAMPTZ: может быть NULL (если запись никогда не обновлялась — это осмысленная информация). Обновляется триггером или в коде приложения. Проверка 'WHERE updated_at IS NULL' найдёт все никогда не обновлявшиеся записи.
7. is_verified BOOLEAN NOT NULL DEFAULT false: BOOLEAN хранит true/false/NULL. DEFAULT false означает, что новые пользователи не верифицированы. Не используй VARCHAR('yes'/'no') или INTEGER(0/1) — BOOLEAN семантически корректнее и занимает 1 байт.
8. age SMALLINT CHECK (age >= 0 AND age <= 150): SMALLINT занимает 2 байта (диапазон -32768..32767) — экономия 2 байт на строку, при 100 млн строк = 200MB. CHECK ограничение гарантирует бизнес-правило на уровне БД.
9. phone_number VARCHAR(20): для телефонных номеров VARCHAR(20) разумен — номера не длиннее 20 символов в любом международном формате. Храни в E.164 формате: '+79001234567'.
10. Особая тема: NULL в агрегатных функциях. COUNT(*) считает все строки, COUNT(phone_number) считает только строки где phone_number IS NOT NULL. AVG(age) игнорирует NULL строки — среднее считается только по заполненным. SUM(amount) при всех NULL возвращает NULL, не 0.
11. UUID как первичный ключ: UUID занимает 16 байт (против 8 для BIGINT), UUID v4 случайный и вызывает фрагментацию B-tree индекса. Преимущества: глобально уникален, можно генерировать на клиенте без обращения к БД. Для большинства случаев BIGINT GENERATED ALWAYS AS IDENTITY лучше. Если нужен UUID — используй UUIDv7 (time-ordered) из PostgreSQL 17+.
12. JSONB для гибких данных: PostgreSQL поддерживает JSONB — бинарный JSON с индексами. Подходит для метаданных и атрибутов, которые различаются между объектами (например, product_attributes). Не злоупотребляй: JSONB затрудняет валидацию схемы и усложняет запросы.

Примеры кода

Правильная схема таблицы с корректными типами

CREATE TABLE users (
  id            BIGINT          GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email         TEXT            NOT NULL UNIQUE,
  full_name     TEXT            NOT NULL,
  phone_number  VARCHAR(20),
  age           SMALLINT        CHECK (age >= 0 AND age <= 150),
  is_verified   BOOLEAN         NOT NULL DEFAULT false,
  role          TEXT            NOT NULL DEFAULT 'user'
                                CHECK (role IN ('user', 'admin', 'moderator')),
  created_at    TIMESTAMPTZ     NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ
);

CREATE TABLE payments (
  id             BIGINT      GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id        BIGINT      NOT NULL REFERENCES users(id),
  amount         NUMERIC(15, 2) NOT NULL CHECK (amount > 0),
  currency       CHAR(3)     NOT NULL DEFAULT 'RUB',
  status         TEXT        NOT NULL DEFAULT 'pending'
                             CHECK (status IN ('pending', 'completed', 'failed', 'refunded')),
  paid_at        TIMESTAMPTZ,
  metadata       JSONB,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);

Демонстрация проблемы FLOAT для денег

-- Проблема FLOAT: потеря точности
SELECT
  0.1::FLOAT + 0.2::FLOAT AS float_result,
  0.1::NUMERIC + 0.2::NUMERIC AS numeric_result;
-- float_result:   0.30000000000000004
-- numeric_result: 0.3

-- Накопленная ошибка на реальных данных
SELECT
  SUM(amount::FLOAT)   AS float_sum,
  SUM(amount::NUMERIC) AS numeric_sum
FROM (VALUES
  (0.10), (0.20), (0.30), (0.10), (0.15),
  (0.25), (0.05), (0.35), (0.10), (0.20)
) AS t(amount);
-- float_sum:   1.7999999999999998
-- numeric_sum: 1.80

NULL: трёхзначная логика и правильные проверки

-- NULL не равен ничему, включая самого себя
SELECT NULL = NULL;      -- NULL (не TRUE!)
SELECT NULL IS NULL;     -- TRUE (правильно)
SELECT NULL IS NOT NULL; -- FALSE

-- NULL в логических выражениях
SELECT
  true  AND NULL,  -- NULL (неизвестно)
  false AND NULL,  -- FALSE (false AND anything = false)
  true  OR  NULL,  -- TRUE  (true OR anything = true)
  false OR  NULL;  -- NULL  (неизвестно)

-- Типичная ошибка: поиск NULL через =
SELECT count(*) FROM users WHERE updated_at = NULL;  -- всегда 0 строк!
SELECT count(*) FROM users WHERE updated_at IS NULL; -- правильно

-- COALESCE: первое не-NULL значение
SELECT COALESCE(phone_number, 'не указан') AS phone
FROM users;

-- NULLIF: вернуть NULL если значение равно второму аргументу
SELECT NULLIF(status, 'unknown') FROM orders; -- NULL если 'unknown'

TIMESTAMPTZ vs TIMESTAMP — разница в реальном приложении

-- Создаём события с двумя типами timestamp
CREATE TABLE events_demo (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  name        TEXT,
  ts_no_tz    TIMESTAMP,     -- без timezone
  ts_with_tz  TIMESTAMPTZ    -- с timezone
);

-- Вставляем с явным timezone
INSERT INTO events_demo(name, ts_no_tz, ts_with_tz) VALUES
  ('login',  '2024-03-15 10:00:00+05',  '2024-03-15 10:00:00+05'),
  ('logout', '2024-03-15 10:30:00+03',  '2024-03-15 10:30:00+03');

-- Смотрим как хранятся
SELECT name, ts_no_tz, ts_with_tz FROM events_demo;
-- TIMESTAMP выбрасывает timezone при вставке, хранит "голое" время
-- TIMESTAMPTZ конвертирует в UTC и хранит UTC

-- Изменяем timezone сессии и смотрим снова
SET timezone = 'Europe/Moscow';
SELECT name, ts_no_tz, ts_with_tz FROM events_demo;
-- ts_no_tz не меняется — она не знает своего timezone
-- ts_with_tz автоматически конвертируется в московское время

Что происходит под капотом

• INTEGER занимает ровно 4 байта и хранит значения от -2,147,483,648 до 2,147,483,647. BIGINT — 8 байт, от -9.2×10^18 до 9.2×10^18. SMALLINT — 2 байта, от -32768 до 32767. Выбор меньшего типа экономит место: для таблицы с 100 миллионами строк разница SMALLINT vs INTEGER — 200MB только на этом столбце.
• NUMERIC(p, s) хранит число как точную десятичную строку в специальном формате: знак, количество групп цифр (по 4 цифры в группе), сами цифры. Хранение занимает переменное количество байт — от 2 до нескольких десятков в зависимости от точности. Арифметика медленнее, чем с FLOAT (нет аппаратного FPU), но точная.
• FLOAT (double precision, 8 байт) и REAL (single precision, 4 байта) — это IEEE 754 форматы с плавающей точкой. Они представляют числа в двоичной системе, поэтому большинство десятичных дробей (0.1, 0.2, 0.3) хранятся приближённо. Для научных вычислений и метрик — нормально. Для денег, количества товаров, процентных ставок — недопустимо.
• TEXT и VARCHAR(n) в PostgreSQL физически хранятся одинаково через общий механизм varlena (variable-length array). VARCHAR(n) — это TEXT + невидимый CHECK (length(value) <= n). Поэтому нет никакой разницы в производительности или занимаемом месте. VARCHAR(255) — реликт MySQL, где это имело смысл.
• TIMESTAMPTZ хранит значение как 64-битное целое — количество микросекунд с 2000-01-01 00:00:00 UTC. Часовой пояс НЕ хранится вместе со значением — он применяется при вводе (для конвертации в UTC) и при выводе (для конвертации обратно). Настройка timezone сессии через SET timezone = 'Europe/Moscow' влияет только на отображение.
• BOOLEAN в PostgreSQL занимает 1 байт и хранит три состояния: true ('t', 'true', 'yes', '1'), false ('f', 'false', 'no', '0'), и NULL. В C-коде PostgreSQL это значения BoolGetDatum(true) и BoolGetDatum(false). Эффективен в WHERE благодаря возможности создать partial index: CREATE INDEX ON users(id) WHERE is_active = true.
• UUID занимает 16 байт (128 бит) против 8 байт BIGINT. UUID v4 (случайный) плох для B-tree индекса первичного ключа: случайные значения вызывают много page splits и приводят к высокому индексному bloat. UUID v7 (time-ordered, в PostgreSQL 17) решает эту проблему. Альтернатива: ULID — Universally Unique Lexicographically Sortable Identifier.
• JSONB (binary JSON) хранит JSON в разобранном бинарном виде, что позволяет: прямую индексацию через GIN, доступ к полю без парсинга, операторы containment (@>, <@). JSON (текстовый) хранит JSON как строку — быстрее записывает, медленнее читает, не поддерживает GIN. Для 99% случаев используй JSONB.
• Приведение типов в PostgreSQL: явное (value::INTEGER, CAST(value AS INTEGER)) и неявное (PostgreSQL автоматически конвертирует '42' в INTEGER при сравнении с INTEGER-столбцом). Неявные приведения могут стоить индекса: WHERE user_id = '42' если user_id BIGINT — PostgreSQL приведёт строку к BIGINT и использует индекс. Но WHERE to_char(created_at, 'YYYY-MM') = '2024-03' — функция на столбце, индекс не используется.
• Перечислимые типы: PostgreSQL имеет ENUM (CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped')). ENUM: компактно (4 байта), проверяет при INSERT/UPDATE, но добавление нового значения — ALTER TYPE (в PostgreSQL 9.1+ это non-blocking). Альтернатива: TEXT + CHECK — гибче для миграций, но менее семантичен. Для финансовых систем с неизменяемым набором статусов ENUM часто предпочтительнее.

Термины урока

Мини-разбор реальной ситуации

Финтех-стартап запускает систему расчётов между пользователями. Первый разработчик выбирает тип amount как FLOAT DOUBLE PRECISION — 'точности достаточно, деньги небольшие'. Первые 3 месяца всё выглядит нормально. На 4-й месяц бухгалтер замечает: сумма всех входящих переводов не совпадает с суммой всех исходящих на 0.03 рубля. Начинается разбирательство. Выясняется: при конвертации FLOAT в строку для отображения некоторые суммы отображаются как 99.99999999... вместо 100.00 — это нормальное поведение IEEE 754. Накопленная ошибка растёт. Для исправления нужна миграция: ALTER TABLE payments ALTER COLUMN amount TYPE NUMERIC(15,2) — но таблица уже 40 миллионов строк, операция займёт часы и заблокирует таблицу. Команда проводит 3-шаговую zero-downtime миграцию: добавляет новый столбец amount_numeric, заполняет его постепенно, переключает код. Две недели работы вместо одной строки в CREATE TABLE.