Типы данных в SQL и CAST: конверсия равна нулю, сумма не сходится

Коротко

Тип колонки — не техническая подробность, а часть смысла числа. Он решает, будет ли у доли дробная часть, сойдётся ли сумма до копейки, попадёт ли в выборку последний день месяца и воспользуется ли база индексом.

• 912 / 4613 в PostgreSQL даёт 0, в DuckDB — 0,1977. Для доли приводите числитель: 912::numeric / 4613 или 912 * 1.0 / 4613.
• Деньги храните и считайте в numeric/DECIMAL. Двадцать процентов от выручки учебной базы в DOUBLE — это 6127,800000000138, в DECIMAL — ровно 6127,80.
• В PostgreSQL нет round(double precision, integer) и sum(boolean): оба запроса падают, хотя в DuckDB работают.
• Текст в число и дату приводят после проверки: строка '19,00' не превращается в число ни в одной из двух баз, а '03.08.2026' становится 3 августа или 8 марта в зависимости от настройки DateStyle.
• BETWEEN '2026-08-01' AND '2026-08-29' по колонке timestamp теряет почти весь последний день: в учебной базе 588 событий 29 августа.
• WHERE event_time::date = … заставляет PostgreSQL прочитать всю таблицу, даже если по event_time есть индекс. Диапазон по исходной колонке его использует.

Типы данных в SQL: какие встречаются аналитику

В учебной базе пять таблиц и шесть типов колонок: INTEGER, DECIMAL, VARCHAR, DATE, TIMESTAMP и BOOLEAN. Седьмой тип, DOUBLE, в таблицах не хранится, но появляется в расчётах: в DuckDB его возвращает avg(amount). В PostgreSQL те же типы называются немного иначе, смысл у них тот же. В таблице ниже — что хранит каждый тип и на чём он ловит.

Тип колонки показывает DESCRIBE users в DuckDB и \d users в psql. Тип выражения — typeof(…) в DuckDB и pg_typeof(…) в PostgreSQL, и проверять стоит именно выражение: count(*) возвращает BIGINT, разность двух дат — целое число дней, а разность двух timestamp — интервал.

Целочисленное деление: одна формула, два ответа

В учебной базе 4 613 пользователей, из них 912 хоть раз платили. count() возвращает целое число, поэтому доля — это деление целого на целое. PostgreSQL в этом случае выполняет целочисленное деление и отбрасывает остаток: 912 / 4613 = 0. DuckDB с версии 0.8 делит так всегда дробно: 912 / 4613 = 0.1977, а целочисленное деление там записывается двойной косой чертой, 912 // 4613 = 0.

SQL Server ведёт себя как PostgreSQL, MySQL и BigQuery — как DuckDB. Поэтому запрос, который «работал», перестаёт работать не из-за правки, а из-за переезда: из ноутбука в хранилище, из одной BI-системы в другую. Худший вариант — когда отчёт строится на PostgreSQL с самого начала. Тогда нули во всех строках выглядят как «данных нет», а не как ошибка.

Та же ловушка ждёт в A/B-тесте. В варианте checklist из 1 541 участника сконвертировались 467, в контроле — 306 из 1 527. sum(converted::int) / count(*) в PostgreSQL даёт 0 и 0, и эффект эксперимента выглядит нулевым. В DuckDB — 0,3030 и 0,2004. Эффект в десять процентных пунктов исчезает на этапе арифметики.

Лечится явным приведением хотя бы одного операнда до деления: count(*)::numeric, CAST(count(*) AS numeric) или умножение на 1.0. Приводить нужно именно операнд. (912 / 4613)::numeric превратит в numeric уже готовый ноль.

SELECT
  count(DISTINCT p.user_id)::numeric
    / NULLIF(count(DISTINCT u.user_id), 0) AS paying_share  -- 0.1977
FROM users u
LEFT JOIN payments p ON p.user_id = u.user_id;

Деньги считаются в DECIMAL, а не в DOUBLE

DOUBLE и float хранят число в двоичной дроби, и большинство десятичных дробей в ней не представимы точно. Классический пример: 0.1 + 0.2 = 0.3 для DOUBLE ложно и в PostgreSQL, и в DuckDB. Для numeric и DECIMAL — истинно.

На учебной базе это видно на обычной бухгалтерской задаче. Двадцать процентов от всех платежей: sum(amount * 0.2). Колонка amount там хранится как DECIMAL(10, 2), и результат — ровно 6127,80. Стоит привести ту же колонку к DOUBLE — в этом типе суммы оказываются, например, после чтения CSV в pandas, — и тот же запрос возвращает 6127,800000000138. Погрешность в десятом знаке после запятой кажется безобидной, пока отчёт не сверяют с бухгалтерией копейка в копейку, а в выгрузке вместо 6127,80 не появляется число с длинным хвостом.

