Числовые типы

Числовые типы PostgreSQL используются для представления целых и вещественных значений. С общей точки зрения к числовым типам PostgreSQL относятся следующие типы:

• 2-, 4- и 8-байтовые целые числа;
• 4- и 8-байтовые вещественные числа;
• дробные числа с фиксированной точностью.

Помимо перечисленных в PostgreSQL существуют некоторые специальные типы, также относимые к категории числовых, в том числе устаревший тип money и специальная конструкция serial (табл. 3.13).

Таблица 3.13. Числовые типы PostgreSQL

Как видно из табл. 3.13, у некоторых типов данных PostgreSQL имеются синонимы, полностью эквивалентные исходным типам. Синонимы были созданы для удобства, хотя иногда это приводит к недоразумениям, поскольку некоторые синонимы встречаются в других языках программирования. Если не знать, с каким типом ассоциируется тот или иной синоним, возможны случайные ссылки на другие типы данных. Например, в PostgreSQL типы real и doubl e представляют значения, которые во многих языках относятся к типу float; при этом у обоих типов имеются синонимы, имена которых содержат слово «float» (float и flot8 относятся к double precision, float4 относится к real). Если вы попытаетесь использовать синоним f I oat, полагая, что он связан с типом real, возникнут проблемы, поскольку в действительности этот синоним связан с типом double precision.

Тип numeric Тип numeric (также называемый типом decimal) предназначен для представления сколь угодно больших или малых значений с фиксированной точностью, задаваемой пользователем. При создании таблицы с полем типа numeric в круглых скобках указываются два значения: точность и масштаб. Точность определяет максимальное количество цифр (включая цифры в дробной части), а масштаб определяет количество цифр только в дробной части. Если параметры не заданы, по умолчанию точность равна 30, а масштаб — 6. Максимальная точность (а следовательно, и максимальный размер), задаваемая таким образом, равна 1000. На практике 1000 цифр обычно вполне достаточно.

ПРИМЕЧАНИЕ

Нарушение точности и масштаба полей типа numeric не всегда приводит к выдаче сообщения об ошибке в PostgreSQL.

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

Например, в поле numeric( 11.6) можно безопасно сохранить значение 9.99999999 с лишними цифрами в дробной части (хотя оно будет округлено до 10.000000). С другой стороны, как видно из листинга 3.22, попытка сохранения числа 99999.99999999 завершается неудачей.

Проблемы, возникающие при вставке слишком длинных чисел, можно легко обойти. Для этого в команду INSERT включается функция trunc(), усекающая число по размерам поля. При вызове функции передается требуемое количество позиций, поэтому вы должны знать, с какими параметрами определялось поле. Применение функции trunc() продемонстрировано в листинге 3.22.

Листинг 3.22. Предотвращение ошибок переполнения

booktcwn=# INSERT INTO numbers

VALUES (9.99999999);

INSERT 3390697 1

booktown=# SELECT * FROM numbers:

number

10.000000 (1 row)

booktown=# INSERT INTO

numbers VALUES (9999.99999999);

ERROR: overflow on numeric

AMS(value) >= 10*5 for field with precision

11 scale 6 booktown=# INSERT INTO

numbers VALUES (trunc(99999.99999999.6));

INSERT 3390698 1

booktown=# SELECT * FROM

numbers; number

10.000000 99999.999999 (2 rows)

booktown=# INSERT INTO

numbers VALUES (trunc0.99999999. 6));

INSERT 3390699 1

booktown=# SELECT * FROM numbers;

number

10.000000 99999.999999 9.999999

(3 rows)

Тип money

Тип money предназначен для хранения денежных величин и обычных чисел. На момент написания книги тип money считается устаревшим и использовать его не рекомендуется. В книге он представлен лишь как один из действующих типов, который все еще может встречаться в существующих системах PostgreSQL.

Вместо типа money следует использовать тип numeric с масштабом 2 и точностью, достаточной для представления максимальной необходимой величины (включая две цифры для дробной части). Форматирование, аналогичное типу money, выполняется при помощи функции to_char(), используемой в листинге 3.23. В этом примере продемонстрирован оператор конкатенации и функция форматирования текста ltrim(), о которых рассказано в главе 4.

