Типы данных: числа, строки, даты

Типы данных: числа, строки, даты

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

Числовые типы

Целые числа:

Правило выбора: для большинства задач подходит INTEGER. BIGINT нужен для id в высоконагруженных системах (более 2 млрд строк) или для денежных сумм в копейках/центах. SMALLINT — для статусов, кодов, маленьких счётчиков.

Числа с автоинкрементом (для id):

id SERIAL           -- INTEGER + автоматическая последовательность
id BIGSERIAL        -- BIGINT + автоматическая последовательность
id INTEGER GENERATED ALWAYS AS IDENTITY   -- SQL-стандарт (PostgreSQL 10+)
id BIGINT GENERATED ALWAYS AS IDENTITY

Числа с дробной частью:

NUMERIC(precision, scale) (или DECIMAL) — точный тип. Хранит числа точно, без ошибок округления.

price    NUMERIC(10, 2)  -- до 10 цифр всего, 2 после запятой: 99999999.99
tax_rate NUMERIC(5, 4)   -- 1.2500 (25%)

Используйте NUMERIC для денег, процентных ставок, любых расчётов, где точность критична.

REAL и DOUBLE PRECISION — числа с плавающей точкой. Быстрее NUMERIC, но не точные:

SELECT 0.1::DOUBLE PRECISION + 0.2::DOUBLE PRECISION;
-- Результат: 0.30000000000000004 (ошибка представления)

SELECT 0.1::NUMERIC + 0.2::NUMERIC;
-- Результат: 0.3 (точно)

Никогда не используйте REAL/DOUBLE PRECISION для денег. Для аппроксимаций (координаты, научные расчёты) — нормально.

Строковые типы

PostgreSQL предоставляет три строковых типа:

TEXT — строка произвольной длины. Это рекомендуемый тип для строк в PostgreSQL: нет лимита на длину, нет потерь производительности от неполной заполненности.

bio     TEXT,    -- описание профиля, любой длины
content TEXT     -- текст статьи

VARCHAR(n) — строка максимальной длины n. Если вставить строку длиннее n — ошибка. Если короче — хранится как есть (без дополнения пробелами).

username    VARCHAR(50),   -- максимум 50 символов
country_code VARCHAR(2)    -- ровно/максимум 2 символа (RU, US)

CHAR(n) — строка фиксированной длины n. Если значение короче — дополняется пробелами до n. Это устаревший тип, почти не используется в новом коде.

Что выбрать? В PostgreSQL TEXT и VARCHAR одинаково эффективны. Если есть бизнес-ограничение на длину (username не может быть длиннее 50 символов) — используйте VARCHAR(n): СУБД защитит от ошибочно длинных данных. Для свободного текста — TEXT.

Проверь себя: почему не стоит использовать CHAR(50) для хранения имени пользователя?

Типы для дат и времени

DATE — только дата, без времени: 2024-03-15. Диапазон: 4713 г. до н.э. — 5874897 г. н.э. Для дней рождения, дат событий без привязки ко времени.

TIME — только время без даты: 14:30:00. Редко используется без привязки к дате.

TIMESTAMP — дата и время без часового пояса. Хранит «наивное» время, не учитывает летнее/зимнее время.

TIMESTAMPTZ — дата и время с часовым поясом. PostgreSQL хранит в UTC, отображает в локальном часовом поясе подключения. Рекомендуемый тип для временных меток в большинстве приложений.

created_at  TIMESTAMPTZ DEFAULT NOW(),
birth_date  DATE,
start_time  TIME

Разница между TIMESTAMP и TIMESTAMPTZ:

-- В сессии с часовым поясом Europe/Moscow (UTC+3):
SELECT '2024-01-15 10:00:00'::TIMESTAMP;
-- 2024-01-15 10:00:00 (как есть, без конвертации)

SELECT '2024-01-15 10:00:00'::TIMESTAMPTZ;
-- 2024-01-15 07:00:00+00 (конвертируется в UTC при хранении)

Для баз данных с пользователями из разных часовых поясов — всегда TIMESTAMPTZ.

INTERVAL — промежуток времени:

INTERVAL '7 days'
INTERVAL '1 hour 30 minutes'
INTERVAL '1 month'

Используется для арифметики с датами: NOW() + INTERVAL '30 days', expires_at - created_at.

Логический тип

is_active   BOOLEAN DEFAULT TRUE,
is_verified BOOLEAN DEFAULT FALSE

