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

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

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

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

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

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

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

Но если вы попытаетесь сравнить значение типа TEXT с числом — например, столбец name TEXT со значением 42 — PostgreSQL выдаст ошибку. Дело в том, что у литералов в SQL особая судьба: голый литерал '42' имеет тип unknown, и PostgreSQL без проблем приведёт его к integer по контексту. А вот значение, уже типизированное как text, само к числу не приведётся.

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

SELECT 'abc'::text = 42; -- ERROR: operator does not exist: text = integer

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

SELECT '42'::INTEGER = 42; -- TRUE
SELECT '42' = 42;          -- TRUE — '42' это unknown-литерал, PG приведёт к integer

Если значение реально имеет тип text, синтаксис ::Тип помогает явно сказать: «Пожалуйста, сначала преврати это в число».

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

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

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

Однако будьте осторожны с числами с плавающей запятой (REAL, DOUBLE PRECISION). Из-за ограниченной точности такие числа могут вести себя неожиданно. В PostgreSQL литералы вроде 0.1, 0.2, 0.3 по умолчанию имеют тип numeric (точная арифметика), поэтому классическая загадка работает «как ожидается»:

SELECT 0.1 + 0.2 = 0.3;  -- t (true) — литералы по умолчанию имеют тип numeric
SELECT 0.1::float + 0.2::float = 0.3::float;  -- f (false) — для float классическая проблема представления

Та самая известная загадка программирования возникает только для плавающей точки. PostgreSQL по умолчанию использует numeric для десятичных литералов, и поэтому от неё спасает.

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

При работе с текстовыми типами вы можете сравнивать 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' в число

Чтобы избежать таких ошибок, предварительно проверяйте данные: используйте регулярные выражения (value ~ '^[0-9]+$') или CASE WHEN ... THEN ... ELSE NULL END. PostgreSQL не имеет встроенной функции TRY_CAST (она есть в SQL Server / T-SQL).

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

При преобразовании дат вы можете использовать функции 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. Некорректное использование форматов при преобразовании дат.

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

• Всегда проверять данные перед преобразованием.
• Использовать 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 лекция

Недоступна

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

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

Открыть