CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE, схемы
DDL (Data Definition Language) — это язык определения данных, который управляет структурой базы данных. В отличие от DML (Data Manipulation Language), который работает с данными, DDL определяет, как организованы данные.
Основные команды DDL:
CREATE — создание объектовALTER — изменение объектовDROP — удаление объектовTRUNCATE — быстрая очистка данныхRENAME — переименованиеCREATE TABLE table_name (
column_name data_type [CONSTRAINTS],
column_name data_type [CONSTRAINTS],
...
);CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT UNIQUE NOT NULL,
age INTEGER CHECK (age >= 0),
created_at TIMESTAMPTZ DEFAULT NOW()
);Разбор компонентов:
| Компонент | Описание | Пример |
|---|---|---|
PRIMARY KEY | Первичный ключ (уникальность + NOT NULL) | id INTEGER PRIMARY KEY |
NOT NULL | Запрет NULL-значений | name TEXT NOT NULL |
UNIQUE | Уникальность значений | email TEXT UNIQUE |
CHECK | Проверка условия | age INTEGER CHECK (age >= 0) |
DEFAULT | Значение по умолчанию | created_at TIMESTAMPTZ DEFAULT NOW() |
PostgreSQL поддерживает богатый набор типов данных:
| Тип | Размер | Диапазон | Когда использовать |
|---|---|---|---|
SMALLINT | 2 байта | -32768 до 32767 | Маленькие числа, флаги |
INTEGER | 4 байта | -2×10⁹ до 2×10⁹ | Чаще всего |
BIGINT | 8 байт | -9×10¹⁸ до 9×10¹⁸ | Большие счётчики, ID |
DECIMAL(p, s) | Переменный | Точная точность | Деньги, финансы |
NUMERIC(p, s) | Переменный | Точная точность | Деньги, финансы |
REAL | 4 байта | ~6 знаков | Приближённые вычисления |
DOUBLE PRECISION | 8 байт | ~15 знаков | Научные вычисления |
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC(10, 2) NOT NULL, -- 10 цифр всего, 2 после запятой
quantity INTEGER DEFAULT 0,
discount REAL DEFAULT 0.0
);| Тип | Описание | Когда использовать |
|---|---|---|
TEXT | Переменная длина без ограничения | В 99% случаев |
VARCHAR(n) | Переменная длина с лимитом | Когда нужен строгий лимит |
CHAR(n) | Фиксированная длина | Редко (коды, символы) |
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL, -- TEXT предпочтительнее VARCHAR
username VARCHAR(50) NOT NULL, -- Когда нужен лимит
gender CHAR(1) -- Фиксированная длина
);Почему TEXT лучше VARCHAR:
| Тип | Размер | Диапазон | Пример |
|---|---|---|---|
TIMESTAMP | 8 байт | 4713 до н.э. — 5874775 н.э. | 2024-01-15 10:30:00 |
TIMESTAMPTZ | 8 байт | То же + часовой пояс | 2024-01-15 10:30:00+03 |
DATE | 4 байта | 4713 до н.э. — 5874775 н.э. | 2024-01-15 |
TIME | 8 байт | 00:00:00 — 24:00:00 | 10:30:00 |
INTERVAL | 16 байт | Разница времени | 1 day 2 hours |
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_name TEXT NOT NULL,
event_date DATE NOT NULL,
start_time TIME NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(), -- С часовым поясом
duration INTERVAL DEFAULT '1 hour'
);TIMESTAMP vs TIMESTAMPTZ:
TIMESTAMP — хранит «как есть», без часового поясаTIMESTAMPTZ — рекомендуется, автоматически конвертирует в UTCCREATE TABLE tasks (
id SERIAL PRIMARY KEY,
is_completed BOOLEAN DEFAULT FALSE,
is_archived BOOLEAN DEFAULT FALSE
);Значения: TRUE, FALSE, NULL (неизвестно).
-- UUID для глобально уникальных идентификаторов
CREATE TABLE sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);
-- JSONB для полуструктурированных данных
CREATE TABLE products (
id SERIAL PRIMARY KEY,
attributes JSONB
);
-- Массивы
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
tags TEXT[]
);
-- INET для IP-адресов
CREATE TABLE logs (
ip_address INET
);Ограничения обеспечивают целостность данных.
-- Одиночный ключ
CREATE TABLE users (
id INTEGER PRIMARY KEY
);
-- Составной ключ
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
total_amount NUMERIC(10, 2)
);
-- Или явно:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
-- С каскадным удалением
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);Опции FOREIGN KEY:
| Опция | Описание |
|---|---|
ON DELETE CASCADE | Удалить дочерние строки при удалении родительской |
ON DELETE SET NULL | Установить NULL в дочерних строках |
ON DELETE SET DEFAULT | Установить значение по умолчанию |
ON DELETE RESTRICT | Запретить удаление (проверка в конце транзакции) |
ON DELETE NO ACTION | Запретить удаление (проверка сразу) |
-- Уникальность одного столбца
CREATE TABLE users (
email TEXT UNIQUE NOT NULL
);
-- Уникальность комбинации
CREATE TABLE team_members (
team_id INTEGER,
user_id INTEGER,
UNIQUE (team_id, user_id) -- Один пользователь в одной команде
);-- Простая проверка
CREATE TABLE products (
price NUMERIC(10, 2) CHECK (price >= 0),
discount NUMERIC(5, 2) CHECK (discount BETWEEN 0 AND 100)
);
-- Проверка с несколькими условиями
CREATE TABLE users (
age INTEGER CHECK (age >= 18 AND age <= 120),
status TEXT CHECK (status IN ('active', 'inactive', 'pending'))
);
-- Проверка формата email (упрощённая)
CREATE TABLE users (
email TEXT CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$')
);CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL, -- Обязательно
phone TEXT -- Можно NULL
);CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);Поведение:
Когда использовать:
-- Копировать только структуру
CREATE TABLE users_copy (LIKE users INCLUDING ALL);
-- INCLUDING ALL копирует:
-- - Столбцы и типы
-- - Ограничения (CONSTRAINTS)
-- - Индексы
-- - Значения по умолчанию (DEFAULT)
-- INCLUDING без ALL:
CREATE TABLE users_copy (LIKE users); -- Только столбцы
CREATE TABLE users_copy (LIKE users INCLUDING DEFAULTS); -- + DEFAULT
CREATE TABLE users_copy (LIKE users INCLUDING CONSTRAINTS); -- + CONSTRAINTS
CREATE TABLE users_copy (LIKE users INCLUDING INDEXES); -- + индексы
-- Копировать структуру и данные
CREATE TABLE users_backup AS SELECT * FROM users;
-- Копировать структуру и часть данных
CREATE TABLE active_users_backup AS
SELECT * FROM users WHERE is_active = true;
-- Копировать только структуру (через LIMIT 0)
CREATE TABLE users_empty AS SELECT * FROM users LIMIT 0;-- Временная таблица (видна только в сессии)
CREATE TEMP TABLE temp_results (
id INTEGER,
value TEXT
);
-- С автоматическим удалением после транзакции
CREATE TEMP TABLE temp_results (
id INTEGER
) ON COMMIT DROP;
-- С сохранением строк между транзакциями (по умолчанию)
CREATE TEMP TABLE temp_results (
id INTEGER
) ON COMMIT PRESERVE ROWS;
-- Использование
INSERT INTO temp_results VALUES (1, 'test');
SELECT * FROM temp_results; -- Работает
-- После завершения сессии таблица удаляется автоматическиКогда использовать временные таблицы:
-- Добавить nullable столбец
ALTER TABLE users ADD COLUMN phone TEXT;
-- Добавить с DEFAULT
ALTER TABLE users ADD COLUMN age INTEGER DEFAULT 0;
-- Добавить с NOT NULL (только с DEFAULT)
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active' NOT NULL;
-- Добавить с проверкой
ALTER TABLE users ADD COLUMN age INTEGER CHECK (age >= 0);
-- Добавить несколько столбцов
ALTER TABLE users
ADD COLUMN phone TEXT,
ADD COLUMN address TEXT;Производительность:
| Операция | PostgreSQL < 11 | PostgreSQL 11+ |
|---|---|---|
ADD COLUMN (без DEFAULT) | Быстро | Быстро |
ADD COLUMN (с DEFAULT) | Медленно (перезапись) | Быстро (метаданные) |
ADD COLUMN NOT NULL | Медленно | Быстро (с DEFAULT) |
-- ✅ Быстро в PostgreSQL 11+
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active' NOT NULL;
-- ❌ Медленно на больших таблицах (любая версия)
ALTER TABLE users ADD COLUMN description TEXT DEFAULT '...';
-- Заполняет все существующие строки-- Удалить столбец
ALTER TABLE users DROP COLUMN phone;
-- Если столбец может не существовать
ALTER TABLE users DROP COLUMN IF EXISTS phone;
-- С каскадным удалением зависимостей
ALTER TABLE users DROP COLUMN phone CASCADE;Производительность:
DROP COLUMN не удаляет данные физически сразуVACUUM FULL-- Для немедленного освобождения места
ALTER TABLE users DROP COLUMN phone;
VACUUM FULL users;-- Простое изменение типа (совместимые типы)
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;
-- С преобразованием (несовместимые типы)
ALTER TABLE users ALTER COLUMN age TYPE INTEGER USING age::INTEGER;
-- Сложное преобразование
ALTER TABLE users ALTER COLUMN price TYPE NUMERIC(10, 2)
USING price::NUMERIC(10, 2);
-- Изменение типа с выражением
ALTER TABLE users ALTER COLUMN full_name TYPE TEXT
USING first_name || ' ' || last_name;Совместимые типы (быстрое изменение):
INTEGER → BIGINTVARCHAR(n) → TEXTTIMESTAMP → TIMESTAMPTZНесовместимые типы (требуют USING):
TEXT → INTEGERFLOAT → NUMERICJSON → JSONB-- Установить DEFAULT
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
-- Удалить DEFAULT
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;
-- DEFAULT с выражением
ALTER TABLE orders ALTER COLUMN created_at
SET DEFAULT NOW();Важно: Изменение DEFAULT влияет только на новые строки.
-- Установить NOT NULL (только если нет NULL-значений)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
-- Разрешить NULL
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;-- Если есть NULL-значения, нужно обработать:
UPDATE users SET phone = 'unknown' WHERE phone IS NULL;
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;-- Переименовать таблицу
ALTER TABLE users RENAME TO customers;
-- Переименовать столбец
ALTER TABLE users RENAME COLUMN name TO full_name;
-- Переименовать ограничение
ALTER TABLE users RENAME CONSTRAINT users_pkey TO customers_pkey;
-- Переименовать схему
ALTER SCHEMA sales RENAME TO ecommerce;Важно: Переименование не обновляет ссылки в коде приложения.
-- Добавить ограничение
ALTER TABLE users ADD CONSTRAINT check_age CHECK (age >= 18);
-- Удалить ограничение
ALTER TABLE users DROP CONSTRAINT check_age;
-- Если ограничение может не существовать
ALTER TABLE users DROP CONSTRAINT IF EXISTS check_age;
-- Добавить внешний ключ
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id);
-- Удалить внешний ключ
ALTER TABLE orders DROP CONSTRAINT fk_orders_user;-- Изменить владельца
ALTER TABLE users OWNER TO new_owner;
-- Переместить в другую схему
ALTER TABLE users SET SCHEMA new_schema;
-- Изменить схему и владельца
ALTER TABLE users SET SCHEMA new_schema;
ALTER TABLE users OWNER TO new_owner;-- Удалить таблицу
DROP TABLE users;
-- Если существует
DROP TABLE IF EXISTS users;
-- Удалить несколько таблиц
DROP TABLE IF EXISTS users, orders, products;-- С каскадным удалением зависимостей
DROP TABLE users CASCADE;CASCADE удаляет:
-- Пример зависимостей
CREATE TABLE users (...);
CREATE TABLE orders (user_id INTEGER REFERENCES users(id));
CREATE VIEW active_users AS SELECT * FROM users WHERE is_active = true;
-- DROP TABLE users; -- ❌ Ошибка: есть зависимости
-- DROP TABLE users CASCADE; -- ✅ Удалит users, orders (FK), active_users (VIEW)-- Найти зависимости таблицы
SELECT
dependent_ns.nspname AS dependent_schema,
dependent.relname AS dependent_table,
source_ns.nspname AS source_schema,
source.relname AS source_table
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent ON pg_rewrite.ev_class = dependent.oid
JOIN pg_class AS source ON pg_depend.refobjid = source.oid
JOIN pg_namespace AS dependent_ns ON dependent.relnamespace = dependent_ns.oid
JOIN pg_namespace AS source_ns ON source.relnamespace = source_ns.oid
WHERE source.relname = 'users' AND source_ns.nspname = 'public';-- Очистить таблицу
TRUNCATE TABLE users;
-- Несколько таблиц
TRUNCATE TABLE users, orders;
-- С каскадом (для связанных таблиц)
TRUNCATE TABLE users CASCADE;
-- С перезапуском последовательностей
TRUNCATE TABLE users RESTART IDENTITY;| Характеристика | TRUNCATE | DELETE |
|---|---|---|
| Скорость | 🚀 Очень быстро | 🐌 Медленно |
| Логирование | Минимальное | Полное (каждая строка) |
| WHERE | ❌ Нельзя | ✅ Можно |
| RETURNING | ❌ Нельзя | ✅ Можно |
| Триггеры | ❌ Не вызывает | ✅ Вызывает (FOR EACH ROW) |
| Внешние ключи | ⚠️ Требует CASCADE | ✅ Работает нормально |
| Разрешения | Требуется DROP | Требуется DELETE |
| MVCC | Освобождает место сразу | Место остаётся до VACUUM |
-- ✅ Очистка логов
TRUNCATE TABLE audit_logs;
-- ✅ Сброс тестовых данных
TRUNCATE TABLE test_users RESTART IDENTITY;
-- ✅ Очистка кэша
TRUNCATE TABLE cache_entries;
-- ❌ Когда нужно условие
DELETE FROM users WHERE is_inactive = true;
-- ❌ Когда нужны удалённые строки
DELETE FROM users WHERE id = 1 RETURNING *;
-- ❌ Когда есть триггеры аудита
DELETE FROM orders WHERE id = 1; -- Вызовет триггер-- TRUNCATE: мгновенно (только метаданные)
TRUNCATE TABLE large_table;
-- DELETE: медленно (сканирует все строки)
DELETE FROM large_table;
-- DELETE с WHERE: ещё медленнее
DELETE FROM large_table WHERE created_at < '2024-01-01';Схема — это пространство имён для объектов базы данных (таблиц, представлений, функций и т.д.).
Аналогия:
-- Простое создание
CREATE SCHEMA sales;
-- С указанием владельца
CREATE SCHEMA sales AUTHORIZATION app_user;
-- Создать схему и таблицу в одной команде
CREATE SCHEMA marketing AUTHORIZATION marketing_user;
CREATE TABLE marketing.campaigns (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);-- Полное имя объекта: schema.table
SELECT * FROM sales.orders;
-- Создать таблицу в схеме
CREATE TABLE sales.customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
-- Установить search_path (порядок поиска схем)
SET search_path TO sales, public;
-- Теперь можно обращаться без схемы
SELECT * FROM orders; -- Ищет в sales, затем public
-- Проверить текущий search_path
SHOW search_path;-- Список всех схем
SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;
-- Или через psql
\dn
-- Таблицы в конкретной схеме
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'sales';
-- Или через psql
\dt sales.*
-- Все таблицы во всех схемах
\dt *.*| Схема | Описание |
|---|---|
pg_catalog | Системные таблицы PostgreSQL |
information_schema | Стандартные представления SQL |
public | Схема по умолчанию для пользовательских объектов |
pg_toast | Внутренние данные TOAST |
-- Переименовать схему
ALTER SCHEMA sales RENAME TO ecommerce;
-- Изменить владельца
ALTER SCHEMA sales OWNER TO new_owner;
-- Удалить схему
DROP SCHEMA IF EXISTS sales;
-- Удалить схему со всеми объектами
DROP SCHEMA IF EXISTS sales CASCADE;-- ✅ Разделение по доменам
CREATE SCHEMA users;
CREATE SCHEMA orders;
CREATE SCHEMA analytics;
-- ✅ Разделение по окружениям
CREATE SCHEMA prod;
CREATE SCHEMA staging;
CREATE SCHEMA dev;
-- ✅ Разделение по клиентам (multi-tenant)
CREATE SCHEMA client_1;
CREATE SCHEMA client_2;
-- ❌ Не создавайте слишком много схем без необходимости⚠️ Примечание: Наследование таблиц считается устаревшим. Для новых проектов используйте декларативное партиционирование (
PARTITION BY).
-- Родительская таблица
CREATE TABLE cities (
name TEXT,
population REAL
);
-- Дочерняя таблица наследует столбцы
CREATE TABLE capitals (
is_capital BOOLEAN
) INHERITS (cities);
-- capitals теперь имеет: name, population, is_capital-- Запрос к родительской таблице включает дочерние
SELECT * FROM cities; -- Включает capitals
-- Только родительская (без дочерних)
SELECT * FROM ONLY cities;
-- Вставка в дочернюю
INSERT INTO capitals (name, population, is_capital)
VALUES ('Moscow', 12000000, true);
-- Ограничения не наследуются автоматически
ALTER TABLE cities ADD CHECK (population > 0); -- Только cities
ALTER TABLE capitals ADD CHECK (population > 0); -- Нужно отдельно-- Декларативное партиционирование (PostgreSQL 10+)
CREATE TABLE measurements (
id SERIAL,
log_date DATE NOT NULL,
value INTEGER
) PARTITION BY RANGE (log_date);
-- Создание партиций
CREATE TABLE measurements_2024_q1
PARTITION OF measurements
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE measurements_2024_q2
PARTITION OF measurements
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');-- Создать схему
CREATE SCHEMA IF NOT EXISTS app;
-- Таблица пользователей
CREATE TABLE app.users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
is_active BOOLEAN DEFAULT TRUE,
is_verified BOOLEAN DEFAULT FALSE,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT check_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
CONSTRAINT check_password_length CHECK (length(password_hash) >= 60)
);
-- Таблица заказов
CREATE TABLE app.orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES app.users(id) ON DELETE CASCADE,
total_amount NUMERIC(10, 2) NOT NULL DEFAULT 0,
status TEXT NOT NULL DEFAULT 'pending',
created_at TIMESTAMPTZ DEFAULT NOW(),
CONSTRAINT check_total_amount CHECK (total_amount >= 0),
CONSTRAINT check_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);
-- Таблица элементов заказа
CREATE TABLE app.order_items (
order_id BIGINT REFERENCES app.orders(id) ON DELETE CASCADE,
product_id BIGINT NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1,
price NUMERIC(10, 2) NOT NULL,
PRIMARY KEY (order_id, product_id),
CONSTRAINT check_quantity CHECK (quantity > 0),
CONSTRAINT check_price CHECK (price >= 0)
);
-- Индексы
CREATE INDEX idx_users_email ON app.users(email);
CREATE INDEX idx_orders_user_id ON app.orders(user_id);
CREATE INDEX idx_orders_status ON app.orders(status);
CREATE INDEX idx_orders_created_at ON app.orders(created_at);
-- Триггер для updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER users_updated_at
BEFORE UPDATE ON app.users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();-- Добавить новый столбец
ALTER TABLE users ADD COLUMN phone TEXT;
-- Заполнить существующие строки
UPDATE users SET phone = '+0000000000' WHERE phone IS NULL;
-- Установить NOT NULL
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;
-- Добавить индекс
CREATE INDEX idx_users_phone ON users(phone);
-- Добавить проверку формата
ALTER TABLE users ADD CONSTRAINT check_phone_format
CHECK (phone ~ '^\+?[0-9]{10,15}$');-- Создать резервную копию структуры
CREATE TABLE users_backup (LIKE users INCLUDING ALL);
-- Создать резервную копию структуры и данных
CREATE TABLE users_backup AS SELECT * FROM users;
-- Создать резервную копию с добавлением метаданных
CREATE TABLE users_backup AS
SELECT *, NOW() AS backed_up_at FROM users;-- Создать архивную таблицу
CREATE TABLE users_archive (LIKE users INCLUDING ALL);
-- Переместить неактивных пользователей
INSERT INTO users_archive
SELECT * FROM users WHERE is_active = false;
DELETE FROM users WHERE is_active = false;
-- Или использовать TRUNCATE + INSERT для больших объёмов
TRUNCATE users_archive;
INSERT INTO users_archive
SELECT * FROM users WHERE is_active = false;-- Создать схему для эксперимента
CREATE SCHEMA experiment_v2;
-- Создать изменённую таблицу
CREATE TABLE experiment_v2.users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
new_feature_enabled BOOLEAN DEFAULT false, -- Новое поле
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Переключить приложение на новую схему
SET search_path TO experiment_v2, public;-- ⚠️ Блокировки при DDL
-- ADD COLUMN с DEFAULT (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';
-- ACCESS EXCLUSIVE lock (кратковременно)
-- ADD COLUMN без DEFAULT
ALTER TABLE users ADD COLUMN description TEXT;
-- ACCESS EXCLUSIVE lock (дольше на больших таблицах)
-- CREATE INDEX CONCURRENTLY (без блокировки записи)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- SHARE lock (не блокирует запись)
-- Обычный CREATE INDEX
CREATE INDEX idx_users_email ON users(email);
-- ACCESS EXCLUSIVE lock-- ✅ Использовать CONCURRENTLY для индексов в продакшене
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- ✅ Выполнять DDL в транзакции
BEGIN;
ALTER TABLE users ADD COLUMN phone TEXT;
ALTER TABLE users ADD CONSTRAINT check_phone CHECK (...);
COMMIT;
-- ✅ Проверять влияние через EXPLAIN
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'test';
-- ✅ Использовать maintenance_work_mem для больших операций
SET maintenance_work_mem = '4GB';
CREATE INDEX idx_large_table ON large_table(column);-- ❌ Плохо: столбец может быть NULL
CREATE TABLE users (
email TEXT -- Может быть NULL!
);
-- ✅ Хорошо: явное ограничение
CREATE TABLE users (
email TEXT NOT NULL
);-- ❌ Плохо: FLOAT для денег (потеря точности)
CREATE TABLE orders (
amount FLOAT
);
-- ✅ Хорошо: NUMERIC/DECIMAL для точности
CREATE TABLE orders (
amount NUMERIC(10, 2)
);-- ❌ Плохо: нет индекса на FK
CREATE TABLE orders (
user_id INTEGER REFERENCES users(id)
);
-- ✅ Хорошо: индекс для JOIN
CREATE TABLE orders (
user_id INTEGER REFERENCES users(id)
);
CREATE INDEX idx_orders_user_id ON orders(user_id);-- ❌ Ошибка
ALTER TABLE users ALTER COLUMN age TYPE INTEGER;
-- Если age был TEXT
-- ✅ Правильно
ALTER TABLE users ALTER COLUMN age TYPE INTEGER USING age::INTEGER;-- ❌ Ошибка, если таблица не существует
DROP TABLE users;
-- ✅ Безопасно
DROP TABLE IF EXISTS users;Перед созданием таблицы проверьте:
Готовы проверить знания? Переходите к вопросам!
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.