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

# Конвертирование типов данных в PostgreSQL

Конвертирование типов данных (type casting) — это основная операция при работе с БД. PostgreSQL предоставляет несколько способов преобразования типов, каждый с собственными нюансами и производительностью.

Основной синтаксис: CAST и оператор ::

В PostgreSQL есть два эквивалентных способа привести тип:

SELECT CAST('123' AS INTEGER);
SELECT '123'::INTEGER;
SELECT CAST('2024-01-15' AS DATE);
SELECT '2024-01-15'::DATE;

Оператор :: более удобен при множественных преобразованиях:

SELECT ('123'::INTEGER * 2)::TEXT;

Популярные преобразования типов

Текст к Числам

SELECT '42'::INTEGER;
SELECT CAST('42' AS BIGINT);
SELECT '3.14'::NUMERIC;
SELECT '3.14'::DECIMAL(5,2);
SELECT 42::TEXT;
SELECT 3.14::VARCHAR;

Текст к Датам

SELECT '2024-01-15'::DATE;
SELECT '2024-01-15 14:30:00'::TIMESTAMP;
SELECT TO_DATE('15/01/2024', 'DD/MM/YYYY');
SELECT TO_TIMESTAMP('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
SELECT '2024-01-15'::DATE::TEXT;
SELECT TO_CHAR(NOW(), 'DD.MM.YYYY HH24:MI');

JSON преобразования

SELECT '{"name": "John"}'::JSONB;
SELECT data::TEXT FROM my_table;
SELECT data->>'name' FROM my_table;
SELECT data->'age' FROM my_table;

Массивы

SELECT STRING_TO_ARRAY('apple,banana,orange', ',');
SELECT ARRAY_TO_STRING(ARRAY['a', 'b', 'c'], ', ');

Обработка ошибок при преобразовании

Неправильное преобразование вызывает ошибку. Используй TRY_CAST (PostgreSQL 12+) для безопасного преобразования:

SELECT TRY_CAST('abc' AS INTEGER);
SELECT CASE WHEN '123' ~ '^[0-9]+$' THEN '123'::INTEGER ELSE NULL END;

Специальные функции преобразования

SELECT TO_NUMBER('1,234.56', '9,999.99');
SELECT TO_CHAR(1234.5, '9,999.99');
SELECT ENCODE('Hello'::BYTEA, 'hex');
SELECT DECODE('48656c6c6f', 'hex');
SELECT SUBSTRING('2024-01-15'::TEXT, 1, 4)::INTEGER;

Производительность при конвертировании

Неэффективно — преобразование в SELECT для каждой строки требует ресурсов. Хорошо — убедись, что данные имеют правильные типы в таблице с самого начала.

Когда преобразование необходимо

1. Миграция данных при смене типа колонки
2. Интеграция систем с разными типами данных
3. Парсинг JSON из API
4. Вычисления, требующие определённого типа

Best Practices

• Используй :: вместо CAST для краткости в PostgreSQL
• Всегда проверяй данные перед преобразованием
• Преобразуй на уровне БД, а не приложения
• Документируй причины преобразований
• Выбирай правильные типы при создании таблиц, чтобы избежать лишних преобразований
• Помни о производительности: частые преобразования указывают на проблемы в дизайне схемы