В статье рассматриваются особенности типов данных для хранения вещественных чисел в PostgreSQL.
Типы данных PostgreSQL для работы с вещественными числами:
1) float4, синоним real, синоним float(1..24)
2) float8, синоним float, синоним double precision, синоним float(25..53)
3) numeric, синоним decimal (десятичные числа). Диапазон для этого типа значительный: 131072 цифр до точки и 16383 цифр после точки. Но если при определении типа указать numeric(точность, масштаб), то максимальные значения точности и масштаба 1000. numeric можно объявить с отрицательным масштабом: значения могут округляться до десятков, сотен, тысяч.
Во всех этих типах данных кроме чисел и null могут храниться значения Infinity, -Infinity, NaN.
Поля типов float4 и float8 имеют фиксированную ширину, а такие типы данных не могут вытесняться в TOSAT-таблицу. Поля переменной ширины (numeric) могут. Если длина строки превышает примерно 2000 байт, то часть полей будет вытеснена в TOAST.
При вычислениях нужно учитывать правила округления значений и точность вычислений (approx или exact). float4 обеспечивает точность 6 разрядов (значащих чисел в десятичной системе счисления), float8 обеспечивает точность 15 разрядов. Последний разряд округляется:
select 12345678901234567890123456789.1234567890123456789::float4::numeric;
numeric
-------------------------------
12345700000000000000000000000
(1 row)
select 12345678901234567890123456789.1234567890123456789::float8::numeric;
numeric
-------------------------------
12345678901234600000000000000
(1 row)
На картинке красным цветом выделены шестой и пятнадцатый разряды, которые были округлены. В том же первом примере на картике видно, что разряды больше шестого и пятнадцатого были заменены нулями, что значит что точность не сохраняется.
Недостаток типов float4 и float8 данных в том, что добавление к большому числу маленького числа эквивалентно добавлению нуля:
select (12345678901234567890123456789.1234567890123456789::float8 + 123456789::float8)::numeric;
numeric
-------------------------------
12345678901234600000000000000
(1 row)В примере добавление значения 123456789::float8 эквивалентнно добавлению нуля.
При добавлении к numeric точность сохраняется и разряды не теряются:
select 1234567890123456789.123456789::numeric + 0.00000000000000000000123456789::numeric as numeric;
numeric
---------------------------------------------------
1234567890123456789.12345678900000000000123456789
(1 row)Использование float может привести к плохо диагностируемым ошибкам. Например, столбец хранит дальность полёта самолёта, при тестировании на маленькие расстояния самолёт приземляется с точностью до миллиметра, а при полёте на большие расстояния с точностью до километра. Сравнения значений могут быть проблематичны:
select 1.1::numeric::float8,
1.1::numeric::float4,
1.1::numeric::float8-1.1::numeric::float4 compare;
float8 | float4 | compare
--------+--------+-------------------------
1.1 | 1.1 | -2.3841857821338408e-08
(1 row)При округлении float8 учитывается шестнадцатый разряд:
Параметр конфигурации extra_float_digits
Параметром extra_float_digits можно уменьшить число цифр в текстовом представлении чисел float8, float4 и геометрических типов. Диапазон значений от -15 до 3 включительно
значения 1,2,3 эквивалентны. Параметр влияет только на отображение, на вычисления и приведения к типу numeric не влияет
Влияет на текстовое представление float8, float4 и геометрических типов. Значение по умолчанию 1. Значения параметра extra_float_digits 1,2,3 эквивалентны:
show extra_float_digits;
1
select 1234567890.123456789::float8, 1.123456789::float4;
float8 | float4
---------------------------+--------------------
1234567890.1234567 | 1.1234568
(1 row)
set extra_float_digits = 3;
select 1234567890.123456789::float8, 1.123456789::float4;
float8 | float4
---------------------------+--------------------
1234567890.1234567 | 1.1234568
(1 row)
Значение ноль и отрицательные значения убирают из вывода разряды с округлением:
set extra_float_digits = 0;
select 1234567890.123456789::float8, 1.123456789::float4;
float8 | float4
---------------------------+--------------------
1234567890.12346 | 1.12346
(1 row)
set extra_float_digits = -1;
select 1234567890.123456789::float8, 1.123456789::float4;
float8 | float4
---------------------------+--------------------
1234567890.1235 | 1.1235
(1 row)
set extra_float_digits = -2;
select 1234567890.123456789::float8, 1.123456789::float4;
float8 | float4
---------------------------+--------------------
1234567890.123 | 1.123
(1 row)
set extra_float_digits = -5;
select 1234567890.123456789::float8, 1.123456789::float4;
float8 | float4
---------------------------+--------------------
1234567890 | 1
(1 row)
Параметр конфигурации extra_float_digits влияет только на представление (отображение, вывод). На вычисления и приведения к типу numeric не влияет:
select 1234567890.123456789::float8::numeric, 1.123456789::float4::numeric;
float8 | float4
---------------------------+--------------------
1234567890.12346 | 1.12346
(1 row)Округление может убрать много разрядов:
reset extra_float_digits;
select 234567890.199999989::float8::numeric, 1.19999999123::float4::numeric;
float8 | float4
---------------------------+--------------------
234567890.2 | 1.2
(1 row)
Хранение вещественных чисел
Тип numeric имеет переменную длину и для небольших чисел хранит данные компактнее, чем float8 : точность 15 "десятичных разрядов", цифр в десятичном виде, то есть цифр до и после точки в десятичном виде, то есть если разрядов не хватает, то убираются десятичные и потом целочисленные цифры и заменяются нулями.
Функция проверки размера поля также показывает занимаемое полями место:
select pg_column_size(c1), pg_column_size(c2), pg_column_size(c3) from t5;
pg_column_size | pg_column_size | pg_column_size
----------------+----------------+----------------
8 | 4 | 5
8 | 4 | 13
8 | 4 | 5
8 | 4 | 7Все три типа данных поддерживают значения Infinity NaN -Infinity. Пример:
truncate t5;
insert into t5 values ('Infinity', 'Infinity', 'Infinity');
insert into t5 values ('NaN', 'NaN', 'NaN');
select * from t5;
c1 | c2 | c3
----------+----------+----------
Infinity | Infinity | Infinity
NaN | NaN | NaN
(2 rows)
select lp_off, lp_len, t_hoff, t_data from heap_page_items(get_raw_page('t5','main',0)) order by lp_off;
lp_off | lp_len | t_hoff | t_data
--------+--------+--------+----------------------------------
8096 | 39 | 24 | \x000000000000f87f0000c07f0700c0
8136 | 39 | 24 | \x000000000000f07f0000807f0700d0
(2 rows)
Разрядность результата деления numeric
Разрядность результата деления двух чисел типа numeric:
1) не менее 16 значащих цифр, то есть не хуже, чем float8
2) не меньше, чем разрядность любого из входных параметров.
Для вычисления квадратного корня и других операций с потерей точности действует аналогичное правило. Для операторов сложения, вычитания, умножения и других потери точности нет.
Пример:
insert into t5 values (1,1, 1.000000000000000000000000000000000001/3);
select lp, lp_off, lp_len, t_hoff, t_data from heap_page_items(get_raw_page('t5','main',0))
order by lp desc limit 1;
lp | lp_off | lp_len | t_hoff | t_data
----+--------+--------+--------+-----------------------------------
5 | 7912 | 57 | 24 | \x000000000000f03f0000803f2b7f92050d050d050d050d050d050d050d050d060d
(5 rows)
select * from t5 order by ctid desc limit 1;
c1 | c2 | c3
----+----+---------------------------------------
1 | 1 | 0.333333333333333333333333333333333334 
Типы данных Oracle Database NUMBER, FLOAT, DOUBLE соответствуют типу numeric в PostgreSQL, что стоит учитывать при миграции с Oracle на Postgres. Типы данных, соответствующие float4 и float8, появились только в версиии Oracle Database 10g и называются BINARY_FLOAT и BINARY_DOUBLE. Опции, в том числе, Oracle Spatial Data Option (обработка пространственных данных, геометрия, «инженерные расчёты») используют NUMBER. В коммерческих приложениях важна точность вычислений.
Заключение
В статье рассмотрены особенности работы с типами данных в PostgreSQL. Для обработки десятичных чисел можно использовать numeric, а не float4 и float8, которые используют двоичные вычисления (binary). Если в столбцах таблиц хранятся небольшие числа, то поля numeric используют меньше места, чем типы фиксированной длины. Точность вычислений при использовании numeric не меньше 16 разрядов, то есть не хуже, чем у float8. Точности float4 (real) может быть недостаточно: всего 6 десятичных разрядов. Все три типа данных поддерживают значения Infinity NaN -Infinity.