Сравнение и преобразование типов данных

Зачем вообще всё это нужно — сравнение и преобразование типов? Представьте, у вас в таблице лежит строка "42", а вы хотите сравнить её с числом 42. На первый взгляд — одно и то же. Но для базы данных это могут быть совершенно разные вещи. PostgreSQL не будет автоматически догадываться, что вы имели в виду. И если не понимать, как именно он сравнивает значения разных типов, можно получить странный результат или вообще ошибку.

То же самое касается преобразований. Иногда нужно превратить текст в число, чтобы посчитать, сколько всего штук чего-то там. А бывает, наоборот — число нужно красиво отобразить как строку. Или взять дату и вывести её в виде "01.01.2025", потому что так привычнее пользователю.

Или вот другой пример: у вас точное значение хранится в NUMERIC, но для научных вычислений нужно использовать FLOAT. В таких случаях без явного преобразования — никуда.

Хорошая новость в том, что PostgreSQL отлично с этим справляется. Он даёт гибкие и понятные инструменты для таких задач. Главное — знать, как ими пользоваться, и не бояться немного заглянуть под капот. Этим и займёмся.

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

PostgreSQL старается быть умным — если видит, что сравниваются, скажем, INTEGER и NUMERIC, он спокойно приведёт их к общему знаменателю и всё сравнит. Это нормально, ведь оба — числа.

Но вот если вы решите сравнить строку с булевым значением (TEXT и BOOLEAN), база данных уже не поймёт, чего вы от неё хотите. У этих типов совершенно разная природа, и PostgreSQL просто выдаст ошибку. То же самое произойдёт, если вы попробуете сравнить строку "42" с числом 42, не указав явно, что хотите эту строку превратить в число.

Как это выглядит на практике? Вот пример, который вызовет ошибку:

SELECT '42' = 42; -- Ошибка! Строка и число без преобразования не сравниваются

А вот так всё сработает как надо:

SELECT '42'::INTEGER = 42; -- TRUE

Здесь мы явно говорим: “Пожалуйста, сначала преврати '42' в число”. Для этого и нужен синтаксис ::Тип, о котором мы уже упоминали. PostgreSQL любит, когда с ним разговаривают чётко.

Сравнение числовых типов

Числовые типы данных (INTEGER, NUMERIC, REAL) чаще всего совместимы между собой, поэтому их можно сравнивать без особых сложностей:

SELECT 42 = 42.0; -- TRUE
SELECT 42::REAL = 42.0; -- TRUE
SELECT 42.0::NUMERIC = 42; -- TRUE

Однако будьте осторожны с числами с плавающей запятой (REAL). Из-за ограниченной точности такие числа могут вести себя неожиданно. Например:

SELECT 0.1 + 0.2 = 0.3; -- FALSE

Разве это не самая известная загадка программирования? Здесь сравнение возвращает FALSE из-за особенностей хранения дробных чисел в памяти компьютера.

Сравнение текстовых типов

При работе с текстовыми типами вы можете сравнивать CHAR, VARCHAR и TEXT, так как PostgreSQL автоматически преобразует их к совместимым типам:

SELECT 'Hello' = 'Hello'::TEXT; -- TRUE
SELECT 'World'::CHAR(5) = 'World'::VARCHAR; -- TRUE

Обратите внимание на длину символов в CHAR(n): если строка короче указанной длины, PostgreSQL дополнит её пробелами.

Преобразование типов данных

PostgreSQL предоставляет несколько способов преобразования типов данных. Мы разберем два основных метода:

Способ №1: Явное преобразование (CAST)

Оператор CAST позволяет определить, как нужно преобразовать один тип в другой. Вот пример:

SELECT CAST('42' AS INTEGER); -- Преобразует строку '42' в число 42

Этот метод особенно удобен, если вы хотите сделать SQL-код более читаемым.

Способ №2: Сокращенная запись (::)

