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