Типы данных в PostgreSQL

Какие типы данных чаще всего встречаются в PostgreSQL и как понимать их смысл в запросах

Теория

Тип данных — это правило, по которому БД воспринимает значение. Не внешний вид и не подпись ради порядка, а правило работы. Число можно складывать, делить, округлять и сравнивать как число. Текст можно резать, склеивать, искать по шаблону. Дата подходит для расчёта дней, месяцев, стажа и сроков. Логическое значение хранит состояние из двух вариантов.

Посмотрите на одну строку из таблицы workers. В ней могут стоять должность, зарплата и дата найма. На взгляд это просто три поля. Для PostgreSQL это три разных типа данных. Зарплата подчиняется арифметике. Должность живёт по правилам текста. Дата найма годится для работы со временем. В одной строке таблицы лежат значения с разной природой, и система обязана это различать.

Поэтому тип данных — это часть смысла столбца. Он заранее объясняет системе, как вести себя со значением и какие операции для него уместны. Даже boolean лучше воспринимать не как абстрактное «истина или ложь», а как удобную форму для состояний вроде активен или не активен, оплачен или не оплачен, включено или выключено.

В PostgreSQL нам постоянно будут встречаться такие группы типов:
• целые числа — smallint, integer, bigint;
• дробные числа — numeric, real, double precision;
• текст — text, varchar, char;
• дата и время — date, time, timestamp, interval;
• логические значения — boolean.

На учебной схеме следующие столбцы таблиц (выбрано несколько столбцов для примера) находятся в типах:
• workers.salary — число;
• products.price — точная дробь;
• users.registration_date — дата;
• orders.order_date — дата со временем;
• orders.status — текст.

Почти каждый следующий запрос будет опираться на это понимание. Почему одну колонку можно передать в функцию SUM(), а другую нельзя. Почему для денег берут один тип, а для счётчика другой. Почему строковая функция не понимает число, пока его не переведут в текст. Это облегчает понимание SQL.

Синтаксис

SELECT 1::integer,
       1::bigint,
       CAST(99.95 AS numeric(10,2)),
       'SQL'::text,
       DATE '2024-06-01',
       TIME '12:30:00',
       TIMESTAMP '2024-06-01 12:30:00',
       INTERVAL '2 days',
       true;

SELECT pg_typeof(99.95::numeric),
       pg_typeof(DATE '2024-06-01'),
       pg_typeof(TIME '12:30:00'),
       pg_typeof(true);

SQL

Ниже представлена таблица основных типов. Смотрите на неё как на способ быстро увидеть: что хранит тип и для какой задачи он обычно нужен.

Тип	Что хранит	Практический смысл
smallint	Небольшие целые числа	Диапазон от -32768 до 32767. Подходит там, где значения точно небольшие.
integer	Целые числа	Диапазон от -2147483648 до 2147483647. Основной рабочий тип для количеств, зарплат, идентификаторов и обычных счётчиков.
bigint	Очень большие целые числа	Диапазон от -9223372036854775808 до 9223372036854775807. Берут там, где integer уже тесно.
numeric(p,s)	Точные дробные числа	Хороший выбор для цен и точных расчётов. p показывает общее количество цифр, s — сколько из них после запятой. За точность обычно платят чуть более тяжёлой обработкой.
real	Приближённые дробные числа	Хранит дробные значения, но не про идеальную точность. Для денег звучит уже сомнительно.
double precision	Приближённые дробные числа повышенной точности	Точнее, чем real, но по своей природе всё равно остаётся приближённым типом.
text	Текст	Гибкий текстовый тип без ограничения длины. Одно значение может занимать примерно до 1 ГБ, хотя на практике здравый смысл обычно останавливает намного раньше.
varchar(n)	Текст с ограничением по длине	Уместен, если ограничение по длине — это часть бизнес-правила, а не просто привычка. Если длина не указана, по вместимости ведёт себя почти как text — тоже примерно до 1 ГБ на значение.
char(n)	Текст фиксированной длины	Если строка короче, PostgreSQL дополняет её пробелами. Именно поэтому тип считается довольно капризным.
date	Только дату	Хранит год, месяц и день. Без времени.
time	Только время	Хранит часы, минуты и секунды без даты. Подходит, например, для времени начала смены или времени звонка.
timestamp	Дату и время	Нужен там, где важен не просто день, а конкретный момент.
interval	Промежуток времени	Используют, когда нужно прибавить или вычесть дни, часы, минуты и другие куски времени.
boolean	Логическое значение	Хранит логику из двух состояний. Внутри это true или false, но по смыслу может описывать любые пары вроде да или нет, активен или не активен, включено или выключено.

Теперь разберём несколько опорных идей.

1. Не пытайтесь выучить все типы разом
Намного полезнее сначала различать семейства. Целое число, точная дробь, приближённая дробь, текст, дата, момент времени, логический флаг. Этого хватает, чтобы запросы перестали выглядеть как набор случайных правил.

2. У целых типов главный вопрос — диапазон
smallint подходит для небольших значений. integer закрывает обычные повседневные задачи. bigint нужен там, где числам тесно в рамках integer. По смыслу это всё целые числа, но по вместимости у них разный масштаб.

3. numeric и double precision решают разные задачи
numeric нужен там, где важна точность, особенно в ценах и денежных расчётах. Но за точность обычно платят более тяжёлой обработкой. double precision хранит приближённые значения после 6 знаков после запятой. Это не плохой тип, а просто другой инструмент.

4. У текстовых типов тоже свой характер
text обычно самый спокойный вариант. varchar(n) полезен там, где ограничение длины — это настоящее бизнес-правило. Если длину не указывать, и text, и varchar вмещают очень большие строки — примерно до 1 ГБ на значение. char(n) ведёт себя жёстче и любит дополнять строку пробелами.

