Типы данных, создание таблиц, связи между таблицами, ограничения целостности
Хорошая схема базы данных — фундамент надёжного приложения. В этой теме вы изучите типы данных PostgreSQL, создание таблиц, связи между таблицами и ограничения целостности.
PostgreSQL поддерживает богатую коллекцию типов данных. Вот наиболее часто используемые:
| Тип | Размер | Диапазон | Описание |
|---|---|---|---|
SMALLINT | 2 байта | -32768 до +32767 | Малое целое |
INTEGER | 4 байта | -2×10⁹ до +2×10⁹ | Стандартное целое |
BIGINT | 8 байт | -9×10¹⁸ до +9×10¹⁸ | Большое целое |
NUMERIC(p, s) | переменный | произвольный | Точное число с фиксированной точностью |
REAL | 4 байта | ~6 знаков | Число с плавающей точкой |
DOUBLE PRECISION | 8 байт | ~15 знаков | Double-precision float |
-- Примеры использования
CREATE TABLE products (
id INTEGER PRIMARY KEY,
quantity SMALLINT NOT NULL, -- небольшое число
price NUMERIC(10, 2) NOT NULL, -- точные деньги
rating REAL -- приблизительная оценка
);Важно: Для денежных значений всегда используйте
NUMERIC, а неREAL/DOUBLE PRECISION. Числа с плавающей точкой могут давать ошибки округления.
| Тип | Описание |
|---|---|
CHAR(n) | Фиксированная длина, дополняется пробелами |
VARCHAR(n) | Переменная длина с ограничением |
TEXT | Переменная длина без ограничения |
CREATE TABLE users (
username VARCHAR(50) NOT NULL, -- ограничение длины
email TEXT NOT NULL, -- без ограничения
status CHAR(1) DEFAULT 'A' -- фиксированная длина
);Рекомендация: В PostgreSQL
TEXTиVARCHARбез ограничения имеют одинаковую производительность. ИспользуйтеTEXT, если не нужно ограничение длины на уровне БД.
| Тип | Размер | Описание |
|---|---|---|
DATE | 4 байта | Дата (год, месяц, день) |
TIME | 8 байт | Время (часы, минуты, секунды) |
TIMESTAMP | 8 байт | Дата и время |
TIMESTAMPTZ | 8 байт | Дата и время с тайм-зоной |
INTERVAL | 16 байт | Разница между датами |
CREATE TABLE events (
id SERIAL PRIMARY KEY,
event_date DATE NOT NULL,
event_time TIME,
created_at TIMESTAMP DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
duration INTERVAL
);
-- Примеры операций с датами
SELECT
NOW() as current_timestamp,
NOW()::DATE as current_date,
INTERVAL '1 day' as one_day,
NOW() + INTERVAL '7 days' as next_week;Важно: Всегда используйте
TIMESTAMPTZ(timestamp with time zone) для хранения времени в многопользовательских приложениях. Это избавит от проблем с часовыми поясами.
CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
is_completed BOOLEAN DEFAULT FALSE,
is_archived BOOLEAN DEFAULT FALSE
);
-- BOOLEAN принимает: TRUE, FALSE, NULL
-- При вводе: 't', 'true', 'y', 'yes', 'on', '1' → TRUE
-- 'f', 'false', 'n', 'no', 'off', '0' → FALSECREATE TABLE documents (
id SERIAL PRIMARY KEY,
metadata JSON, -- JSON как текст
properties JSONB -- JSON в бинарном формате
);| Тип | Описание |
|---|---|
JSON | Хранит JSON как текст, валидация при вставке |
JSONB | Бинарный формат, быстрее для запросов, поддерживает индексы |
Рекомендация: Используйте
JSONBпочти всегда. Он быстрее для чтения и поддерживает индексацию.JSONимеет смысл только для частой записи.
-- Запросы к JSONB
INSERT INTO documents (properties)
VALUES ('{"color": "red", "size": "large", "tags": ["sale", "new"]}');
-- Извлечение значения
SELECT properties->>'color' FROM documents; -- "red"
-- Фильтрация по JSONB
SELECT * FROM documents
WHERE properties->>'color' = 'red';
-- Проверка наличия ключа
SELECT * FROM documents
WHERE properties ? 'tags';
-- Проверка вхождения значения
SELECT * FROM documents
WHERE properties->'tags' ? 'sale';PostgreSQL поддерживает массивы любых типов:
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
tags TEXT[], -- массив строк
ratings INTEGER[] -- массив целых
);
-- Вставка массива
INSERT INTO articles (title, tags, ratings)
VALUES ('PostgreSQL Guide', ARRAY['sql', 'database'], ARRAY[5, 4, 5]);
-- Или синтаксис с фигурными скобками
INSERT INTO articles (title, tags)
VALUES ('Another Article', '{"postgres", "tutorial"}');
-- Доступ к элементам
SELECT tags[1] FROM articles; -- первый элемент
-- Поиск по массиву
SELECT * FROM articles
WHERE 'sql' = ANY(tags); -- содержит 'sql'-- Создание типа ENUM
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
-- Использование в таблице
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
status order_status DEFAULT 'pending',
created_at TIMESTAMP DEFAULT NOW()
);
-- Использование
UPDATE orders SET status = 'processing' WHERE id = 1;
-- Получение всех значений ENUM
SELECT unnest(enum_range(NULL::order_status));CREATE TABLE table_name (
column_name data_type [CONSTRAINTS],
...
);CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email TEXT NOT NULL,
password_hash TEXT NOT NULL,
first_name TEXT,
last_name TEXT,
age INTEGER CHECK (age >= 0 AND age < 150),
is_active BOOLEAN DEFAULT TRUE,
role VARCHAR(20) DEFAULT 'user',
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW(),
-- Ограничение на длину username
CONSTRAINT username_length CHECK (length(username) >= 3),
-- Ограничение на допустимые роли
CONSTRAINT valid_role CHECK (role IN ('user', 'admin', 'moderator'))
);-- Таблица существует только в рамках сессии
CREATE TEMP TABLE temp_results (
id INTEGER,
value TEXT
);
-- Автоматически удаляется при завершении сессии-- Родительская таблица
CREATE TABLE base_table (
id SERIAL PRIMARY KEY,
created_at TIMESTAMP DEFAULT NOW()
);
-- Дочерняя таблица наследует столбцы
CREATE TABLE child_table (
extra_column TEXT
) INHERITS (base_table);-- Пользователь может иметь много заказов
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id),
total NUMERIC(10, 2),
created_at TIMESTAMP DEFAULT NOW()
);-- Студенты и курсы: один студент может записаться на много курсов,
-- один курс может иметь много студентов
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL
);
-- Связующая таблица
CREATE TABLE enrollments (
student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
course_id INTEGER NOT NULL REFERENCES courses(id) ON DELETE CASCADE,
enrolled_at TIMESTAMP DEFAULT NOW(),
PRIMARY KEY (student_id, course_id)
);-- Профиль пользователя (дополнительная информация)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL
);
CREATE TABLE user_profiles (
user_id INTEGER PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
bio TEXT,
avatar_url TEXT,
birth_date DATE
);-- Одиночный ключ
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,
CONSTRAINT fk_orders_user
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE -- при удалении пользователя удалить заказы
ON UPDATE CASCADE -- при изменении id пользователя обновить заказы
);| Действие | Описание |
|---|---|
CASCADE | Каскадное удаление/обновление |
RESTRICT | Запретить удаление (по умолчанию) |
SET NULL | Установить NULL при удалении родителя |
SET DEFAULT | Установить значение по умолчанию |
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT UNIQUE, -- столбец уникален
username TEXT,
UNIQUE (email, username) -- комбинация уникальна
);CREATE TABLE products (
id SERIAL PRIMARY KEY,
price NUMERIC(10, 2) CHECK (price > 0),
discount NUMERIC(5, 2) CHECK (discount >= 0 AND discount <= 100),
status TEXT CHECK (status IN ('active', 'inactive', 'archived'))
);CREATE TABLE users (
id SERIAL PRIMARY KEY,
email TEXT NOT NULL, -- обязательно для заполнения
name TEXT -- может быть NULL
);PostgreSQL автоматически создаёт индексы для:
PRIMARY KEY — уникальный B-tree индексUNIQUE — уникальный B-tree индексFOREIGN KEY — не создаётся автоматически! (нужно создавать вручную для производительности)-- Рекомендуется создавать индексы для FK
CREATE TABLE orders (
user_id INTEGER REFERENCES users(id)
);
CREATE INDEX idx_orders_user_id ON orders(user_id);-- Плохо
CREATE TABLE t1 (c1 int, c2 text);
-- Хорошо
CREATE TABLE customers (
customer_id int PRIMARY KEY,
customer_name text NOT NULL
);CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ DEFAULT NOW()
);
-- Триггер для автообновления 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 trg_articles_updated_at
BEFORE UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();-- Плохо: дублирование данных
CREATE TABLE orders (
id SERIAL,
user_name TEXT, -- дублирует users.name
user_email TEXT, -- дублирует users.email
...
);
-- Хорошо: ссылка через FK
CREATE TABLE orders (
id SERIAL,
user_id INTEGER REFERENCES users(id),
...
);-- Используйте BIGINT для счётчиков, которые могут вырасти
CREATE TABLE events (
id BIGSERIAL PRIMARY KEY, -- не SERIAL, если ожидается много событий
...
);Теперь вы умеете создавать таблицы и связи. В следующей теме вы изучите нормализацию — принципы организации данных для минимизации избыточности и улучшения целостности.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.