Типы данных в PostgreSQL
Какие типы данных чаще всего используются в PostgreSQL
Теория
Тип данных — это правило, по которому БД воспринимает значения в столбцах таблицы. Не просто внешний вид, а правило работы с данными. Например, число можно складывать, делить, округлять и сравнивать как число. Текст можно обрезать, склеивать, искать внутри текста совпадения по какому-то шаблону. Дата подходит для расчёта дней, месяцев, стажа и сроков. Логическое значение хранит состояние из двух (а на самом деле даже трех) вариантов.
Посмотрите на одну строку из таблицы workers. В ней могут стоять должность, зарплата и дата найма. На взгляд это просто три столбца. Для PostgreSQL это три разных типа данных. Зарплата подчиняется арифметике. Должность написана текстом. Дата найма годится для работы со временем. В одной строке таблицы лежат значения с разной природой, и система обязана это различать.
Поэтому тип данных — это часть смысла столбца таблицы. Он заранее объясняет системе, как вести себя со значением и какие операции для него уместны. Даже тип boolean лучше воспринимать не как абстрактное «истина или ложь», а как удобную форму для состояний вроде активен или не активен, оплачен или не оплачен, включено или выключено.
В PostgreSQL нам постоянно будут встречаться такие группы типов:
• целые числа — smallint, integer, bigint;
• дробные числа — numeric, real, double precision;
• текст — text, varchar, char;
• дата и время — date, time, timestamp, interval;
• логические значения — boolean.
На учебной схеме следующие столбцы таблиц (выбрано несколько столбцов для примера) находятся в следующих типах:
• столбец salary из таблицы workers — число;
• столбец price из таблицы products — точная дробь;
• столбец registration_date из таблицы users — дата;
• столбец order_date из таблицы orders — дата со временем;
• столбец status из таблицы orders — текст.
Почти каждый следующий запрос будет опираться на это понимание. Почему одну колонку можно передать в функцию SUM(), а другую нельзя. Почему для денег берут один тип, а для счётчика другой. Почему строковая функция не понимает число, пока его не переведут в текст. Это облегчает понимание SQL.
Откуда у столбцов таблицы берутся эти типы данных? Типы данных задаются проектировщиком /разработчиком БД при создании таблиц в специальных командах.
Посмотрите на одну строку из таблицы workers. В ней могут стоять должность, зарплата и дата найма. На взгляд это просто три столбца. Для PostgreSQL это три разных типа данных. Зарплата подчиняется арифметике. Должность написана текстом. Дата найма годится для работы со временем. В одной строке таблицы лежат значения с разной природой, и система обязана это различать.
Поэтому тип данных — это часть смысла столбца таблицы. Он заранее объясняет системе, как вести себя со значением и какие операции для него уместны. Даже тип boolean лучше воспринимать не как абстрактное «истина или ложь», а как удобную форму для состояний вроде активен или не активен, оплачен или не оплачен, включено или выключено.
В PostgreSQL нам постоянно будут встречаться такие группы типов:
• целые числа — smallint, integer, bigint;
• дробные числа — numeric, real, double precision;
• текст — text, varchar, char;
• дата и время — date, time, timestamp, interval;
• логические значения — boolean.
На учебной схеме следующие столбцы таблиц (выбрано несколько столбцов для примера) находятся в следующих типах:
• столбец salary из таблицы workers — число;
• столбец price из таблицы products — точная дробь;
• столбец registration_date из таблицы users — дата;
• столбец order_date из таблицы orders — дата со временем;
• столбец status из таблицы orders — текст.
Почти каждый следующий запрос будет опираться на это понимание. Почему одну колонку можно передать в функцию SUM(), а другую нельзя. Почему для денег берут один тип, а для счётчика другой. Почему строковая функция не понимает число, пока его не переведут в текст. Это облегчает понимание SQL.
Откуда у столбцов таблицы берутся эти типы данных? Типы данных задаются проектировщиком /разработчиком БД при создании таблиц в специальных командах.
Синтаксис
SELECT 1::integer, -- приводим число к целому типу integer
1::bigint, -- приводим число к большому целому типу bigint
CAST(99.95 AS numeric(10,2)), -- приводим число к точному числовому типу
'SQL'::text, -- приводим строку к текстовому типу
DATE '2024-06-01', -- записываем значение типа date
TIME '12:30:00', -- записываем значение типа time
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); -- проверяем логический типНиже представлена таблица основных типов. Она позволит увидеть: что хранит тип и для какой задачи он обычно нужен.
| Тип | Что хранит | Практический смысл |
|---|---|---|
| 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. Не пытайтесь выучить все типы разом
Сразу запомнить все типы сложно (более того, в списке выше указаны далеко не все типы PostgreSQL, а небольшая их часть, но часть основная). Попробуйте запоминать типы по их семействам. Целое число, точная дробь, приближённая дробь, текст, дата, момент времени, логический флаг. Этого хватает, чтобы запросы перестали выглядеть как набор случайных правил.
2. У целых типов главный вопрос — диапазон
smallint подходит для небольших значений. integer закрывает обычные повседневные задачи. bigint нужен там, где числам тесно в рамках integer. По смыслу это всё целые числа, но по вместимости у них разный масштаб.
3. numeric и double precision решают разные задачи
numeric нужен там, где важна точность, особенно в ценах и денежных расчётах. Но за точность обычно платят более тяжёлой обработкой. double precision хранит приближённые значения после 6 знаков после запятой.
4. Про текстовые типы
text обычно самый распространенный вариант использования в таблицах. varchar(n) полезен там, где ограничение длины — это настоящее бизнес-правило. Если длину не указывать, и text, и varchar вмещают очень большие строки — примерно до 1 ГБ на значение. char(n) ведёт себя жёстче и дополняет строку пробелами. Имеется в виду, что если вы присвоите столбцу тип данных char(15) и вставите в этот столбец текстовое значение размером в 10 символов, остальные 5 символов заполнятся автоматически пробелами, и эти пробелы будут весить столько же, сколько обычные символы.
5. date, time, timestamp и interval — это четыре разные роли
date хранит день. time хранит только время суток. timestamp хранит полный момент времени и дату и время. interval не хранит дату вообще, а описывает промежуток.
6. Когда появляется сомнение, выручает функция pg_typeof()
Если возник вопрос «А это значение вообще какого типа?», не нужно гадать. В PostgreSQL существует функция pg_typeof(). Внутрь функции можно поместить название интересующего нас столбца и узнать, что у него за тип: pg_typeof(column_name).
Примеры
1. Смотрим типы столбцов в таблице работников
Можно не угадывать тип по названию столбца, а спросить у PostgreSQL, используя функцию pg_typeof(). В ответе видно: salary — число, hire_date — дата, а position — текст.
🔄 Попробуйте изменить запрос:
- • Замените position на department_id и посмотрите, какой тип у этого столбца
- • Добавьте ещё и pg_typeof(first_name)
2. Проверяем, в каком типе находится цена товара
Поле price хранится как numeric(10,2), а значит PostgreSQL понимает: перед ним точное число для денежных расчётов.
🔄 Попробуйте изменить запрос:
- • Попробуйте вместо price посмотреть тип столбца category
3. Сравниваем типы text, varchar и char по длине слова "SQL"
Функция octet_length() определяет, сколько байт занимает значение. Пример показывает поведение типов: text и varchar(10), и как видно, ведут себя одинаково, а char(10) дополняет строку пробелами до фиксированной длины, которую мы указали в скобках (10) — это модификатор, ограничивающий длину строки в столбце. Заметьте, что несмотря на то, что у varchar(10) мы тоже указали значение в скобках, этот тип данных не дополняет строку пробелами до указанного в скобках параметра длины.
🔄 Попробуйте изменить запрос:
- • Замените 10 на 5 и сравните результат
- • Замените слово в скобках функции 'SQL' на 'AB'
4. Сравниваем date, time, timestamp и interval
date — это день. time — время суток. timestamp — полный момент. interval — не дата, а сдвиг во времени. Последний столбец показывает, как этот сдвиг работает на практике. Конструкция DATE '2024-06-01' указывает на то, что дата в кавычках должна восприниматься системой именно как типа DATE, для других типов аналогично.
🔄 Попробуйте изменить запрос:
- • Замените 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', а отдельный логический тип. И по смыслу он полезен не только для абстрактных флагов, но и для любых состояний из двух вариантов: да или нет, активно или не активно, оплачен или не оплачен.
Практика
Проверь себя
Ответьте на вопросы, чтобы закрепить материал:
1
Какой тип обычно уместнее выбрать для столбца с ценой товара, если важна точность копеек?
2
В чём главное практическое отличие integer от bigint?
3
Когда использование varchar(20) особенно оправдано?
4
Какой сценарий особенно хорошо ложится на тип boolean?
5
Какой тип нужен, если требуется хранить только время начала смены без даты?
6
Что вернёт выражение octet_length('SQL'::char(5))? Введите только число без кавычек.
7
Напишите запрос к таблице products, который выведет product_id, product_name, price и столбец discounted_value_type. В discounted_value_type нужно показать тип выражения price * 0.9 через pg_typeof(). Оставьте только товары, у которых цена не меньше 50000 и категория не равна 'Аксессуары'. Отсортируйте результат по price от большей к меньшей, а затем по product_id.
8
Напишите запрос к таблице workers, который выведет worker_id, first_name, position и столбец position_bytes. В position_bytes нужно показать, сколько байт занимает значение position после перевода в char(20) через octet_length(). Оставьте только работников, у которых position указана и department_id входит в список 2, 3, 5. Отсортируйте результат по worker_id.