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

Числа не всегда хранятся в базе данных как числа. Они могут находиться внутри текста. Посмотри на time_offset таблицы timezone:

SELECT *
  FROM timezone

Например, мы хотим получить все часовые зоны со смещением относительно UTC не более 5 часов.

Разобьем задачу на части:

1. Получим сдвиг относительно UTC числом;
2. Ограничим 5 часами.

Получим сдвиг относительно UTC числом

Мы знаем, что в поле time_offset данные хранятся в формате UTC+СДВИГ_В_ЧАСАХ. Возьмем подстроку, начиная с 5-го символа:

SELECT t.timezone_id,
       t.time_offset,
       right(t.time_offset, -4) AS hours
  FROM timezone t

Ограничим 5 часами

Получив сдвиг в часах, мы сделали почти всю работу. Осталось лишь добавить WHERE right(t.time_offset, -4) <= 5:

SELECT t.timezone_id,
       t.time_offset,
       right(t.time_offset, -4) AS hours
  FROM timezone t
 WHERE right(t.time_offset, -4) <= 5

error: operator does not exist: text <= integer

Упс... Что-то пошло не так...

Postgres говорит, что нельзя сравнивать текст с числом.

Для исправления ошибки нужно преобразовать подстроку, полученную right(t.time_offset, -4), к типу данных integer (целые числа). Сделать это можно одним из способов:

• использовав конструкцию ::
• вызвав функцию cast()

Конструкция ::

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

SELECT '123'::integer

Посмотрим на типы данных в нашем запросе:

SELECT pg_typeof (t.time_offset) AS "type time_offset",
       pg_typeof (right(t.time_offset, -4)) AS "type number substring",
       pg_typeof (right(t.time_offset, -4)::integer) AS "type with ::integer",
       pg_typeof ('12'::integer) AS "type '12'::integer",
       pg_typeof (current_timestamp) AS "type current_timestamp"
  FROM timezone t
 LIMIT 1

Доделаем работу до конца

SELECT t.timezone_id,
       t.time_offset,
       right(t.time_offset, -4)::integer AS hours
  FROM timezone t
 WHERE right(t.time_offset, -4)::integer <= 5
 ORDER BY hours

Функция cast

Функция cast делает ровно то же самое. Синтаксис

cast (значение AS тип данных)

Например:

SELECT cast('123' AS integer)

SELECT t.timezone_id,
       t.time_offset,
       cast(right(t.time_offset, -4) AS integer) AS hours
  FROM timezone t
 WHERE cast(right(t.time_offset, -4) AS integer) <= 5
 ORDER BY hours

P.S. Функция cast включена в стандарт SQL и доступна в СУБД других производителей. Синтаксис через :: является историческим для PostgreSQL. На практике чаще используют ::, потому что так короче.