Раздел 7 — SQL: создание структуры БД (DDL)

DDL (Data Definition Language) — подмножество SQL, управляющее структурой объектов: CREATE создаёт, ALTER изменяет, DROP удаляет, TRUNCATE очищает.

У PostgreSQL есть свойство, которого нет у большинства СУБД: DDL транзакционен. Команды изменения структуры работают внутри транзакции и откатываются вместе с ней.

BEGIN;

CREATE TABLE test_table (id integer PRIMARY KEY);
ALTER TABLE users ADD COLUMN email varchar(200);

ROLLBACK;

-- Ни таблицы, ни столбца не появилось — как будто ничего не было

Исключения из транзакционности есть, их немного: CREATE DATABASE, CREATE TABLESPACE, CREATE INDEX CONCURRENTLY, VACUUM. Их нельзя выполнять внутри блока транзакции.

Ограничения задаются двумя способами: на уровне столбца — короче, на уровне таблицы — обязательно, если ограничение затрагивает несколько столбцов.

CREATE TABLE user_bonuses (
    -- ограничения уровня столбца
    id           bigint       GENERATED ALWAYS AS IDENTITY,
    user_id      bigint       NOT NULL,
    community_id bigint       NOT NULL,
    bonus        numeric(5,2) NOT NULL DEFAULT 0,
    granted_at   timestamptz  NOT NULL DEFAULT now(),

    -- ограничения уровня таблицы, с явными именами
    CONSTRAINT pk_user_bonuses
        PRIMARY KEY (id),
    CONSTRAINT fk_user_bonuses_user_id
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_user_bonuses_community_id
        FOREIGN KEY (community_id) REFERENCES communities(id) ON DELETE CASCADE,
    CONSTRAINT uq_user_bonuses_user_community
        UNIQUE (user_id, community_id),
    CONSTRAINT ch_user_bonuses_bonus_range
        CHECK (bonus BETWEEN 0 AND 100)
);

Ограничения можно объявлять откладываемыми — тогда проверка выполняется не после каждой команды, а при фиксации транзакции. Это нужно, когда две таблицы ссылаются друг на друга и вставить первую строку иначе невозможно.

ALTER TABLE orders
    ADD CONSTRAINT fk_orders_invoice
        FOREIGN KEY (invoice_id) REFERENCES invoices(id)
        DEFERRABLE INITIALLY DEFERRED;

Суррогатный первичный ключ нужно чем-то заполнять. В PostgreSQL три способа, и они не равноценны.

-- Рекомендуемый вариант
CREATE TABLE logs (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    message text   NOT NULL
);

-- Значение подставляется автоматически
INSERT INTO logs (message) VALUES ('старт');

-- ALWAYS запрещает подставить id вручную — это защита от рассинхронизации
INSERT INTO logs (id, message) VALUES (100, 'нельзя');
-- ОШИБКА: cannot insert into column "id"

-- Обойти запрет можно явно — так делают при переносе данных
INSERT INTO logs (id, message) OVERRIDING SYSTEM VALUE
VALUES (100, 'перенос из старой базы');

Под IDENTITY скрывается последовательность — самостоятельный объект базы, который можно создать и использовать напрямую:

CREATE SEQUENCE seq_invoice_number
    START WITH 1000
    INCREMENT BY 1;

SELECT nextval('seq_invoice_number');   -- 1000, счётчик сдвинулся
SELECT currval('seq_invoice_number');   -- 1000, значение в этой сессии

-- Синхронизировать счётчик с уже загруженными данными
SELECT setval('seq_invoice_number',
              (SELECT max(number) FROM invoices));

-- Для IDENTITY то же самое делается через ALTER TABLE
ALTER TABLE logs ALTER COLUMN id RESTART WITH 1000;

Классическая ошибка после загрузки данных дампом с явными идентификаторами: счётчик остался на нуле, и первая же вставка нарушает уникальность.

-- Симптом: duplicate key value violates unique constraint "pk_users"
-- Лечение: подтянуть счётчик к данным
SELECT setval(
    pg_get_serial_sequence('users', 'id'),
    (SELECT coalesce(max(id), 0) + 1 FROM users),
    false
);

DEFAULT подставляет значение, если столбец не указан в INSERT. Вычисляется в момент вставки.

created_at timestamptz NOT NULL DEFAULT now(),
status     varchar(20)  NOT NULL DEFAULT 'new',
uuid       uuid         NOT NULL DEFAULT gen_random_uuid()

Генерируемый столбец — значение, вычисляемое из других столбцов той же строки. Пересчитывается автоматически при каждом изменении и не может быть записан вручную.

CREATE TABLE order_items (
    order_id   bigint       NOT NULL,
    product_id bigint       NOT NULL,
    quantity   integer      NOT NULL CHECK (quantity > 0),
    price      numeric(12,2) NOT NULL,

    -- вычисляется базой, вручную не записывается
    total      numeric(14,2)
        GENERATED ALWAYS AS (quantity * price) STORED,

    PRIMARY KEY (order_id, product_id)
);

Частое применение — нормализованное поле для поиска:

email        varchar(200) NOT NULL,
email_lower  varchar(200) GENERATED ALWAYS AS (lower(email)) STORED,
CONSTRAINT uq_users_email_lower UNIQUE (email_lower)

Изменить структуру пустой таблицы просто. Вся сложность появляется, когда в таблице миллионы строк, а приложение работает.

