1. Введение

2. Целочисленные типы

Дамп выполнения test-substance

my_user@engine:~/tests_pqlib$ ./test-substance begin test-substance.done in 01 min 32.091 sec

Во время тестирования добавления 1000000 записей в таблицу substances, у меня родилась идея наклонить SQL-тип smallint в сторону переполнения целого(Integer Underflow). Для чего написал тест, который добавляет в таблицу substances записи со значениеми атрибута kind, удовлетворяющие условию переполнения целого(Integer Underflow). В id будут записываться итерации выполнения теста. По ним мы сможем судить на какой итерации произошло это переполнение.

Как мы можем увидеть из дампа ниже, SQL-тип smallint соответствует С-типу signed short int, а С-тип usigned short int не может быть использован, т.к. неизбежно приведет к переполнению целого. Поэтому С-тип usigned short лучше не использовать, чтобы не обременять себя лишними проверками. Так же по аналогии с SQL-типом smallint, с SQL-типами integer и bigint не стоит использовать C-типы unsigned long int и соответственно unsigned long long int.

Файлы исходного кода и данных test- substance

Makefile
test-config.h
test-common.h
test-common.c
test-overtype.c

Дамп psql(1). Выполнение test-substance

my_user@engine:~/testdb/tests_pqlib$ ./test-overtype
begin test-overtype
test at Integer Underflow for unsigned short int!
10 step: test 32762 value
9 step: test 32763 value
8 step: test 32764 value
7 step: test 32765 value
6 step: test 32766 value
5 step: test 32767 value
4 step: test 32768 value
to cause Integer Underflow(for short)!
PQexecParams(): ERROR: smallint вне диапазона
test at Integer Underflow for short int!
110 step: test 65530 value
PQexecParams(): ERROR: smallint вне диапазона
done in 000 min 0.190 sec


test_db=# SELECT * FROM substances;
   kind | class | id
--------+-------+----
  32762 |   0   | 10
  32763 |   0   | 9
  32764 |   0   | 8
  32765 |   0   | 7
  32766 |   0   | 6
  32767 |   0   | 5
(6 rows)


К таким SQL-типам относятся decimal и numеric. SQL-тип numеric позволяет масштабировать значения десятичных чисел до 1^1000 и производить c ними точные вычисления. Этот тип является полностью эквивалентным к decimal.

Тип numеric рекомендуется использовать для хранения денежно-финансовых сумм, где требуется точность вычислений с ними. Этот тип должен применяться взамен монетарного типа.

Декларация типа numеric состоит из двух частей: дробной части и целой. Дробная часть определяет количество разрядов после запятой в правой части. Целая часть определяет количество разрядов(имеется в виду в десятичном исчислении) до запятой в левой части числа. Принято следующее представление SQL-типа:

NUMERIC(precision, scale)

где precision — задает масштаб целой части. Масштаб применяется для ограничения ее размерности. Значение precision должно быть в диапазоне 1 от 1000. Значение precision равное 1,2 запрещают использование целой части, потому что значение атрибута типа numeric должено быть меньше единицы. Соответственно, значение precision равное 3 устанавливает лимит значений атрибута типа numeric меньше 10, 4 — меньше 100, 5 — меньше 1000 и т.д.

scale задет количество разрядов в правой части, т.е. задает количество знаков после запятой. Так scale равное 2 установит соответственно два знака после запятой, поэтому число 0.239 будет округлено по правилам математического округления до 0.24 и записано в таблицу.

test_db=# INSERT INTO price_list(product,price) VALUES('Какой-то продукт', 0.239);
INSERT 0 1
test_db=# SELECT * from price_list WHERE product='Какой-то продукт';
  product         | price
------------------+-------
Какой-то продукт  | 0.24
(1 запись)


Рассмотрим следующий пример. Создадим таблицу price_list с двумя атрибутами текстовым product и числовым price. Соответственно, назначим атрибуту price тип TEXT, а атрибуту product тип NUMERIC(10,2), что нам позволит существенно сэкономить память, которая будет выделяться для каждой записи. Таким образом, мы устанавливаем максимальное число 9 999 999 999 для значения атрибута NUMERIC, что будет вряд ли превышено в средних компаниях чьи финансовые операции вряд перевалят за 1 трлн. рублей за одну операцию, что не во всех финансовых институтах и организациях это мы сможем наблюдать. В то же время, например для розничной торговли можно использовать ограничение в 1 млрд. рублей.

test_db=# CREATE TABLE price_list ( product text, price numeric ( 10, 2 ) );

Теперь напишем небольшой тест test-numeric, который будет вносить записи в таблицу price_list . А именно цены на овощи:

Наименование продукции	Цена
Champignon	3,10$
Garlic	2,60$
Zucchini	2,00$
Fennel	1,80$
Tomato	2,58$
Apium	3,00$
Salad	3,00$
Beet root	0,15$
Radish	0,95$
Garden radish	2,05$
Punch	6,00$
Brazilian Pepper Tree	7,12$
Red pepper	7,00$
Capsicum annuum	6,50$
Cucumber	2,30$
Daucus carota	1,10$
Wild carrot	0,91$
Allium porrum	2,45$
Allium	6,00$
Red onion	1,00$
Cilantro	10,10$
Celeriac	1,76$
Potatoes	0,60$
Cauliflower	2,10$
Borecole boot	1,46$
White cabbage	1,00$
Chinese cabbage	2,61$
Squash	3,50$

Исходный код программы test-numeric

Makefile
test-config.h
test-common.h
test-common.c
test-numeric.c

Дамп выполнения test-numeric

my_user@engine:~/Work/learned_database/testdb/tests_pqlib$ ./test-numeric begin test-numeric done in 000 min 0.475 sec

