> SmartGate 41 752 9863

Типы данных и приведение типов

Как устроены типы данных в PostgreSQL и как переводить один тип в другой

Теория

В PostgreSQL значения в каждом столбце находятся в каком-то определенном типе данных. Это могут быть числа, строки, даты и другие типы. Не всегда визуально возможно определить в каком типе данных перед нами значение.

Например, '2024-06-01' в типе DATE и '2024-06-01' в типе TEXT выглядят похоже только для нас, но для СУБД это разные значения. В первом случае это дата, во втором — просто строка. Для базы данных это две разные сущности, и обращаться с ними она будет по-разному.

Приведение типов - это операция, при помощи которой можно перевести (конвертировать) значение из одного типа в другой. То есть присвоить значению тот тип, в котором мы хотим использовать его дальше.

Это нужно в ситуациях, когда необходимо:
• текст превратить в число, чтобы считать;
• текст превратить в дату, чтобы сравнивать даты;
• дату превратить в timestamp, чтобы прибавить часы или интервал;
• число превратить в текст, если дальше его нужно обрабатывать как строку;
• значение перевести в numeric, если важна точность и предсказуемое округление.

Чаще всего в этой теме мы будем обсуждать типы:
• integer — целое число;
• numeric — точное дробное число;
• text — текст;
• date — только дата;
• timestamp — дата и время;
• boolean — логическое значение true или false.

В PostgreSQL есть две основные формы записи для преобразования типов:
• Функция CAST(value AS type);
• И оператор value::type.

Обе делают одно и то же. Разница только в том, как это записано. CAST() выглядит более универсально и чуть более официально, а запись через :: короче и в PostgreSQL встречается буквально на каждом шагу.

Главная мысль: тип данных — это не скучная техническая приписка, а часть смысла запроса. Как только это становится понятно, многие ошибки перестают быть загадкой.

Синтаксис 

SELECT CAST(value AS numeric),
       value::numeric,
       some_timestamp::date,
       some_number::text
FROM table_name;

SELECT CAST('2024-06-01' AS date),
       '2024-06-01 12:30:00'::timestamp;
SQL

Короткая шпаргалка. Она нужна не для зубрёжки, а чтобы глазами быстро уловить, что во что обычно переводят.

Пример Результат Что делает
'42'::integer 42 Превращает текст в целое число.
CAST('12.675' AS numeric) 12.675 Превращает текст в точное дробное число.
order_date::date Только дата Убирает время у значения timestamp.
registration_date::timestamp Дата + 00:00:00 Превращает дату в дату со временем.
price::text Текст с числом Позволяет использовать число как текст.
CAST('2024-12-31' AS date) 31.12.2024 Превращает текст в дату.

Разложим механику по полочкам.


1. После значения мы указываем тип, в который хотим его перевести
Если вы пишете value::numeric, это читается так: возьми value и считай его значением типа numeric. Если вы пишете CAST(value AS numeric), смысл абсолютно тот же. Разница только во внешнем виде записи.


2. Приведение типа почти всегда делается не само по себе, а ради следующего шага
Обычно мы переводим значение не потому, что нам просто захотелось, а потому что дальше какая-то функция, операция или выражение ждёт другой тип. Например, LEFT() ждёт внутри значение текстового типа, а двухаргументный ROUND() в практических сценариях часто используют вместе с numeric.


3. Не каждое значение можно честно перевести в любой тип
Если в текстовом типе находится число, его можно превратить в integer или numeric. Но если в текстовом типе находится что-то вроде 'abc', никакого волшебства не произойдёт. PostgreSQL не будет гадать, что вы имели в виду, а просто вернёт ошибку, потому что 'abc'нельзя перевести в число.


4. При переводе date в timestamp PostgreSQL добавляет время 00:00:00
Это важный момент. База данных не выдумывает случайные часы и минуты, а честно ставит начало суток. Поэтому после такого преобразования можно прибавлять интервалы, часы и другие куски времени.


5. Перевод в text удобен для вывода, но с ним легко поторопиться
Как только число или дата стали текстом, дальше база данных уже смотрит на них как на строку символов. Это удобно, если нужно склеить, обрезать или красиво показать значение. Но для арифметики и нормальной работы с датами это плохая почва. Поэтому обычно сначала считают, сравнивают и фильтруют, а уже потом переводят результат в текст, если это действительно нужно.

Примеры

1. Берём только дату из поля timestamp

Иногда timestamp нужен целиком, а иногда время только мешает. Здесь мы как будто снимаем с записи часы и минуты, оставляя только календарный день. Новый столбец order_day как раз про это.

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

  • • Добавьте в SELECT ещё и order_date::date - '2024-01-01'::date
  • • Оставьте только заказы со статусом 'доставлен'

