Урок 04

Создание таблиц и схемы

Базовый синтаксис

CREATE TABLE users (
    id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name  TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Что здесь:

  • CREATE TABLE — создать таблицу.
  • id — колонка.
  • BIGINT — тип.
  • GENERATED ... IDENTITY — автогенерация.
  • PRIMARY KEY — первичный ключ.
  • NOT NULL — обязательное.
  • UNIQUE — уникальное.
  • DEFAULT NOW() — значение по умолчанию.

Ограничения (constraints)

5 видов:

  • PRIMARY KEY — уникальный идентификатор.
  • UNIQUE — уникальность.
  • NOT NULL — запрет NULL.
  • CHECK — проверка условия.
  • FOREIGN KEY — ссылка на другую таблицу.

PRIMARY KEY

-- Одна колонка
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- Составной (несколько колонок)
PRIMARY KEY (user_id, role_id)

Что даёт:

  • Уникальность.
  • NOT NULL.
  • Автоматический индекс.
  • Цель для FK.

Правило: у каждой таблицы — свой PK.

UNIQUE

email VARCHAR(255) NOT NULL UNIQUE

-- Составной
UNIQUE (user_id, course_id)

Отличие от PK: может быть NULL (если не NOT NULL).

Named constraint:

email VARCHAR(255) NOT NULL,
CONSTRAINT users_email_unique UNIQUE (email)

Зачем имя: понятнее в ошибках и миграциях.

CHECK

price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
status TEXT NOT NULL CHECK (status IN ('draft', 'published')),
age INTEGER CHECK (age >= 18)

Named:

CONSTRAINT products_price_positive CHECK (price >= 0)

Что даёт: валидация на уровне БД. Даже если приложение сломается — БД не пропустит.

⚠️ CHECK не должен зависеть от других строк (для этого — триггеры).

FOREIGN KEY

CREATE TABLE posts (
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id   BIGINT NOT NULL,
    title     TEXT NOT NULL,

    CONSTRAINT posts_user_fk
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Что означает:

  • REFERENCES users(id) — ссылка на users.id.
  • ON DELETE CASCADE — удалить родителя → удалить потомков.
  • ON UPDATE CASCADE — обновить родителя → обновить потомков.

Действия ON DELETE / ON UPDATE:

  • CASCADE — каскадом.
  • SET NULL — обнулить FK.
  • SET DEFAULT — дефолт.
  • RESTRICT — запретить (по умолчанию).
  • NO ACTION — как RESTRICT, но проверка в конце транзакции.

Правило:

  • Комментарии/лайки → CASCADE.
  • Справочники → RESTRICT.
  • Опциональная связь → SET NULL.

DEFAULT

created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
status TEXT NOT NULL DEFAULT 'draft',
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
tags TEXT[] NOT NULL DEFAULT '{}'

Функции:

  • NOW() — текущее время.
  • gen_random_uuid() — UUID.
  • CURRENT_USER — имя пользователя.

⚠️ DEFAULT срабатывает только при отсутствии значения в INSERT.

Схемы

Схема — пространство имён для таблиц.

CREATE SCHEMA auth;
CREATE SCHEMA billing;
CREATE SCHEMA analytics;

Обращение:

SELECT * FROM auth.users;
SELECT * FROM billing.invoices;

Поиск по умолчанию: search_path.

SHOW search_path;                 -- "$user", public
SET search_path TO auth, public;

Что даёт:

  • Логическая группировка.
  • Разные права на схемы.
  • Разные префиксы для разных модулей.

ALTER TABLE

Добавить колонку:

ALTER TABLE users ADD COLUMN last_login_at TIMESTAMPTZ;

Удалить:

ALTER TABLE users DROP COLUMN last_login_at;

Переименовать:

ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users RENAME TO accounts;

Изменить тип:

ALTER TABLE users ALTER COLUMN age TYPE INTEGER USING age::integer;

Добавить / убрать NOT NULL:

ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ALTER COLUMN bio DROP NOT NULL;

Добавить / убрать DEFAULT:

ALTER TABLE users ALTER COLUMN status SET DEFAULT 'draft';
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

Добавить constraint:

ALTER TABLE users ADD CONSTRAINT users_email_check CHECK (email LIKE '%@%');

Удалить constraint:

ALTER TABLE users DROP CONSTRAINT users_email_check;

DROP TABLE

DROP TABLE users;             -- ошибка, если есть FK
DROP TABLE users CASCADE;     -- удалит и зависимые
DROP TABLE IF EXISTS users;   -- без ошибки

⚠️ CASCADE удаляет всё, что ссылается. Осторожно.

TRUNCATE

Быстро удалить все строки:

TRUNCATE users;
TRUNCATE users CASCADE;        -- с зависимостями
TRUNCATE users RESTART IDENTITY; -- сбросить счётчик

Отличие от DELETE:

  • TRUNCATE — быстрее, не пишет WAL по строкам, нельзя WHERE.
  • DELETE — построчно, можно WHERE, откатывается.

CREATE TABLE LIKE

Копия структуры:

CREATE TABLE users_backup (LIKE users INCLUDING ALL);

Опции:

  • INCLUDING DEFAULTS — дефолты.
  • INCLUDING CONSTRAINTS — CHECK.
  • INCLUDING INDEXES — индексы.
  • INCLUDING ALL — всё.

Что даёт: быстрая копия структуры для бэкапа или партиций.

Пример: интернет-магазин

-- Пользователи
CREATE TABLE users (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Категории
CREATE TABLE categories (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    parent_id BIGINT REFERENCES categories(id) ON DELETE SET NULL
);

-- Товары
CREATE TABLE products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_id BIGINT REFERENCES categories(id) ON DELETE RESTRICT,
    sku VARCHAR(64) NOT NULL UNIQUE,
    name TEXT NOT NULL,
    price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Заказы
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    total NUMERIC(12, 2) NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Позиции заказа
CREATE TABLE order_items (
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Просмотр структуры

\d users                    -- структура таблицы
\d+ users                   -- с размерами
\dt                         -- список таблиц
\dt auth.*                  -- таблицы схемы auth
\dn                         -- список схем

SQL:

-- Информационная схема
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users';

-- Размер таблицы
SELECT pg_size_pretty(pg_total_relation_size('users'));

Комментарии

COMMENT ON TABLE users IS 'Пользователи системы';
COMMENT ON COLUMN users.email IS 'Уникальный email';

Просмотр:

SELECT obj_description('users'::regclass, 'pg_class');

Что даёт: документация прямо в схеме.

Частые ошибки

1. Нет PRIMARY KEY:

Нельзя ссылаться через FK, тяжело обновлять

Решение: всегда PK.

2. Серийный id без bigint:

Переполнение на 2.1 млрд

Решение: bigint + GENERATED ALWAYS AS IDENTITY.

3. FK без ON DELETE:

RESTRICT по умолчанию — не удастся удалить пользователя с заказами

Решение: осознанно выбрать CASCADE / SET NULL / RESTRICT.

4. Один большой скрипт без транзакции:

Упало на середине — половина таблиц создана

Решение:

BEGIN;
-- все CREATE TABLE
COMMIT;

5. DROP TABLE без проверки:

Потеря данных

Решение: IF EXISTS, бэкап, транзакция.

6. Изменение типа без USING:

ALTER TABLE users ALTER COLUMN age TYPE integer;  -- ошибка

Решение:

ALTER TABLE users ALTER COLUMN age TYPE integer USING age::integer;

Практика

1. Создайте схему blog:

CREATE SCHEMA blog;
SET search_path TO blog, public;

2. Создайте таблицы:

CREATE TABLE blog.authors (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE blog.posts (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    author_id BIGINT NOT NULL REFERENCES blog.authors(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE blog.comments (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    post_id BIGINT NOT NULL REFERENCES blog.posts(id) ON DELETE CASCADE,
    author_name TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

3. Добавьте updated_at в posts:

ALTER TABLE blog.posts ADD COLUMN updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();

4. Проверьте структуру:

\d blog.posts

Итог

  • 5 constraints: PK, UNIQUE, NOT NULL, CHECK, FK.
  • ON DELETE: CASCADE, SET NULL, RESTRICT.
  • Идентификаторы: bigint + GENERATED ALWAYS AS IDENTITY.
  • Схемы: группировка таблиц.
  • ALTER TABLE — изменения.
  • Транзакции для DDL — обязательно.

Дальше — CRUD-операции.