PostgreSQL предлагает альтернативный синтаксис для преобразования типов — использование ::. Это та же операция, но записана более кратко:

SELECT '42'::INTEGER; -- То же самое, что и CAST('42' AS INTEGER)

Автоматическое преобразование

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

SELECT '42' = 42::TEXT; -- TRUE

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

Примеры преобразования разных типов данных

Преобразование чисел в текст

Иногда возникает необходимость преобразовать числа в текст (например, для формирования сообщений):

SELECT 42::TEXT; -- Преобразует число 42 в строку '42'
SELECT 3.14::TEXT; -- Преобразует число 3.14 в строку '3.14'

Преобразование текста в числа

Если в строке содержится корректное число, вы можете преобразовать её в числовой тип:

SELECT '123'::INTEGER; -- Преобразует строку '123' в число 123
SELECT '3.14'::FLOAT;  -- Преобразует строку '3.14' в число 3.14

Но что будет, если текст не поддается преобразованию? Например:

SELECT 'Hello'::INTEGER; -- Ошибка: невозможно преобразовать 'Hello' в число

Чтобы избежать таких ошибок, вы можете использовать функцию TRY_CAST() (начиная с PostgreSQL 14) или предварительно проверять данные.

Преобразование даты в текст и обратно

При преобразовании дат вы можете использовать функции TO_CHAR() и TO_DATE():

SELECT TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'); -- Преобразует дату в строку
SELECT TO_DATE('2023-10-25', 'YYYY-MM-DD'); -- Преобразует строку в дату

Преобразование между BOOLEAN и текстом

Логический тип данных BOOLEAN также можно преобразовывать в строки:

SELECT TRUE::TEXT; -- 'true'
SELECT FALSE::TEXT; -- 'false'

Или наоборот:

SELECT 'true'::BOOLEAN; -- TRUE
SELECT 'false'::BOOLEAN; -- FALSE

Обратите внимание, что строки вроде 'yes' или 'no' не будут преобразованы автоматически.

Практика: всё на реальных примерах

Создадим таблицу, которая демонстрирует использование разных типов данных:

Теперь выполним операции преобразования:

-- Преобразование текста в число
SELECT number_as_text::INTEGER FROM data_types_demo WHERE number_as_text = '42';

-- Преобразование даты в текст
SELECT TO_CHAR(actual_date, 'DD/MM/YYYY') FROM data_types_demo;

-- Преобразование строки в дату
SELECT TO_DATE(date_as_text, 'YYYY-MM-DD') FROM data_types_demo;

Типичные ошибки при преобразовании данных

Типичные ошибки включают:

1. Попытки преобразовать данные, которые не соответствуют ожидаемому формату (например, строку 'Hello' в INTEGER).
2. Проблемы с округлением и точностью при работе с числами с плавающей запятой.
3. Некорректное использование форматов при преобразовании дат.

Для предотвращения ошибок рекомендуется:

• Всегда проверять данные перед преобразованием.
• Использовать функции обработки ошибок (TRY_CAST или CASE).
• Указывать формат явно при преобразовании дат.

-- Проверка данных перед преобразованием
SELECT 
    CASE 
        WHEN number_as_text ~ '^\d+$' THEN number_as_text::INTEGER
        ELSE NULL
    END AS safe_integer
FROM data_types_demo;

Используйте этот подход, чтобы защитить ваши запросы от неожиданных ситуаций!

На этом этапе вы обладаете базовыми навыками для сравнения и преобразования типов данных в PostgreSQL. Остается только практика, практика и ещё раз практика. Увидимся на следующих лекциях!

2

Задача

SQL SELF, 16 уровень, 3 лекция

Недоступна

Преобразование строкового значения в число

Преобразование строкового значения в число

Открыть

2

Задача

SQL SELF, 16 уровень, 3 лекция

Недоступна

Преобразование дат

Преобразование дат

Открыть