В учебной базе все платежи в целых долларах, поэтому сама сумма amount сошлась бы и в DOUBLE: хвост появляется только после умножения на 0,2. В жизни цены с копейками, скидки в процентах и курсы валют, и накопленная ошибка растёт с числом строк. Правило простое: деньги хранятся в numeric с явной точностью, а DOUBLE оставляют для измерений, где погрешность в последнем знаке ничего не значит: время ответа, координаты, веса модели.

SELECT
  sum(CAST(amount AS DOUBLE PRECISION) * 0.2)  AS vat_double,   -- 6127.800000000138
  sum(amount * 0.2)                            AS vat_decimal   -- 6127.800
FROM payments;

round и тип аргумента

Средний платёж в учебной базе — avg(amount) = 24,491606714628297. Хочется округлить до центов: round(avg(amount), 2). В DuckDB это 24,49. В PostgreSQL для колонки типа double precision тот же запрос падает: function round(double precision, integer) does not exist. Округление до знаков после запятой там определено только для numeric. В учебной базе amount — DECIMAL, поэтому ошибку увидите, только приведя колонку: round(avg(CAST(amount AS double precision)), 2).

Отсюда привычка PostgreSQL-аналитиков писать round(avg(amount)::numeric, 2). Она выглядит лишней, пока не встречается колонка float. И наоборот: в PostgreSQL avg() от целой колонки возвращает numeric, а от float — float. Тип результата агрегата зависит от типа колонки, и одинаковый на вид запрос ведёт себя по-разному на разных таблицах.

Boolean: считать, а не складывать

В таблице experiment_exposures есть колонка converted типа BOOLEAN. В DuckDB sum(converted) работает и возвращает 773: логическое значение там неявно превращается в 0 или 1. В PostgreSQL тот же запрос падает с function sum(boolean) does not exist.

Переносимых записей две, и обе честнее суммы. count(*) FILTER (WHERE converted) отвечает на вопрос «сколько» и не зависит от того, умеет ли база складывать логические значения. avg(converted::int) сразу даёт долю: 0,3030 в варианте checklist и 0,2004 в контроле. Только помните про предыдущий раздел: sum(converted::int) / count(*) в PostgreSQL снова упрётся в целочисленное деление, а avg — нет.

Отдельная история — флаги, которые пришли не как BOOLEAN, а как текст 'true'/'false', '1'/'0' или 'Y'/'N'. Приводить их стоит один раз, в слое очистки, с явным списком допустимых значений. Всё, что в список не попало, должно всплыть как ошибка, а не молча стать false.

SELECT
  variant,
  count(*)                              AS exposed,
  count(*) FILTER (WHERE converted)     AS converted,
  round(100.0 * avg(converted::int), 2) AS conversion_pct
FROM experiment_exposures
GROUP BY variant
ORDER BY variant;

-- checklist 1541 / 467 / 30.30; control 1527 / 306 / 20.04

Текст в число: сначала проверка, потом CAST

Выгрузки из российских систем часто приходят с десятичной запятой: '19,00'. CAST('19,00' AS numeric) падает и в PostgreSQL (invalid input syntax for type numeric), и в DuckDB (Could not convert string). Один такой символ в миллионе строк останавливает весь запрос. Узнаёте вы об этом в момент построения отчёта, а не при загрузке.

DuckDB, Snowflake и SQL Server умеют TRY_CAST: он возвращает NULL вместо ошибки. В PostgreSQL такой функции нет, запись с TRY_CAST там — синтаксическая ошибка. По документации, в PostgreSQL 16 появилась pg_input_is_valid('19,00', 'numeric'): она отвечает false, не падая. Запись, которая работает и в PostgreSQL 14, и в DuckDB, — проверка формата регулярным выражением и CAST внутри CASE: приведение получает только строки, прошедшие проверку.

Порядок работы одинаковый в любой базе. Посчитайте, сколько строк не приводятся. Посмотрите на них глазами: это запятая, пробел-разделитель тысяч, знак валюты или пустая строка. Исправьте формат явно, например replace(value, ',', '.'), и только потом приводите. TRY_CAST без подсчёта NULL на выходе опаснее ошибки: плохие строки тихо исчезнут из суммы.

В первом запросе ниже шесть условных строк выгрузки заданы прямо в тексте запроса. Проверку формата проходит одна, четыре не проходят, шестая — NULL. После удаления пробелов и замены запятой числами становятся ещё две: 19,00 и 1 290,00. Пустая строка и n/a получают NULL: что с ними делать, решает человек. Шаблон строже, чем CAST, который принял бы и -5, и 1e3. Якоря ^ и $ обязательны: без них оператор ~ в PostgreSQL ищет совпадение в любом месте строки, а в DuckDB сравнивает строку целиком. Второй запрос считает те же строки через TRY_CAST в DuckDB: плохих четыре, а сумма равна 19,00 вместо 1 328,00 из первого запроса — строки 19,00 и 1 290.00 выпали из неё молча.