Листинг 3.23. Использование типа numeric вместо money

booktown=# CREATE TABLE money_example (money_cash money.

booktown(# numeric_cash numeric(10,2)):

CREATE

booktown=# INSERT INTO money_example VALUES C$12.241, 12.24);

INSERT 3391095 1

booktown=# SELECT * FROM money_example;

money_cash | numeric_cash

$12.24 | 12.24 (1 row)

booktown=# SELECT money_cash,

booktown-# '$' | ltrim(to_char(numenc_cash. '9999.99'))

booktown-# AS numeric_cashif1ed

booktown-# FROM money_example;

money_cash | numeric_cashified

$12.24 j 12.24

(1 row)

Тип serial

Хотя тип serial не относится к числу стандартных типов, он часто используется при создании в таблице полей-идентификаторов, содержащих уникальное значение для каждой записи. В типе serial объединены функциональные возможности 4-байтового типа integer, индекса и последовательности. В листинге 3.24 тип serial генерирует уникальный идентификатор для каждой записи в таблице auto_identified.

В листинге 3.25 та же задача решается при помощи поля типа integer, функции nextval() и последовательности (последовательности описаны в главе7). На момент написания книги эти два способа были функционально тождественными.

Листинг 3.24. Использование типа serial

booktown=# CREATE TABLE autojdentified (id_serial);

NOTICE: CREATE TABLE will create implicit sequence '

auto Jdentif led Jd_seq'

for SERIAL column 'auto_identified.id'

NOTICE: CREATE TABLE/UNIQUE will create

Implicit index 'auto_identified_1d_key' for table

'autojdentified'

CREATE

Листинг 3.25. Решение задачи «вручную»

booktown=# CREATE SEQUENCE autojdentified id_seq;

CREATE

booktown=# CREATE TABLE autojdentified

booktown-# (id integer UNIQUE DEFAULT

nextval('autojdentifiedjd_seq'));

NOTICE: CREATE TABLE/UNIQUE will create implicit index

'autojdentifiedjdjcey' for table

'autojdentified'

CREATE

ВНИМАНИЕ

После удаления таблицы последовательность, созданная для типов senal, автоматически не удаляется. При удалении таблиц, содержащих поля типа serial, эти последовательности должны удаляться отдельно.

Дата и время

Следующая категория типов является удобным средством для хранения даты и времени в универсальной структуре SQL. При этом программист избавляется от хлопот, связанных с форматом хранения (как, например, при хранении этих данных в символьных строках). В PostgreSQL все вычисления с датой и временем производятся но юлианскому календарю. Продолжительность года считается равной 365,24 дня, что обеспечивает правильное представление любой даты с 4713 года до нашей эры и далеко в будущее.

В PostgreSQL поддерживаются все типы даты и времени, определенные в стандарте SQL92 (табл. 3.14), а также некоторые вспомогательные типы PostgreSQL, помогающие решить проблемы с представлением часовых поясов в SQL92.

Таблица 3.14. Типы даты и времени

timestamp with time zone, 8 байт Календарная дата и время От 1903 г. н. э.

timestamp с часовым поясом до 2037 г. н. э.

interval 12 байт Общий промежуток От-1780 000 000 лет

времени до 1 780 000 000 лет

Совместимость

Для сохранения совместимости с предыдущими версиями PostgreSQL разработчики продолжают поддерживать типы данных datetime и timespan. Тип datetime эквивалентен timestamp, а тип timespan — типу interval.

К числу типов даты/времени также относятся типы abstime и reltime, обладающие пониженной точностью представления. Тем не менее это внутренние типы PostgreSQL, которые могут исчезнуть в следующих версиях. Старайтесь использовать только SQL-совместимые типы данных и как можно скорее устранить устаревшие типы из существующих приложений.

Формат представления даты

В PostgreSQL предусмотрено несколько стандартных форматов даты, в том числе формат ISO-8601, традиционный формат SQL, исходный формат PostgreSQL и многие другие. Некоторые форматы даты указаны в табл. 3.15.

Перечисленные форматы относятся к типам данных date и timestamp.

Таблица 3.15. Форматы представления даты

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

Таблица 3.16. Сокращенные обозначения месяцев