-- Безопасные операции: выполняются мгновенно
ALTER TABLE users ADD COLUMN email varchar(200);
ALTER TABLE users ADD COLUMN is_active boolean NOT NULL DEFAULT true;
ALTER TABLE users RENAME COLUMN city TO city_name;
ALTER TABLE users ALTER COLUMN email SET DEFAULT '';
ALTER TABLE users DROP COLUMN email;

-- Опасные операции: переписывают таблицу целиком под блокировкой
ALTER TABLE users ALTER COLUMN age TYPE bigint;
ALTER TABLE users ALTER COLUMN name TYPE varchar(50);   -- сужение типа
ALTER TABLE users ALTER COLUMN email SET NOT NULL;      -- полное сканирование

Для добавления проверочного ограничения на большую таблицу есть приём в два шага. Сначала ограничение добавляется как NOT VALID — оно начинает действовать на новые строки, но существующие не проверяются, и блокировки нет. Затем проверка запускается отдельно, слабой блокировкой.

-- Шаг 1: мгновенно, действует на новые данные
ALTER TABLE orders
    ADD CONSTRAINT ch_orders_amount_positive
        CHECK (amount > 0) NOT VALID;

-- Шаг 2: проверка старых строк, не блокирует запись
ALTER TABLE orders VALIDATE CONSTRAINT ch_orders_amount_positive;

Тот же приём работает для внешних ключей. Проверить, какие ограничения ещё не валидированы:

SELECT conrelid::regclass AS table_name, conname, convalidated
FROM   pg_constraint
WHERE  NOT convalidated;

PostgreSQL отслеживает зависимости между объектами и по умолчанию отказывается удалять то, на что кто-то ссылается.

-- Откажет, если на таблицу ссылаются внешние ключи или представления
DROP TABLE communities;
-- ОШИБКА: cannot drop table communities because other objects depend on it

-- Удалит вместе со всем зависимым
DROP TABLE communities CASCADE;

-- Не ошибаться, если объекта нет
DROP TABLE IF EXISTS temp_import;

-- Кто ссылается на таблицу внешними ключами
SELECT conrelid::regclass AS referencing_table, conname
FROM   pg_constraint
WHERE  confrelid = 'communities'::regclass;

-- Безопасный способ: посмотреть последствия и откатить
BEGIN;
DROP TABLE communities CASCADE;   -- выведет NOTICE со списком удаляемого
ROLLBACK;

TRUNCATE очищает таблицу, не удаляя её. В отличие от DELETE он не сканирует строки, а освобождает файлы — на большой таблице это разница между минутами и мгновением.

TRUNCATE logs;
TRUNCATE logs RESTART IDENTITY;          -- заодно сбросить счётчик
TRUNCATE orders, order_items CASCADE;   -- вместе со ссылающимися

-- Таблица из результата запроса: структура и данные
CREATE TABLE active_users AS
SELECT id, name, city FROM users WHERE age >= 18;

-- Только структура, без данных
CREATE TABLE users_archive AS
SELECT * FROM users WITH NO DATA;

-- Копия структуры с ограничениями, индексами и умолчаниями
CREATE TABLE users_copy (
    LIKE users INCLUDING ALL
);

Два специальных вида таблиц:

-- Временная: видна только своей сессии, исчезает при отключении
CREATE TEMP TABLE import_buffer (
    raw_line text
);

-- Нежурналируемая: быстрая запись, но теряется при аварийном рестарте
CREATE UNLOGGED TABLE session_cache (
    key   text PRIMARY KEY,
    value jsonb
);

Комментарии хранятся в самой базе, видны в psql по \d+ и в графических клиентах. В отличие от документа в общей папке, они не расходятся с реальностью.

COMMENT ON TABLE user_bonuses
    IS 'Бонусные баллы пользователей в разрезе сообществ';

COMMENT ON COLUMN user_bonuses.bonus
    IS 'Бонус в процентах (0–100), зависит от числа лайков';

COMMENT ON CONSTRAINT ch_user_bonuses_bonus_range ON user_bonuses
    IS 'Бизнес-правило: бонус не превышает 100%';

-- Прочитать комментарии запросом
SELECT c.relname, obj_description(c.oid) AS comment
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  n.nspname = 'public' AND c.relkind = 'r';

Структуру боевой базы не меняют руками через графический клиент. Каждое изменение оформляется миграцией — пронумерованным SQL-скриптом, который лежит в репозитории рядом с кодом.

-- migrations/003_add_user_bonuses.sql
BEGIN;

CREATE TABLE user_bonuses (
    id           bigint       GENERATED ALWAYS AS IDENTITY,
    user_id      bigint       NOT NULL,
    community_id bigint       NOT NULL,
    bonus        numeric(5,2) NOT NULL DEFAULT 0,
    CONSTRAINT pk_user_bonuses PRIMARY KEY (id),
    CONSTRAINT fk_user_bonuses_user_id
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT ch_user_bonuses_bonus_range
        CHECK (bonus BETWEEN 0 AND 100)
);

COMMENT ON TABLE user_bonuses IS 'Бонусы пользователей по сообществам';

INSERT INTO schema_migrations (version, applied_at)
VALUES (3, now());

COMMIT;

Учёт применённых миграций ведёт служебная таблица:

CREATE TABLE schema_migrations (
    version    integer     PRIMARY KEY,
    applied_at timestamptz NOT NULL DEFAULT now()
);

-- Какая версия схемы сейчас на сервере
SELECT max(version) FROM schema_migrations;