Типы данных для хранения вещественных чисел в PostgreSQL

В статье рассматриваются особенности типов данных для хранения вещественных чисел в 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.

Поля типов данных фиксированной длины не могут вытесняться в TOSAT-таблицу, переменной длины (numeric) могут.

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 может привести к плохо диагностируемым ошибкам. Например, столбец хранит дальность полёта самолёта, при тестировании на маленькие расстояния самолёт приземляется с точностью до миллиметра, а при полёте на большие расстояния с точностью до километра.

При округлении 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 

 Заключение

 Для обработки десятичных чисел можно использовать numeric, а не float4 и float8. Преимущество numeric в том, что обычно, в столбцах хранятся небольшие числа и поля numeric используют меньше места, чем типы фиксированной длины. Точность вычислений при использовании numeric не меньше 16 разрядов, то есть не хуже, чем у float8. Точности float4 (real) может быть недостаточно: всего 6 десятичных разрядов. Все три типа данных поддерживают значения Infinity NaN -Infinity.