test_db=# SELECT product, to_char(price,'9999999990D99"р."') AS price FROM price_list; SELECT product, to_char(price,'9999999990D99"р."') FROM price_list;
product                | price
-----------------------+-----------------
Champignon             | 3,10$
Garlic                 | 2,60$
Zucchini               | 2,00$
Fennel                 | 1,80$
Tomato                 | 2,58$
Apium                  | 3,00$
Salad                  | 3,00$
Beet root              | 0,15$
Radish                 | 0,95$
Garden radish          | 2,05$
Punch                  | 6,00$
Brazilian Pepper Tree  | 7,12$
Red pepper             | 7,00$
Capsicum annuum        | 6,50$
Cucumber               | 2,30$
Daucus carota          | 1,10$
Wild carrot            | 0,91$
Allium porrum          | 2,45$
Allium                 | 6,00$
Red onion              | 1,00$
Cilantro               | 10,10$
Celeriac               | 1,76$
Potatoes               | 0,60$
Cauliflower            | 2,10$
Borecole boot          | 1,46$
White cabbage          | 1,00$
Chinese cabbage        | 2,61$
Squash                 | 3,50$
(28 rows)


Типы real и double относятся к неточным числовым типам. Под неточностью тут понимается то, что некоторые значения не могут быть непосредственно конвертированы к внутреннему формату и сохраняются приближено. Поэтому в результате сохранения и перевода этих значений этого типа могут иметь некоторые отличные значения.

Если есть необходимость в соблюдении точности вычислений, например в денежно-финансовых суммах, рекомендуется использовать NUMERIC. В то же время, если Вы хотите использовать типы с плавающей точкой в каких-либо важных вычислениях, особенно, где возможно возникновение пограничных случаев( бесконечности и переполнения), Вам необходимо использовать их очень осторожно. Ещё нужно помнить, что сравнение двух чисел с плавающей точкой может приводить к их эквивалентности или не приводить.

При использовании на сборках ядра для 32-х разрядной архитектуре x86, точность составляет диапазон значений 1E-37 – 1E+37 для одиночной точности с количеством знаков после запятой не менее 6-ти и диапазона значений 1E-307 – E+308 для двойной точности с кол-вом знаков не менее 15-ти после запятой.

Эти типы позволяют индексировать записи по уникальным числовым значениям в таблицах вместо использования специальных последовательностей, как показано ниже в примере.

Следующая запись:

CREATE TABLE tablename (
       colname SERIAL
     );


будет эквивалентна:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
       colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
     );


В этой таблице создаётся целочисленный столбец, который по-умолчанию увеличивает на одно значение каждую запись, используя для этого генератор последовательности. В следующем примере мы увидим, что после создания таблицы tablename вместе с ней была создана последовательность.

postgres=# CREATE TABLE tablename (
postgres(# colname SERIAL
postgres(# );
postgres=# \d
public | tablename | таблица | postgres
public | tablename_colname_seq | последовательность | postgres
postgres=# \d+ tablename
colname | integer | not null default nextval('tablename_colname_seq'::regclass) |


Как видно из дампа psql(1), после того как мы создали таблицу tablename с единственным атрибутом colname, по-мимо таблицы ещё была создана последовательность tablename_colname_seq. Поэтому утверждать, что данный тип позволяет избежать использование последовательностей не совсем верно.

Если мы взглянем на таблицу tablename более внимательнее то увидим, что кроме контрольного ограничения NOT NULL ещё используется DEFAULT, который с помощью встроенной функции nextval() вызывает последовательность, в результате обращения к которой возвращается следующее значение colname и которое будет затем, записано в только что созданную запись(new) таблицы.

Чтобы пощупать это на практике предлагаю добавить в таблицу tablename текстовый столбец dsc и затем добавить в неё несколько записей, а потом посмотреть её содержимое через SELECT.

postgres=# INSERT INTO tablename(dsc) VALUES('one');
postgres=# INSERT INTO tablename(dsc) VALUES('two');
postgres=# INSERT INTO tablename(dsc) VALUES('three');
postgres=# SELECT * FROM tablename;
colname | dsc
--------+-----------
  1     | one
  2     | two
  3     | three


При использовании типа SERIAL в атрибуте colname автоматически создается ограничение NOT NULL, которое вводит запрет на ввод нулевых значений. Но, так как вам скорее всего захочется использовать ограничение UNIQUE или PRIMARY KEY, чтобы исключить вероятность появления дублирующих значений, то вам нужно будет устанавливать эти контрольные ограничения самим, так как они не создаются автоматически. Ниже показано как добавлять в таблицу контрольное ограничение для исключения дублирующих значений атрибута colname в записях таблицы tablename.

Дамп psql(1). Выполнение ALTER TABLE(добавление контрольных ограничений)

6. Символьные типы

Дамп psql(1). Примеры использования символьных типов. Исключительные ситуации

postgres=#CREATE TABLE test2 (b varchar(5));
postgres=#INSERT INTO test2 VALUES ('ok');
postgres=#INSERT INTO test2 VALUES ('good ');
postgres=#INSERT INTO test2 VALUES ('too long');
ERROR: value too long for type character varying(5)
postgres=#INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
postgres=#SELECT b, char_length(b) FROM test2;
  b     | char_length
--------+-------------
  ok    |    2
  good  |    5
  too l |    5


После выполнения проверочного SELECT мы выясняем, что too long был оберзан доtoo l, до длинны, которая составляет 5-ть символов.

Вернуться в Доки-Токи

Copyright © 2010 rjaan as Andrey Rjavskov(Rzhavskov) <rjaan@yandex.ru> <arjavskov@gmail.com>