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

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

SELECT *
  FROM timezone
# timezone_id time_offset
1 1 UTC+2
2 2 UTC+3
3 3 UTC+4
4 4 UTC+5
5 5 UTC+6
... ... ...

Например, мы хотим получить все часовые зоны со смещением относительно 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
# timezone_id time_offset hours
1 1 UTC+2 2
2 2 UTC+3 3
3 3 UTC+4 4
4 4 UTC+5 5
5 5 UTC+6 6
6 6 UTC+7 7
7 7 UTC+8 8
8 8 UTC+10 10
9 9 UTC+1 1
10 10 UTC+9 9

Ограничим 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
# int4
1 123

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

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
# type time_offset type number substring type with ::integer type '12'::integer type current_timestamp
1 text text integer integer timestamp with time zone

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

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
# timezone_id time_offset hours
1 9 UTC+1 1
2 1 UTC+2 2
3 2 UTC+3 3
4 3 UTC+4 4
5 4 UTC+5 5

Функция cast

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

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

Например:

SELECT cast('123' AS integer)
# int4
1 123
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
# timezone_id time_offset hours
1 9 UTC+1 1
2 1 UTC+2 2
3 2 UTC+3 3
4 3 UTC+4 4
5 4 UTC+5 5

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

8.4 Простейшие арифметические операции (% ^ !)

8.6 ROUND - округление числа