В табл. 3.17 приведены аналогичные сокращения для дней недели.

Таблица 3.17. Сокращенные обозначения дней недели

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

ПРИМЕЧАНИЕ

Хотя даты можно форматировать при помощи строковых функций (таких, как to_char()), гораздо эффективнее установить по умолчанию как можно более близкое представление и лишь затем применять ручное преобразование типов и форматирование текста.

Общий формат вывода даты/времени устанавливается применением команды SET к переменной DATESTYLE. Переменной может быть присвоено одно из четырех стандартных значений, перечисленных в табл. 3.18.

Таблица 3.18. Константы форматов даты

Например, формат SQL устанавливается следующей командой SQL:

booktown=# SET DATESTYLE TO SQL;

SET VARIABLE

Если выполнить запрос SELECT current_timestamp после присваивания, PostgreSQL вернет текущее время в формате SQL:

booktown=# SELECT current_timestamp;

timestamp

08/10/2001 13:25:55.00 PDT (1 row)

Вывод текущего значения переменной DATESTYLE во время работы PostgreSQL производится следующей командой:

booktown=# SHOW DATESTYLE;

NOTICE: DateStyle is SQL

with US (NonEuropean)

conventions SHOW VARIABLE

Помимо общих форматов, представление даты в PostgreSQL зависит от другого фактора: порядка перечисления компонентов (табл. 3.19). Этот порядок перечисления определяет, должен ли в выводимой дате день следовать за месяцем или наоборот. Порядок перечисления компонентов применяется к четырем общим форматам знакомой командой SET DATESTYLE и не изменяет в формате ничего, кроме относительного расположения дня и месяца.

Таблица 3.19. Дополнительные форматы вывода даты

Более того, общий формат и относительный порядок дня/месяца можно задать в одной команде SET с разделением констант запятыми. Порядок перечисления констант в команде SET не важен, если они не являются взаимоисключающими (например, SQL и ISO). Пример приведен в листинге 3.26.

Листинг 3.26. Выбор формата даты

booktown=# SET DATESTYLE TO ISO,US;

SET VARIABLE

booktown=# SHOW DATESTYLE;

NOTICE: DateStyle is ISO with

US (NonEuropean) conventions

SHOW VARIABLE

booktown=f SET DATESTYLE TO

NONEUROPEAN. GERMAN;

SET VARIABLE

booktown=# SHOW DATESTYLE;

NOTICE: DateStyle is German

with European conventions

SHOW VARIABLE

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

Хотя переменная DATESTYLE является удобным средством настройки формата даты, необходимо помнить, что она относится к категории переменных времени исполнения, а это означает, что настройка действует лишь на протяжении текущего сеанса. Существует два способа присвоить переменной DATESTYLE значение по умолчанию, чтобы его не приходилось указывать заново при каждом сеансе.

• Изменение переменной среды PGDATESTYLE на сервере, на котором работает процесс postmaster. Например, при использовании командного интерпретатора bash можно добавить строку PGDATESTYLE="SQL US" в файл .bash_profile пользователя postgres. При запуске процесса postmaster пользователем postgres переменная PGDATESTYLE будет автоматически применяться ко всем операциям форматирования даты и времени, выполняемым PostgreSQL.
• Изменение переменной среды PGDATESTYLE, используемой клиентским приложением (при условии, что оно было написано с применением библиотеки libpq) в начале сеанса. Этот вариант выбирается в том случае, если формат вывода должен настраиваться клиентом, а не сервером. Например, присваивая значение переменной PGDATESTYLE командой export в приглашении bash перед запуском клиента psql, вы задаете формат, который будет использоваться в работе psql.

Форматы представления времени

Время, как и дата, может задаваться разными способами. В табл. 3.20 перечислены самые распространенные форматы значений типов time и time with time zone.

Taблица 3.20. Форматы представления времени

В дополнение к этим форматам в PostgreSQL предусмотрена возможность уточнения времени в типах time и time with time zone. Дополнительные форматы перечислены в табл. 3.21.

Таблица 3.21. Допустимые форматы часового пояса

ПРИМЕЧАНИЕ

