Типы данных и приведение типов
Преобразование одного типа данных в другой
Теория
В 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 встречается буквально на каждом шагу (:: является особенностью PostgreSQL и в других аналогичных СУБД вряд ли будет работать).
Важно: когда в SQL-запросе используется преобразование типов, в самой БД на самом деле ничего не изменяется. Там столбцы находятся в тех типах данных, в которых они там и были ранее (при создании). Результат преобразования в SQL-запросах мы получаем только лишь для чтения и анализа на мониторе нашего компьютера.
Например, '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 встречается буквально на каждом шагу (:: является особенностью PostgreSQL и в других аналогичных СУБД вряд ли будет работать).
Важно: когда в SQL-запросе используется преобразование типов, в самой БД на самом деле ничего не изменяется. Там столбцы находятся в тех типах данных, в которых они там и были ранее (при создании). Результат преобразования в SQL-запросах мы получаем только лишь для чтения и анализа на мониторе нашего компьютера.
Синтаксис
SELECT CAST(value AS numeric), -- приводим значение value к типу numeric
value::numeric, -- то же самое, но через оператор ::, а не через CAST
some_timestamp::date, -- приводим дату и время к обычной дате
some_number::text -- приводим число к текстовому типу
FROM table_name; -- указываем таблицу, из которой берём данные
SELECT CAST('2024-06-01' AS date), -- приводим строку к типу date
'2024-06-01 12:30:00'::timestamp; -- приводим строку к типу timestampКороткая шпаргалка.
| Пример | Результат | Что делает |
|---|---|---|
| '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() ожидает внутри значение текстового типа.
3. Не каждое значение можно перевести в любой тип
Если в текстовом типе находится число, его можно превратить в integer или numeric. Но если в текстовом типе находится что-то вроде 'abc', никакого волшебства не произойдёт. PostgreSQL не будет угадывать, что вы имели в виду, а просто вернёт ошибку, потому что 'abc'нельзя перевести в число. Таким образом, если вы хотите перевести какой-то столбец из текстового типа в числовой, и если хотя бы в одной строке этого столбца будут содержаться не числовые символы, то ничего не выйдет.
4. При переводе date в timestamp PostgreSQL добавляет время 00:00:00
При переводе значения из типа DATE в тип TIMESTAMP система прибавит к значению даты 00:00:00 и дальше будет воспринимать новое значение как тип TIMESTAMP.
5. Перевод в text удобен для вывода, но с ним можно поторопиться
Если вы преобразовали число или дату в текст, то дальше СУБД будет смотреть на это как на набор символов. Это удобно, если нужно склеить, обрезать или выполнить другие манипуляции с текстовой строкой. Но выполнить арифметические операции с такого рода значением уже не получится.
Примеры
1. Берём только дату из поля timestamp
Иногда timestamp нужен целиком, а иногда время мешает. Здесь мы убираем часы и минуты, оставляя только календарный день.
🔄 Попробуйте изменить запрос:
- • Добавьте в SELECT операцию разности дат order_date::date - '2024-01-01'::date и назовите это diff_days
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 действительно нужен
В PostgreSQL существует ROUND (подробно про функции поговорим в следующих темах). Задача этой функции — округлить значение до указанной нами точности. Например, конструкция ROUND(12.675, 2) округлит число до двух знаков после запятой. Но проблема в том, что если в БД столбец с округляемыми значениями находится в типе данных real или double precision, система выдаст ошибку, потому что не получится округлить до заданной точности неточное дробное число. А мы помним из прошлой темы, что real или double precision — это неточные дробные числа после запятой. Однако у вас получится округлить, если внутри функции вы сначала переведёте тип данных double precision в тип numeric, как в примере.
🔄 Попробуйте изменить запрос:
- • Уберите последний ::numeric внутри ROUND() и посмотрите, какую ошибку вернёт PostgreSQL
- • Замените 2 на 1 и сравните, как меняется округление
5. Переводим число в текст, когда хотим обработать его как строку
Цена — это число, и для расчётов это то, что нужно. Но LEFT() является функцией, которая не выполняет математические расчеты, а работает со строкой, если быть точнее, эта функция выводит указанное количество символов с начала строки. Поэтому здесь сначала нужно перевести цену в text, а уже потом выводить нужную часть строки функциейLEFT().
🔄 Попробуйте изменить запрос:
- • Уберите ::text и посмотрите, почему LEFT перестанет работать
- • Замените 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.