5. date, time, timestamp и interval — это четыре разные роли
date хранит день. time хранит только время суток. timestamp хранит полный момент времени. interval не хранит дату вообще, а описывает промежуток. Если это развести в голове, дальше с датами будет проще.

6. Когда появляется сомнение, выручает функция pg_typeof()
Возник вопрос «а это значение вообще какого типа?». Не нужно гадать. PostgreSQL умеет ответить сам. Для обучения это особенно полезно: меньше догадок, больше прямого понимания.

Примеры

1. Смотрим типы столбцов на реальной таблице работников

SELECT worker_id,
       pg_typeof(salary) AS salary_type,
       pg_typeof(hire_date) AS hire_date_type,
       pg_typeof(position) AS position_type
FROM workers
ORDER BY worker_id
LIMIT 5;

Очень полезный приём. Не угадывать тип по названию столбца, а прямо спросить у PostgreSQL. В ответ сразу видно: salary для него число, hire_date дата, а position текст.

🔄 Попробуйте изменить запрос:

• Замените position на department_id и посмотрите, какой тип у этого столбца
• Добавьте ещё и pg_typeof(first_name)

2. Проверяем, как хранится цена товара

SELECT product_id,
       price,
       pg_typeof(price) AS price_type
FROM products
ORDER BY product_id
LIMIT 5;

Пример показывает, что цена для базы данных — не просто абстрактная дробь. Поле хранится как numeric(10,2), а значит PostgreSQL понимает: перед ним точное число для денежных расчётов.

🔄 Попробуйте изменить запрос:

• Попробуйте вместо price посмотреть тип столбца category

3. Сравниваем text, varchar и char на одном коротком слове

SELECT octet_length('SQL'::text) AS text_bytes,
       octet_length('SQL'::varchar(10)) AS varchar_bytes,
       octet_length('SQL'::char(10)) AS char_bytes;

Здесь помогает функция octet_length(). Она показывает, сколько байт занимает значение. На коротком слове сразу видно поведение типов: text и varchar(10) ведут себя одинаково, а char(10) дополняет строку пробелами до фиксированной длины, которую мы указали в скобках. Заметьте, что несмотря на то, что у varchar(10) мы тоже указали значение в скобках этот тип данных не дополняет строку пробелами до указанного в скобках параметра длины.

🔄 Попробуйте изменить запрос:

• Замените 10 на 5 и сравните результат
• Замените 'SQL' на 'AB'

4. Сравниваем date, time, timestamp и interval в одном запросе

SELECT DATE '2024-06-01' AS only_date,
       TIME '12:30:00' AS only_time,
       TIMESTAMP '2024-06-01 12:30:00' AS exact_moment,
       INTERVAL '2 days 3 hours' AS time_shift,
       TIMESTAMP '2024-06-01 12:30:00' + INTERVAL '2 days 3 hours' AS moved_moment;

Задача примера простая: развести по местам четыре похожие на вид сущности. date — это день. time — время суток. timestamp — полный момент. interval — не дата, а сдвиг во времени. Последний столбец показывает, как этот сдвиг работает на практике.

🔄 Попробуйте изменить запрос:

• Замените INTERVAL '2 days 3 hours' на INTERVAL '5 hours'
• Попробуйте прибавить этот же интервал не к timestamp, а к DATE '2024-06-01'

Типичные ошибки

Сваливают все числа в одну общую кучу
integer, bigint, numeric и double precision — это не просто четыре похожих названия. У каждого своя задача. Один удобен для обычных целых значений, другой для огромного диапазона, третий для точных дробей, четвёртый для приближённых расчётов.

Берут приближённый тип там, где потом придётся отвечать за деньги
Если речь идёт о ценах и денежных расчётах, у numeric здесь гораздо лучше репутация. Типы вроде real и double precision подходят под другие задачи, где точность после 6 знаков после запятой не важна.

Недооценивают характер char(n)
Этот тип любит фиксированную длину и дополняет строку пробелами. На таких мелочах могут появляться странные эффекты.

Смешивают в голове date и timestamp
date хранит только день. Если в задаче важны часы, минуты и секунды, это про timestamp. Похожее название не делает их одинаковыми по смыслу.

Забывают, что у точного numeric есть своя цена
Этот тип хорош там, где точность критична, особенно в ценах и денежных расчётах. Но за такую аккуратность СУБД обычно платит более тяжёлой обработкой. Поэтому использовать numeric вообще для всего подряд — не самая хорошая привычка.

Путают вместимость text и varchar
Многим кажется, что varchar сам по себе уже всё строго ограничивает. На деле без указания длины он по вместимости очень близок к text. В обоих случаях речь идёт о очень больших строках, примерно до 1 ГБ на значение.

Смотрят на boolean как на обычный текст
boolean — это не строка со словами 'true' и 'false', а отдельный логический тип. И по смыслу он полезен не только для абстрактных флагов, но и для любых состояний из двух вариантов: да или нет, активно или не активно, оплачен или не оплачен.

Типы данных в PostgreSQL

Теория

Синтаксис

Примеры

1. Смотрим типы столбцов на реальной таблице работников

🔄 Попробуйте изменить запрос:

2. Проверяем, как хранится цена товара

🔄 Попробуйте изменить запрос:

3. Сравниваем text, varchar и char на одном коротком слове

🔄 Попробуйте изменить запрос:

4. Сравниваем date, time, timestamp и interval в одном запросе

🔄 Попробуйте изменить запрос:

Типичные ошибки

Практика

Проверь себя