Значения: TRUE/FALSE (или 't'/'f', 1/0 при вводе). NULL тоже допустим для трёхзначной логики. В PostgreSQL BOOLEAN — однобайтовый тип.

UUID

UUID — 128-битный уникальный идентификатор вида 550e8400-e29b-41d4-a716-446655440000.

id UUID DEFAULT gen_random_uuid() PRIMARY KEY

Преимущество перед SERIAL: уникален глобально, не требует централизованного генератора. Удобно при горизонтальном масштабировании или репликации. gen_random_uuid() — встроенная PostgreSQL-функция (v14+, ранее требовала расширение pgcrypto).

Недостаток: занимает больше места (16 байт vs 4/8 для integer), хуже сжимается в индексах.

JSON и JSONB

PostgreSQL поддерживает хранение JSON-документов:

• JSON — хранит как текст, индексирование невозможно
• JSONB — хранит в бинарном формате, поддерживает индексы

metadata JSONB,
settings JSONB DEFAULT '{}'

JSONB — рекомендуемый тип. Полезен для хранения атрибутов, которые часто меняются по структуре. Но не злоупотребляйте: реляционные данные лучше хранить в отдельных колонках — это проще индексировать, валидировать и запрашивать.

Массивы

PostgreSQL поддерживает массивы любого типа:

tags     TEXT[],
scores   INTEGER[],

-- Вставка массива
INSERT INTO posts (title, tags) VALUES ('SQL', ARRAY['database', 'sql', 'postgresql']);

-- Запрос по элементу массива
SELECT * FROM posts WHERE 'sql' = ANY(tags);

Удобно для тегов, списков. Для сложных структур лучше создать отдельную таблицу.

Типичные ошибки при выборе типов

1. VARCHAR вместо TEXT без реального ограничения:

-- Избыточно: нет смысла в ограничении 1000 для произвольного текста
content VARCHAR(1000)
-- Лучше
content TEXT

2. FLOAT для денег:

-- Опасно: ошибки округления
price FLOAT
-- Правильно
price NUMERIC(10, 2)

3. VARCHAR(255) по привычке из MySQL: В PostgreSQL VARCHAR(255) не быстрее TEXT. Это MySQL-паттерн, который не нужен в PostgreSQL.

4. TIMESTAMP вместо TIMESTAMPTZ в приложениях с часовыми поясами:

-- Может вызвать проблемы при смене часового пояса сервера
created_at TIMESTAMP
-- Рекомендуется
created_at TIMESTAMPTZ

Приведение типов (CAST)

Иногда нужно явно преобразовать значение из одного типа в другой:

-- Синтаксис CAST
SELECT CAST('42' AS INTEGER);
SELECT CAST(price AS TEXT) FROM products;

-- PostgreSQL-краткий синтаксис ::
SELECT '42'::INTEGER;
SELECT price::TEXT FROM products;
SELECT NOW()::DATE;    -- из TIMESTAMPTZ получить только DATE

PostgreSQL часто делает неявное приведение (coercion), но явное :: делает намерение понятным. Ошибка при неудачном приведении:

SELECT 'abc'::INTEGER;
-- ERROR: invalid input syntax for type integer: "abc"

Выбор типа для id: INTEGER vs BIGINT vs UUID

Практическое руководство:

• SERIAL / INTEGER GENERATED AS IDENTITY — для небольших таблиц (до сотен миллионов строк). 99% приложений.
• BIGSERIAL / BIGINT GENERATED AS IDENTITY — для систем с большими объёмами данных или очень быстрой вставкой.
• UUID — когда id должны быть уникальными между несколькими БД, при репликации или микросервисной архитектуре.

Не оптимизируйте преждевременно: начните с SERIAL, мигрируйте на BIGSERIAL или UUID когда понадобится.

Краткий итог

• INTEGER / BIGINT — для целых чисел; NUMERIC(p,s) — для денег и точных дробных
• TEXT — для строк без ограничения; VARCHAR(n) — с ограничением на длину
• TIMESTAMPTZ — рекомендуется для временных меток; DATE — для дат без времени
• BOOLEAN для флагов; UUID для глобально уникальных id; JSONB для гибких атрибутов
• Никогда FLOAT/REAL для денег — только NUMERIC

Типы данных в других СУБД

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

Имена разные, суть одна. При миграции между СУБД типы нужно отображать вручную.

Что дальше

Вы знаете типы данных. Следующий урок — первичные и внешние ключи: как правильно объявить PRIMARY KEY и FOREIGN KEY, что такое ссылочная целостность и как настроить каскадные действия.