Как в PostgreSQL конвертировать данные из строки в целое число

PostgreSQL: Конвертирование строки в целое число

В PostgreSQL есть несколько способов конвертирования строк в целые числа (INTEGER, BIGINT). Рассмотрю все основные подходы с примерами.

1. Оператор CAST (явное приведение типов)

SELECT CAST('123' AS INTEGER);
-- Результат: 123 (тип INTEGER)

SELECT CAST('456' AS BIGINT);
-- Результат: 456 (тип BIGINT)

SELECT CAST(age_string AS INTEGER)
FROM users;
-- Конвертирует все значения из текстовой колонки в INTEGER

2. Оператор :: (PostgreSQL синтаксис)

Это PostgreSQL-специфичный синтаксис, более компактный:

SELECT '789'::INTEGER;
-- Результат: 789

SELECT '999'::BIGINT;
-- Результат: 999

SELECT phone_id::INTEGER FROM orders;
-- Конвертирует значение из таблицы

3. Функция CAST с операндом

SELECT CAST(string_value AS INTEGER) AS numeric_value
FROM table_name
WHERE string_value ~ '^[0-9]+$';  -- Проверяем, что это число

Практические примеры

Пример 1: Конвертирование колонки в таблице

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price_string VARCHAR(20)  -- Хранится как строка
);

INSERT INTO products (name, price_string) VALUES
('Товар 1', '100'),
('Товар 2', '250'),
('Товар 3', '500');

-- Конвертируем и суммируем
SELECT 
    name,
    price_string::INTEGER AS price
FROM products;

-- Или с CAST
SELECT 
    name,
    CAST(price_string AS INTEGER) AS price
FROM products;

Пример 2: Условие с конвертированием

SELECT * FROM users
WHERE user_id::INTEGER > 100;

-- Или с CAST
SELECT * FROM users
WHERE CAST(user_id AS INTEGER) > 100;

Пример 3: Сортировка по числовому значению

-- БЕЗ конвертирования (неправильно - сортирует как строки):
SELECT * FROM products
ORDER BY product_id;  -- 1, 10, 100, 2, 20, 200

-- С конвертированием (правильно):
SELECT * FROM products
ORDER BY product_id::INTEGER;  -- 1, 2, 10, 20, 100, 200

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

Проблема: Строка содержит не-цифровые символы

SELECT '123abc'::INTEGER;
-- ERROR:  invalid input syntax for integer: \"123abc\"

Решение 1: Функция TRY_CAST (PostgreSQL 15+)

SELECT TRY_CAST('123abc' AS INTEGER);
-- Результат: null (вместо ошибки)

SELECT 
    id,
    TRY_CAST(value AS INTEGER) AS numeric_value
FROM possibly_invalid_data;

Решение 2: Функция CASE и регулярные выражения

SELECT 
    id,
    CASE 
        WHEN value ~ '^[0-9]+$' THEN CAST(value AS INTEGER)
        ELSE NULL
    END AS numeric_value
FROM data_table;

-- ~ это оператор LIKE для regex

Решение 3: Функция NULLIF для избежания ошибок

SELECT 
    id,
    CAST(NULLIF(value, '') AS INTEGER) AS numeric_value
FROM data_table;

-- NULLIF(value, '') возвращает NULL если value == '',
-- иначе возвращает value

Продвинутые техники

1. Конвертирование с дефолтным значением

SELECT 
    id,
    COALESCE(TRY_CAST(value AS INTEGER), 0) AS numeric_value
FROM data_table;

-- COALESCE возвращает первое не-null значение
-- Если TRY_CAST вернул null, используем 0

2. Проверка перед конвертированием

SELECT 
    id,
    value
FROM users
WHERE value ~ '^[0-9]{1,10}$'
  AND CAST(value AS INTEGER) > 0
  AND CAST(value AS INTEGER) <= 2147483647;  -- MAX INT32

3. Обработка разных форматов

-- Конвертирование со скобками и минусом
SELECT 
    id,
    CAST(REPLACE(REPLACE(value, '(', '-'), ')', '') AS INTEGER) AS numeric_value
FROM phone_numbers;

-- Пример: '(123)' → '-123-' → -123

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

CAST vs ::

-- Эти два способа эквивалентны по производительности
EXPLAIN ANALYZE
SELECT CAST(id AS TEXT) FROM large_table;  -- О(n)

EXPLAIN ANALYZE
SELECT id::TEXT FROM large_table;  -- О(n)

-- Обе операции O(n), где n = количество строк

Рекомендация: Индексы

-- Если часто конвертируешь и фильтруешь, создай вычисляемый индекс
CREATE INDEX idx_user_id_numeric 
ON users (CAST(user_id AS INTEGER));

-- Теперь конвертирование в WHERE будет быстрее
SELECT * FROM users
WHERE CAST(user_id AS INTEGER) > 100;  -- Использует индекс

Интеграция с Java/ORM

Hibernate с аннотацией

@Entity
public class Product {
    @Id
    @GeneratedValue
    private Long id;
    
    // Колонка хранится как VARCHAR
    @Column(name = \"price_string\")
    private String priceString;
    
    // Вычисляемое свойство с конвертированием
    @Transient
    public Integer getPrice() {
        return Integer.parseInt(priceString);
    }
}

Или в SQL запросе через HQL/JPQL

@Query(\"SELECT p FROM Product p WHERE CAST(p.priceString AS INTEGER) > ?1\")
List<Product> getExpensiveProducts(Integer minPrice);

Native Query

@Query(value = \"SELECT * FROM products WHERE price_string::INTEGER > :price\",
       nativeQuery = true)
List<Product> getExpensiveProducts(@Param(\"price\") Integer price);

Практический совет

Если часто конвертируешь типы данных:

1. Лучше иметь правильный тип с самого начала

-- ❌ Неправильно
CREATE TABLE orders (
    order_id VARCHAR(20),  -- Хранится как строка
    amount VARCHAR(20)     -- Цена как строка
);

-- ✅ Правильно
CREATE TABLE orders (
    order_id BIGINT,       -- Хранится как число
    amount NUMERIC(10, 2)  -- Цена как число
);

2. Или использовать вью для конвертирования

CREATE VIEW products_numeric AS
SELECT 
    id,
    name,
    price_string,
    CAST(price_string AS INTEGER) AS price
FROM products;

-- Теперь фронтенд работает с приконвертированными данными
SELECT * FROM products_numeric WHERE price > 100;

Итоговая таблица

Рекомендация: Используй CAST для портируемости на другие БД, или :: если специфичен PostgreSQL и хочешь компактнее.