2. Превращаем дату в timestamp

Дата — это просто отметка в календаре. Timestamp — полноценный момент времени. Если дальше захочется прибавить часы, поработать с интервалами или сравнить значение с другим timestamp, обычную дату сначала нужно перевести в такой формат. PostgreSQL для этого добавляет время 00:00:00 к дате.

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

  • • Прибавьте к registration_date::timestamp интервал INTERVAL '12 hours' и посмотрите, как появится не просто дата, а конкретный момент дня
  • • Попробуйте вместо INTERVAL '12 hours' прибавить INTERVAL '2 days'

3. Сначала задаём тип литералу, а потом уже что-то с ним делаем

Представьте, что значение пришло без паспорта. На вид понятно, что перед нами дата, момент времени и число, но PostgreSQL такие вещи любит получать официально. Как только мы явно задаём тип, всё становится на свои места: к дате можно прибавить месяц, timestamp сдвинуть на несколько часов, а текст с ценой использовать в расчёте наценки.

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

  • • Замените '12500.75' на '12 500.75' и посмотрите, почему красивая для человека запись не всегда нравится PostgreSQL
  • • Замените INTERVAL '1 month' на INTERVAL '10 days' и сравните результат

4. Показываем случай, где numeric действительно нужен

Снаружи кажется, что перед нами просто число 12.675. Но PostgreSQL смотрит глубже: в первом столбце это double precision, а двухаргументный ROUND() для предсказуемого округления ждёт numeric. Поэтому схема простая: сначала переводим значение в нужный тип, потом округляем.

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

  • • Уберите последний ::numeric внутри ROUND() и посмотрите, какую ошибку вернёт PostgreSQL
  • • Замените 2 на 1 и сравните, как меняется округление

5. Переводим число в текст, когда хотим обработать его как строку

Цена — это число, и для расчётов это отлично. Но LEFT не считает, а режет строку по символам. Поэтому здесь сначала переводим цену в text, а уже потом берём нужный кусок.

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

  • • Уберите ::text и посмотрите, почему LEFT перестанет понимать price
  • • Замените LEFT на RIGHT и сравните результат

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

Думают, что значение и его тип — это мелочь
На самом деле это часть логики запроса. Текст '42' и число 42 — не одно и то же. То же самое относится к дате и тексту, который просто похож на дату.

Путают CAST() и :: как будто это разные операции
Нет. CAST(value AS type) и value::type делают одно и то же. Разница только в записи.

Пытаются превратить в число или дату то, что туда честно не переводится
Например, 'abc'::integer или CAST('не дата' AS date) приведут к ошибке. Приведение типа не исправляет плохое значение, а только меняет его тип, если это вообще возможно.

Забывают, что при переводе date в timestamp появляется время 00:00:00
Это нормально. PostgreSQL не выдумывает случайное время, а просто добавляет начало суток.

Слишком рано переводят число или дату в text
Для вывода это удобно, но для вычислений уже плохо. Обычно сначала делают расчёты, фильтрацию и сравнение, а уже потом, если нужно, переводят результат в текст.

Не дают понятный псевдоним вычисляемому столбцу после приведения типа
Записи вроде order_date::date или CAST(price AS text) лучше сразу называть через AS: например order_day или price_text. Так результат читать заметно легче.

Практика

Проверь себя

Ответьте на вопросы, чтобы закрепить материал:

1
Какое утверждение про CAST(value AS type) и value::type верное?
2
Что произойдёт при попытке выполнить CAST('abc' AS integer)?
3
Что вернёт выражение CAST('2024-06-15' AS date) + 7? Введите только дату без кавычек.
4
Что вернёт выражение CAST('2024-06-15' AS date)::timestamp? Введите значение целиком без кавычек.
5
В каком случае особенно уместно перевести значение в text?
6
Напишите запрос к таблице workers, который выведет worker_id, first_name, hire_date и столбец first_meeting_time. В first_meeting_time нужно показать hire_date, переведённую в timestamp, а затем сдвинутую на INTERVAL '9 hours'. Оставьте только работников из отделов 1 и 4, у которых hire_date не раньше DATE '2021-01-01'. Отсортируйте результат по hire_date от более ранней к более поздней, а потом по worker_id.
7
Напишите запрос к таблице products, который выведет product_id, product_name, price и столбец price_suffix. В price_suffix нужно показать последние две позиции из price. Для этого цену сначала нужно перевести в текст, а потом применить RIGHT(). Оставьте только товары, у которых price не меньше 50000 и категория указана. Отсортируйте результат по price от большей к меньшей, а потом по product_id.