|
|
|
| Числовые типы Числовые типы PostgreSQL используются для представления целых и вещественных значений. С общей точки зрения к числовым типам PostgreSQL относятся следующие типы:
Помимо перечисленных в 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 значение по умолчанию, чтобы его не приходилось указывать заново при каждом сеансе.
Форматы представления времени Время, как и дата, может задаваться разными способами. В табл. 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 выводит время в часовом поясе, заданном в файле конфигурации операционной системы. Если вы хотите, чтобы время выводилось в другом часовом поясе, это можно сделать четырьмя способами.
ПРИМЕЧАНИЕ Если переменная часового пояса содержит недопустимое значение, в большинстве систем по умолчанию используется время по Гринвичу (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] Здесь:
В листинге 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) |
|
|
|