WITH raw_payments(row_id, raw_amount) AS (
  VALUES (1, '19.00'), (2, '19,00'), (3, '1 290.00'),
         (4, ''), (5, 'n/a'), (6, NULL)
),
fixed AS (
  SELECT
    row_id,
    raw_amount,
    replace(replace(raw_amount, ' ', ''), ',', '.') AS fixed_amount
  FROM raw_payments
)
SELECT
  row_id,
  raw_amount,
  raw_amount ~ '^[0-9]+([.][0-9]+)?$' AS is_number,
  CASE WHEN fixed_amount ~ '^[0-9]+([.][0-9]+)?$'
       THEN CAST(fixed_amount AS DECIMAL(12, 2)) END AS amount
FROM fixed
ORDER BY row_id;

-- 1  '19.00'     true   19.00
-- 2  '19,00'     false  19.00
-- 3  '1 290.00'  false  1290.00
-- 4  ''          false  NULL
-- 5  'n/a'       false  NULL
-- 6  NULL        NULL   NULL

WITH raw_payments(row_id, raw_amount) AS (
  VALUES (1, '19.00'), (2, '19,00'), (3, '1 290.00'),
         (4, ''), (5, 'n/a'), (6, NULL)
)
SELECT
  count(*) FILTER (WHERE raw_amount IS NOT NULL
                     AND TRY_CAST(raw_amount AS DECIMAL(12, 2)) IS NULL) AS bad_rows,  -- 4
  sum(TRY_CAST(raw_amount AS DECIMAL(12, 2)))                            AS total      -- 19.00
FROM raw_payments;

Текст в дату: формат задаёт не строка, а настройка

Строка '03.08.2026' выглядит однозначно для человека из России: 3 августа. PostgreSQL читает её по настройке DateStyle. При ISO, MDY, стандартной для многих установок, '03.08.2026'::date — это 8 марта 2026 года. При ISO, DMY — 3 августа. Один и тот же запрос на двух серверах с разными настройками раскладывает события по разным месяцам и не выдаёт ни одной ошибки. Даты, где день больше двенадцати, упадут, но только они: остальные будут прочитаны неверно.

Лечится явным форматом: to_date('03.08.2026', 'DD.MM.YYYY') в PostgreSQL, strptime('03.08.2026', '%d.%m.%Y') в DuckDB. Формат записан в запросе и не зависит от сервера. Ещё лучше хранить даты в ISO-формате 2026-08-03 с самого начала: его все базы читают одинаково.

Timestamp и дата: BETWEEN теряет последний день

Колонка event_time в учебной базе имеет тип TIMESTAMP. Запрос «события за 1–29 августа» часто пишут так: WHERE event_time BETWEEN '2026-08-01' AND '2026-08-29'. Правая граница — дата, и при сравнении с timestamp она превращается в полночь 29 августа. Всё, что случилось 29-го после 00:00, в выборку не попадает. В учебной базе это 588 событий: весь день, кроме полуночи, в которую не было ни одного события.

BETWEEN возвращает 15 757 событий, полуоткрытый интервал event_time >= '2026-08-01' AND event_time < '2026-08-30' — 16 345. Разница ровно в один день. На графике это последний столбец, упавший почти до нуля, и вопрос «что сломалось 29 августа», на который нет ответа в продукте.

Полуоткрытый интервал — правило для любых периодов по времени: начало включено, конец исключён. Он одинаково работает для дат и timestamp, не требует «23:59:59.999» и не оставляет щелей между соседними периодами. Подробнее о границах периодов и часовых поясах — в статье про даты в SQL.

Приведение в WHERE выключает индекс

Самый короткий способ отобрать события одного дня — WHERE event_time::date = '2026-08-29'. Результат правильный, но в PostgreSQL он дорогой. Индекс построен по значениям event_time, а условие сравнивает не их, а результат функции от них. Воспользоваться индексом база не может и читает таблицу целиком.

На синтетической таблице в 200 000 событий с индексом по event_time PostgreSQL строит для такого условия Seq Scan с фильтром (event_time)::date = …. Для диапазона event_time >= '2026-08-29' AND event_time < '2026-08-30' — Index Only Scan с условием по индексу. Результат одинаковый, но через индекс база читает 2 215 строк одного дня, а без него — все 200 000. В учебной базе индекса нет, и блок ниже просто показывает, что оба условия возвращают одни и те же 588 событий.

Правило: приведение и функции ставьте на константу, а не на колонку. Если без функции от колонки не обойтись, например нужен поиск по lower(email), в PostgreSQL строят индекс по выражению: CREATE INDEX … ON users (lower(email)). Условие должно совпадать с выражением индекса буквально.

