Типы данных и приведения

Бран · продюсер «Хроник Пограничья»

Я похвастался Ильве из «Эпохи Корон», что у нас магом играет каждый третий. Она посчитала по нашей базе и получила ноль процентов. Ноль! Кто-то из нас сломал математику?

Математика цела. Ильва разделила одно целое число на другое, и база сделала ровно то, что ей сказали. Чтобы такие сюрпризы не попадали в отчёты, сегодня разбираемся, какого типа бывают значения и как переводить их из одного типа в другой.

Ящики с разной разметкой

Представь склад, где у каждого ящика своя разметка: «только целые болты», «жидкость в литрах», «бумаги с датами». Положить в ящик для целых болтов полтора болта нельзя, он вернёт один.

Каждый столбец таблицы тоже такой ящик. Его тип данных решает, что в нём лежит и что с этим можно делать. Узнать тип любого значения помогает функция pg_typeof:

SELECT pg_typeof(id) AS id,
       pg_typeof(nickname) AS nickname,
       pg_typeof(birth_date) AS birth_date,
       pg_typeof(registered_at) AS registered_at
FROM players
LIMIT 1;

 id      | nickname | birth_date | registered_at
---------+----------+------------+--------------------------
 integer | text     | text       | timestamp with time zone

В «Хрониках» встречаются четыре семейства:

Тип	Что хранит	Пример из базы
integer	целые числа	characters.level, characters.xp
numeric	точные дробные числа, деньги	store_offers.price_rub, purchases.amount_rub
text	строки	players.nickname, players.birth_date
date, timestamptz	даты и моменты времени	players.registered_at

Заметь странность: дата рождения хранится как text. Так бывает, когда данные приехали из старой системы, и аналитику приходится с этим жить.

Ноль процентов магов

Повторим расчёт Ильвы. Сначала два числа. В скобках здесь стоит маленький запрос, который возвращает одно число; подробно такие запросы разберём позже, в блоке про подзапросы.

SELECT (SELECT count(*) FROM characters WHERE class = 'mage') AS mages,
       (SELECT count(*) FROM characters) AS total;

 mages | total
-------+-------
  1278 |  5602

А теперь деление:

SELECT (SELECT count(*) FROM characters WHERE class = 'mage')
     / (SELECT count(*) FROM characters) AS share;

 share
-------
     0

count(*) возвращает целое число. Когда PostgreSQL делит целое на целое, он выполняет целочисленное деление: дробная часть отбрасывается. Как ящик для болтов: 0,228 болта не бывает, значит, ноль.

Проще всего увидеть на маленьких числах:

SELECT 7 / 2 AS a, 7 / 2.0 AS b, 7::numeric / 2 AS c;

 a | b                  | c
---+--------------------+--------------------
 3 | 3.5000000000000000 | 3.5000000000000000

Хватит, чтобы хотя бы одно число было дробным. Литерал 2.0 уже numeric, и деление становится дробным.