Типы данных SQL и PostgreSQL: таблица с примерами

Коротко

• Целые числа и идентификаторы — integer или bigint, если строк может стать больше двух миллиардов.
• Деньги — numeric(12, 2): точные десятичные дроби. double precision — для измерений, где важна скорость, а не копейка.
• Строки — text или varchar(n). В PostgreSQL они одинаково быстрые, n нужен только как ограничение длины.
• Моменты времени — timestamptz, календарные дни — date.
• Новый ключ-счётчик — bigint generated always as identity вместо устаревшего serial.

Сводная таблица типов PostgreSQL

В таблице — типы, которые встречаются в рабочих базах продукта. PostgreSQL знает больше (геометрию, сетевые адреса, диапазоны), но аналитик сталкивается с ними редко.

Целые числа: integer или bigint

Для подсчётов и небольших справочников хватает integer. Для идентификаторов событий, заказов и всего, что растёт каждый день, берите bigint сразу. Переполнение integer — не теория: на 2 147 483 648-й строке вставка падает с ошибкой «integer out of range», а поменять тип у таблицы в миллиарды строк — это часы блокировки.

Аналитику переполнение встречается и в расчётах: сумма большого числа значений integer в PostgreSQL возвращается как bigint, но умножение двух integer остаётся integer и может не поместиться. Если перемножаете большие счётчики, приведите один множитель к bigint или numeric.

NUMERIC против DOUBLE PRECISION

double precision хранит число в двоичной дроби, и большинство десятичных дробей в ней представимы только приблизительно. Поэтому 0,1 + 0,2 в double не равно 0,3 — разница в шестнадцатом знаке. На одной операции это незаметно, на сумме миллиона платежей копейки расходятся с бухгалтерией.

numeric хранит десятичные цифры точно. Он медленнее на тяжёлых вычислениях, но для денег, процентов комиссии и всего, что сверяют с внешним отчётом, выбора нет. Запись numeric(12, 2) значит: всего до 12 цифр, из них 2 после запятой.

select
  0.1::double precision + 0.2::double precision = 0.3 as double_equal,
  0.1::numeric(10, 2) + 0.2::numeric(10, 2) = 0.3     as numeric_equal;

VARCHAR, TEXT и CHAR

В PostgreSQL text и varchar(n) хранятся одинаково и работают с одной скоростью. Документация прямо говорит, что разницы в производительности между ними нет. varchar(n) нужен, когда ограничение длины — часть правил данных: код страны из двух букв, номер телефона. varchar без числа — то же самое, что text.

У ограничения есть ловушка. Вставка слишком длинной строки в varchar(3) падает с ошибкой, а явное приведение 'abcd'::varchar(3) молча обрезает строку до abc. Если очищаете данные через CAST, длинные значения потеряют хвост без предупреждения.

char(n) дополняет строку пробелами до длины n. При сравнении и склейке PostgreSQL эти пробелы игнорирует, а в выгрузке и при подсчёте байт они есть. Новых причин выбирать char почти нет: ограничение длины даёт varchar(n) без сюрпризов с пробелами.

select
  'abcd'::varchar(3)        as cast_truncates,   -- abc
  'ab'::char(5) = 'ab'      as char_equal,       -- true
  length('ab'::char(5))     as char_length,      -- 2
  octet_length('ab'::char(5)) as char_bytes;     -- 5

-- А вставка не обрезает, а падает:
-- insert into t (code) values ('abcd');
-- ERROR: value too long for type character varying(3)

SERIAL и IDENTITY: автоинкремент

serial — не настоящий тип, а сокращение: столбец integer, последовательность и значение по умолчанию nextval(…). Так id задавали годами, и вы увидите serial во многих старых схемах.

С PostgreSQL 10 стандартный способ — generated always as identity. Он описан в стандарте SQL, последовательность принадлежит столбцу, и случайно записать в id своё значение не выйдет. Для новой таблицы пишите id bigint generated always as identity primary key. Подробнее — в статье про CREATE TABLE.

Дата и время

Главное различие — timestamp и timestamptz. Первый хранит показания часов без пояса, второй — момент во времени, который показывается в поясе сессии. Для событий почти всегда нужен timestamptz, для дат договоров и рождений — date. Разница и перевод между поясами подробно разобраны в статье про TIMESTAMP в PostgreSQL, а группировка по неделям и месяцам — в статье про DATE_TRUNC.

BOOLEAN, UUID, JSONB и массивы

boolean хранит true, false или null. Считать долю удобно через avg(flag::int) или count(*) filter (where flag): сложение булевых значений напрямую в PostgreSQL не работает.

uuid — 128-битный идентификатор, его удобно генерировать вне базы. С PostgreSQL 13 функция gen_random_uuid() встроена. Хранить uuid в text можно, но столбец будет вдвое больше и сравнения медленнее.

jsonb хранит JSON в разобранном виде: поиск по ключам быстрый, по нему строят индексы. Старый json хранит текст как есть и подходит только для логов, которые никто не разбирает. Массивы (text[]) хороши для коротких списков вроде тегов. Если элементы массива нужно соединять с другими таблицами, лучше отдельная таблица.

Как посмотреть типы столбцов

В psql: команда \d payments выводит столбцы таблицы, их типы, ограничения и индексы. В любом клиенте — запрос к стандартному представлению information_schema.columns. Он работает и в PostgreSQL, и в DuckDB, на котором устроена песочница, но названия типов движки пишут по-своему.

Тип любого выражения покажет функция pg_typeof(…): например, pg_typeof(sum(amount)). Удобно, когда непонятно, почему деление вернуло целое число.

select table_name, column_name, data_type
from information_schema.columns
where table_name in ('users', 'payments')
order by table_name, ordinal_position;

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

Стандарт SQL задаёт общие названия, но каждая СУБД добавляет свои. Если запрос или схема переезжает, проверьте прежде всего деньги, строки и время.

Как выбрать тип: чеклист

• Будет ли значение участвовать в арифметике? Если да — число, а не текст с цифрами. Номер телефона и почтовый индекс — текст: с ними не считают, и у них бывают ведущие нули.
• Нужна ли точность до копейки? Да — numeric. Нет, и важна скорость на миллионах строк — double precision.
• Может ли таблица вырасти больше двух миллиардов строк? Да — bigint для ключей.
• Есть ли у значения правило длины? Да — varchar(n) или ограничение check. Нет — text.
• Это момент или календарный день? Момент — timestamptz, день — date.

Частые вопросы

Типы на практике удобнее всего проверить на учебной базе в песочнице. Курс «SQL с нуля» начинается с модуля о том, как устроены таблицы и их столбцы.