tables-and-types

Таблицы и типы

Предпосылки: реляционная модель (таблица, строка, столбец), типы данных (числа, текст, даты, boolean, NULL).

← Оконные функции | Ограничения →

До сих пор таблицы просто «существовали» — мы читали и анализировали данные. Новый проект, база пуста. Нужно определить структуру: создать таблицу, задать столбцы с типами, настроить значения по умолчанию и автоматическую генерацию id.

Определяем таблицу employees — CREATE TABLE

CREATE TABLE (англ. «создать таблицу») определяет структуру:

CREATE TABLE employees (
    id            integer,
    name          text NOT NULL,
    department_id integer,
    salary        integer,
    hire_date     date DEFAULT CURRENT_DATE
);

Каждый столбец имеет имя и тип. NOT NULL и DEFAULT — часть определения столбца.

Именование

Имена таблиц и столбцов: snake_case, множественное число для таблиц (employees, orders). Зарезервированные слова (user, order, table) — допустимы в двойных кавычках ("user"), но лучше избегать: users, orders.

Выбор типов

Типы подробно описаны в типах данных и NULL. Здесь — практические правила выбора для конкретных задач:

Задача	Тип	Почему
Идентификатор	integer / bigint	bigint для таблиц, которые вырастут за 2 млрд строк
Деньги	numeric(12, 2)	Точная арифметика, нет ошибок округления
Текст без ограничения	text	Стандартный строковый тип PostgreSQL
Текст с ограничением	varchar(255)	Когда длина ограничена бизнес-правилом
Дата	date	Без времени
Время события	timestamptz	С часовым поясом — почти всегда правильный выбор
Да/нет	boolean	true/false/NULL

Микросервис забыл created_at — DEFAULT

Один из микросервисов вставляет заказы без указания времени создания. Вместо ошибки или NULL нужна автоматическая подстановка текущего времени.

DEFAULT задаёт значение столбца, если при INSERT оно не указано:

CREATE TABLE orders (
    id         integer,
    total      integer DEFAULT 0,
    created_at timestamptz DEFAULT now(),
    status     text DEFAULT 'pending'
);
 
INSERT INTO orders (id, total) VALUES (1, 15000);

 id | total | created_at                 | status
----+-------+----------------------------+---------
  1 | 15000 | 2024-03-15 10:30:00+00     | pending

created_at и status получили значения по умолчанию. DEFAULT вычисляется в момент INSERT, поэтому now() даёт текущее время вставки.

Конкурентная вставка — SERIAL и IDENTITY

Два сервиса одновременно создают пользователей. Если оба выберут MAX(id) + 1, они получат один и тот же id — конфликт. Нужна автоматическая генерация уникальных id.

SERIAL — исторический способ

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL
);

BIGSERIAL — синтаксический сахар: создаёт столбец bigint, отдельный объект sequence и DEFAULT nextval(sequence). При INSERT без явного id — автоматически берётся следующее число из sequence.

IDENTITY — стандартный способ

CREATE TABLE users (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL
);

IDENTITY (стандарт SQL) тоже использует sequence, но объявляется как часть столбца.

Два режима:

• GENERATED BY DEFAULT AS IDENTITY — можно явно вставить id, иначе nextval.
• GENERATED ALWAYS AS IDENTITY — явная вставка запрещена (защита от случайных конфликтов).

Дырки в id

Вызов nextval() не откатывается вместе с транзакцией (транзакция — группа операций, выполняемых целиком или отменяемых целиком; подробнее в транзакциях):

BEGIN;      -- открыть транзакцию
INSERT INTO users(email) VALUES ('a@example.com'); -- id=100
ROLLBACK;   -- отменить все изменения
 
INSERT INTO users(email) VALUES ('b@example.com'); -- id=101, не 100

Дырки — нормальное поведение: sequence оптимизирован на параллельность, а не на «красивые числа». Не используйте id как порядок событий — для этого нужен отдельный столбец (created_at).

Добавить столбец email — ALTER TABLE

Проект растёт, нужен email. ALTER TABLE изменяет существующую таблицу:

ALTER TABLE employees ADD COLUMN email text;
ALTER TABLE employees DROP COLUMN email;
ALTER TABLE employees ALTER COLUMN salary SET DEFAULT 0;
ALTER TABLE employees ALTER COLUMN name SET NOT NULL;
ALTER TABLE employees RENAME COLUMN dept TO department_id;
ALTER TABLE employees RENAME TO staff;

Некоторые операции (ADD COLUMN с DEFAULT в PostgreSQL 11+) выполняются мгновенно — PostgreSQL запоминает значение по умолчанию в метаданных, не перезаписывая каждую строку. Другие (изменение типа столбца) могут требовать перезаписи всей таблицы — на больших таблицах это означает длительную блокировку. Какие операции безопасны и как обойти опасные — в миграциях.

Удаление таблицы — DROP TABLE

DROP TABLE employees;
DROP TABLE IF EXISTS employees;  -- без ошибки, если таблицы нет

DROP TABLE удаляет таблицу и все данные необратимо. IF EXISTS предотвращает ошибку, если таблица уже удалена.

CASCADE удаляет зависимые объекты (представления, ссылающиеся foreign key):

DROP TABLE employees CASCADE;

Зависимости между таблицами подробнее — в ограничениях.

Как строки хранятся физически на диске — в страницах и кортежах.

Sources

• PostgreSQL Documentation (v16): CREATE TABLE. https://www.postgresql.org/docs/16/sql-createtable.html
• PostgreSQL Documentation (v16): ALTER TABLE. https://www.postgresql.org/docs/16/sql-altertable.html
• PostgreSQL Documentation (v16): Identity Columns. https://www.postgresql.org/docs/16/ddl-identity-columns.html

---

← Оконные функции | Ограничения →