В PostgreSQL поддерживаются все сокращенные обозначения часовых поясов, предусмотренные в стандарте ISO.

Tnntime with time zone поддерживается в PostgreSQL в основном для сохранения совместимости с существующими стандартами SQL и другими СУБД. Если вам потребуется работать с часовыми поясами, рекомендуется использовать тип timestamp, описанный в следующем пункте. Это объясняется прежде всего тем, что из-за действия летнего времени осмысленная интерпретация часовых поясов иногда возможна лишь при наличии даты.

Во внутреннем представлении PostgreSQL вся информация о часовых поясах хранится в виде числового смещения от времени по Гринвичу (GMT), также называемого UTC (Universal Coordinated Time). По умолчанию PostgreSQL выводит время в часовом поясе, заданном в файле конфигурации операционной системы. Если вы хотите, чтобы время выводилось в другом часовом поясе, это можно сделать четырьмя способами.

• Настройка переменной среды TZ на сервере. Переменная используется для определения часового пояса по умолчанию при запуске postmaster. Например, она может задаваться в файле .bash_profile пользователя postgres командой export TZ='zone'.
• Настройка переменной среды PGTZ на стороне клиента. Переменная среды PGTZ может быть прочитана любым клиентом, написанным с использованием библиотеки libpq. Значение интерпретируется как стандартный часовой пояс клиента.
• Команда SQL SET TIMEZONE ТО. Команда устанавливает для текущего сеанса заданный часовой пояс (например, SET TIMEZONE TO UTC).
• Секция SQL AT TIME ZONE. Согласно стандарту SQL92 значение секции задается в виде обозначения часового пояса (например, PST) или интервала (например, Interval ( -07:00')). Секция AT TIME ZONE включается в команду SQL после значения времени (например, SELECT my_t1mestamp AT TIME ZONT 'PST').

ПРИМЕЧАНИЕ

Если переменная часового пояса содержит недопустимое значение, в большинстве систем по умолчанию используется время по Гринвичу (GMT). Кроме того, если при компиляции PostgreSQL был задан ключ USE_AUSTRALIAN_RULES, обозначение EST относится к австралийскому восточному стандартному времени (смещение +10.00 часов по отношению к GMT), а не к восточному стандартному времени США.

Тип timestamp

Тип timestamp PostgreSQL сочетает функциональные возможности типов date и time. Формат timestamp состоит из даты, за которой следует минимум один пробел, после чего идет время и необязательный часовой пояс.

В этом формате поддерживаются любые сочетания форматов даты и времени, перечисленные в табл. 3.15 и 3.20. Примеры допустимого ввода в формате timestamp приведены в табл. 3.22.

Таблица 3.22. Примеры данных типа timestamp

ВНИМАНИЕ

Хотя в PostgreSQL поддерживается синтаксис создания полей или значений типа timestamp without time zone, в PostgreSQL 7.1.2 полученный тип данных все равно содержит информацию о часовом поясе.

Интервальный тип

В стандарте SQL92 определен тип данных interval, представляющий фиксированный промежуток времени. Сам по себе тип interval представляет лишь количественную величину, не связанную с определенным начальным или конечным моментом. Интервалы часто используются в сочетании с типами даты и времени для вычисления новой величины посредством сложения или вычитания. Кроме

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

В PostgreSQL поддерживаются два варианта синтаксиса определения интервалов:

длина единице [ago]

длина! единица [. длина2 единица2 ... ]

[ago]

Здесь:

• длина — продолжительность интервала, заданная в виде целого или вещественного (для микросекунд) числа. Интерпретация числа определяется следующим параметром.
• единица — единица, в которой измеряется заданный интервал. Разрешены следующие ключевые слова: second, minute, hour, day, week, month,year, decade, century, millennium. Также допускаются сокращения (любой длины при условии однозначной интерпретации) и формы множественного числа.
• ago — необязательное ключевое слово ago указывает, что описываемый период времени предшествует некоторому моменту, а не следует после него. Ключевое слово ago можно рассматривать как аналог знака минус (-) в типах даты и времени.

В листинге 3.27 приведены примеры объединения типов date и interval. Как видно из листинга, вычитание инвертированного интервала (с ключевым словом ago) эквивалентно прибавлению обычного интервала по аналогии с вычитанием отрицательных чисел.

Листинг 3.27. Операции с интервалами booktown=# SELECT date('1980-06-25');

date

1980-06-25

(1 row)

booktown=# SELECT interval С 21 years 8 days');

interval

21 years 8 days (1 row)

booktown=# SELECT date('1980-06-25') + interval

('21 years 8 days') booktown-# AS spanned_date:

spanned_date

2001-07-03 00:00:00-07

(1 row)

booktown=# SELECT date ('1980-06-25') -

interval ('21 years 8 days ago1)

booktown-# AS twice_inverted_interval_date;

twice_ nverted_interval_date

2001-07-03 00:00:00-07

(1 row)

Встроенные константы даты и времени

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

Таблица 3.23. Константы даты и времени

В PostgreSQL также поддерживаются три встроенные функции для получения текущего времени, даты и их комбинации. Для них были выбраны подходящие имена current_date, current_time и current_timestamp.

Если судить только по именам, константы now и current на первый взгляд кажутся идентичными. В действительности они принципиально различаются по способу хранения в таблице. Константа now транслируется в системное время на момент выполнения команды (например, время вставки, если константа используется в команде INSERT). С другой стороны, константа current обычно применяется в особых случаях (например, при отслеживании сведений о процессах) для вычисления разности между начальным временем, зафиксированным константой now, и текущим временем; результат определяет время выполнения процесса. В листинге 3.28 константы now и current используются для построения журнала задач. Первая команда создает таблицу с полями для хранения имени задачи, начальной и конечной даты/времени. В таблице создаются записи двух задач, при этом начальное время задается константой now, а конечное время — константой current. Из листинга видно, что обе задачи не завершены.

ВМИМАНИЕ

Константы даты/времени, как показано в листинге 3.28, обязательно заключаются в апострофы.

Листинг 3.28. Использование констант current и now

booktown=# CREATE TABLE tasklog

booktown=# (taskname char(15),

booktown=# timebegun timestamp,

booktown=# timeflnished timestamp);

CREATE

booktown=# INSERT INTO tasklog VALUES

booktown=# ('delivery', 'now', 'current'):

INSERT 169936 1

booktown=# INSERT INTO tasklog

VALUES booktown=# ('remodeling', 'now', 'current');

INSERT 169937 1

booktown=# SELECT taskname, tlmefnished - timebegun

booktown-# AS timespent FROM tasklog;

taskname | timespent

delivery | 00:15-.32

remodeling [ 00:04:42

(2 rows)

Итак, константа now обычно используется при сохранении в таблице фиксированного момента времени, который не изменяется при последующих ссылках. Как видно из листинга 3.29, плохое понимание различий между константами now и current приводит к потенциальным ошибкам программирования SQL. В листинге приведены две команды INSERT; в первой команде используется now, а во второй — current. Сравнение результатов показывает, что в первой строке при каждом запросе время обновляется, а во второй строке оно всегда остается одинаковым.

Листинг 3.29. Сравнение констант now и current

booktown=# INSERT INTO shipments (customer_id. isbn, ship_date)

Ccustomer_id, isbn, ship_date)

booktown-# VALUES (1. '039480001X', 'current');

INSERT 3391221 1

booktown=# INSERT INTO shipments (customer_id. isbn, ship_date)

booktown-# VALUES (2. '0394800753'. 'now');

INSERT 3391222 1

booktown=# SELECT isbn. ship_date FROM shipments: isbn | ship_date

039480001X [ current 0394800753 | 2001-08-10 18:17:49-07

(2 rows)

booktown=# SELECT isbn.

booktown-# to_char(ship_date. 'YYYY-MM-DD HH24:MI:SS')

booktown-# AS value

booktown-# FROM shipments; isbn value

039480001X | 2001-08-10 18:21:22 0394800753 | 2001-08-10 18:17:49

(2 rows)

booktown=# SELECT isbn.

booktown-# to_char(ship_date. 'YYYY-MM-DD HH24:MI:SS')

booktown-# AS value

booktown-# FROM shipments;

isbn | value

039480001X | 2001-08-10 18:22:35 0394800753 | 2001-08-10 18:17:49

(2 rows)