-- Seq Scan: Filter: ((event_time)::date = '2026-08-29'::date)
SELECT 'cast' AS filter_by, count(*) AS events
FROM events
WHERE event_time::date = DATE '2026-08-29'

UNION ALL

-- Index Only Scan: Index Cond: (event_time >= … AND event_time < …)
SELECT 'range', count(*)
FROM events
WHERE event_time >= DATE '2026-08-29'
  AND event_time <  DATE '2026-08-30'
ORDER BY filter_by;

-- cast 588, range 588

CAST, :: и неявное приведение

CAST(x AS numeric) — стандартная запись, она работает везде. x::numeric — сокращение PostgreSQL, которое понимает и DuckDB, но не SQL Server и не MySQL. Для запросов, которые будут жить в разных системах, надёжнее CAST. Для работы в одной базе :: короче и читается не хуже.

Неявное приведение — отдельная тема. Базы сами приводят типы при сравнении и в функциях, и правила у них разные: DuckDB складывает boolean, PostgreSQL — нет. Сравнение текстовой колонки с числом в одной базе приведёт текст к числу, в другой — число к тексту, в третьей упадёт. Неявное приведение работает, пока данные чистые, и ломается в тот день, когда в колонку попала строка с запятой. Явный CAST там, где тип меняется по смыслу, — дешёвая страховка.

Одна граница приведения вместо десяти

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

Приведение типов — работа слоя очистки: одного CTE, view или модели, через которую данные входят во все отчёты. Там строка становится числом или датой, флаг — boolean, сумма — numeric с точностью до копеек. Там же считается, сколько строк не прошло приведение, и это число мониторят. Отчёты работают уже с правильными типами и не делают ни одного CAST.

Частые ошибки с типами

Почти все ошибки с типами проходят без сообщения об ошибке. Их ловят сверкой с числом, посчитанным другим способом, или переносом запроса в другую базу.

• Целое, делённое на целое, в PostgreSQL и SQL Server: доля 0 вместо 0,198.
• Приведение уже поделённого результата: (a / b)::numeric сохраняет ноль.
• Деньги в DOUBLE: 6127,800000000138 вместо 6127,80.
• round(double, 2) и sum(boolean) в PostgreSQL: запрос из DuckDB падает.
• TRY_CAST без подсчёта NULL: плохие строки молча выпадают из суммы.
• Дата из строки без явного формата: 3 августа превращается в 8 марта.
• BETWEEN с датой по timestamp: теряется последний день периода.
• Функция от колонки в WHERE: полное сканирование вместо индекса.

Практика на учебной базе

Три задачи. База та же, что в песочнице симулятора SQL-аналитика; песочница работает на DuckDB, поэтому для проверки поведения PostgreSQL подумайте, что изменится там.

Первая. Посчитайте конверсию по вариантам эксперимента в процентах с двумя знаками. Ответ: checklist 30,30, control 20,04. Затем запишите запрос так, чтобы в PostgreSQL он дал тот же ответ, а не 0 и 0.

Вторая. Посчитайте события за 1–29 августа двумя способами — через BETWEEN и через полуоткрытый интервал. Ответ: 15 757 и 16 345. Объясните разницу одним числом: 588.

Третья. Посчитайте двадцать процентов от каждого платежа и сложите их дважды: сначала приведя amount к DOUBLE, потом в исходном DECIMAL. Ответ: 6127,800000000138 и 6127,80.

Чеклист перед тем, как отдать расчёт

Типы проверяют до того, как смотрят на числа: неправильный тип даёт правдоподобный результат.

• Каждое деление: хотя бы один операнд приведён к numeric до деления, знаменатель защищён NULLIF(…, 0).
• Деньги: колонка и промежуточные расчёты в numeric/DECIMAL с явной точностью.
• Округление: аргумент round приведён к numeric, если запрос будет жить в PostgreSQL.
• Boolean: считается через FILTER или avg(flag::int), а не через sum(flag).
• Текст в число и дату: посчитано, сколько строк не приводится, формат даты записан явно.
• Периоды по timestamp: полуоткрытый интервал, а не BETWEEN.
• Фильтры: функции и приведения стоят на константах, а не на индексированных колонках.

Итог

Тип определяет смысл числа не меньше, чем формула. Одна и та же доля бывает нулём и 19,8%, одна и та же сумма — круглой и с хвостом в десятом знаке после запятой, один и тот же месяц — полным и без последнего дня. Приводите типы явно, один раз и там, где данные входят в расчёт. И проверяйте запрос в той базе, где он будет работать, а не только там, где его написали.

Симулятор SQL-аналитика проходит всё это на заданиях с автоматической проверкой: от первых запросов к учебной базе до метрик и экспериментов. Первые главы — устройство базы, SELECT и WHERE — открыты бесплатно.