Учебное пособие СанктПетербург бхвпетербург
 Скачать 1.88 Mb.
|
|
Глава 4 Типы данных СУБД PostgreSQL После первоначального знакомства с языком SQL имеет смысл немного упорядочить полученные вами знания. Речь идет о типах данных, применяемых в СУБД PostgreSQL. Вообще, типы данных — это одно из базовых понятий любого языка программирования, и язык SQL в этом плане не является исключением. PostgreSQL имеет очень разнообразный набор встроенных типов данных, т. е. тех типов, которые СУБД предоставляет в распоряжение пользователя, как говорят, по умолчанию. Мы намеренно упо- требили здесь термин «встроенные», поскольку пользователь имеет возможность создавать и свои собственные типы данных, которые затем можно включить в систему и использовать их так же, как и встроенные. Такая возможность адаптации системы типов данных к конкретным ситуациям является одной из отличительных черт PostgreSQL. В этой главе мы расскажем лишь о самых основных типах данных, поскольку в вашем распоряже- нии всегда имеется полная документация. Настоящее учебное пособие не является ее заменой, оно призвано лишь помочь вам сделать первые шаги в освоении богатого мира типов данных PostgreSQL. Типы данных объединены в группы, в рамках этих групп они имеют некоторые общие свойства, но также они имеют и различия. 4.1. Числовые типы Группа числовых типов данных включает в себя целый ряд разновидностей: цело- численные типы, числа фиксированной точности, типы данных с плавающей точкой, последовательные типы (serial). В составе целочисленных типов находятся следующие представители: smallint, integer, bigint. Если атрибут таблицы имеет один из этих типов, то он позволяет хранить только целочисленные данные. При этом перечисленные типы различают- ся по количеству байтов, выделяемых для хранения данных. В PostgreSQL существу- ют псевдонимы для этих стандартизированных имен типов, а именно: int2, int4 и int8. Число байтов отражается в имени типа. 51 Глава 4. Типы данных СУБД PostgreSQL При выборе конкретного целочисленного типа принимают во внимание диапазон допустимых значений и затраты памяти. Зачастую тип integer считается оптималь- ным выбором с точки зрения достижения компромисса между этими показателями. Числа фиксированной точности представлены двумя типами — numeric и decimal. Однако они являются идентичными по своим возможностям. Поэтому мы будем про- водить изложение на примере типа numeric. Для задания значения этого типа ис- пользуются два базовых понятия: масштаб (scale) и точность (precision). Масштаб показывает число значащих цифр, стоящих справа от десятичной точки (запятой). Точность указывает общее число цифр как до десятичной точки, так и после нее. На- пример, у числа 12.3456 точность составляет 6 цифр, а масштаб — 4 цифры. Параметры этого типа данных указываются в круглых скобках после имени типа: numeric(точность, масштаб). Например, numeric(6, 2). Его главное достоинство — это обеспечение точных результатов при выполнении вы- числений, когда это, конечно, возможно в принципе. Это оказывается возможным при выполнении сложения, вычитания и умножения. Числа типа numeric могут хра- нить очень большое количество цифр: 131 072 цифры — до десятичной точки (запя- той), 16 383 — после точки. Однако нужно учитывать, что такая точность достигается за счет замедления вычислений по сравнению с целочисленными типами и типами с плавающей точкой. При этом для хранения числа затрачивается больше памяти, чем в случае целых чисел. Данный тип следует выбирать для хранения денежных сумм, а также в других случа- ях, когда требуется гарантировать точность вычислений. Представителями типов данных с плавающей точкой являются типы real и double precision. Они представляют собой реализацию стандарта IEEE «Standard 754 for Binary Floating-Point Arithmetic». Тип данных real может представить числа в диа- пазоне, как минимум, от 1E−37 до 1E+37 с точностью не меньше 6 десятичных цифр. Тип double precision имеет диапазон значений примерно от 1E−307 до 1E+308 с точностью не меньше 15 десятичных цифр. При попытке записать в такой столбец слишком большое или слишком маленькое значение будет генерироваться ошибка. Если точность вводимого числа выше допу- стимой, то будет иметь место округление значения. А вот при вводе очень маленьких чисел, которые невозможно представить значениями, отличными от нуля, будет ге- нерироваться ошибка потери значимости, или исчезновения значащих разрядов (an underflow error). 52 4.1. Числовые типы При работе с числами таких типов нужно помнить, что сравнение двух чисел с пла- вающей точкой на предмет равенства их значений может привести к неожиданным результатам. Например: SELECT 0.1::real * 10 = 1.0::real; ?column? ---------- f (1 строка) В дополнение к обычным числам эти типы данных поддерживают и специальные значения Infinity (бесконечность), −Infinity (отрицательная бесконечность) и NaN (не число). PostgreSQL поддерживает также тип данных float, определенный в стандарте SQL. В объявлении типа может использоваться параметр: float(p). Если его значение лежит в диапазоне от 1 до 24, то это будет равносильно использованию типа real, а если же значение лежит в диапазоне от 25 до 53, то это будет равносильно исполь- зованию типа double precision. Если же при объявлении типа параметр не исполь- зуется, то это также будет равносильно использованию типа double precision. Последним из числовых типов является тип serial. Однако он фактически реализо- ван не как настоящий тип, а просто как удобная замена целой группы SQL-команд. Тип serial удобен в тех случаях, когда требуется в какой-либо столбец вставлять уникальные целые значения, например, значения суррогатного первичного ключа. Синтаксис для создания столбца типа serial таков: CREATE TABLE имя-таблицы ( имя-столбца serial ); Эта команда эквивалентна следующей группе команд: CREATE SEQUENCE имя-таблицы_имя-столбца_seq; CREATE TABLE имя-таблицы ( имя-столбца integer NOT NULL DEFAULT nextval( 'имя-таблицы_имя-столбца_seq' ) ); ALTER SEQUENCE имя-таблицы_имя-столбца_seq OWNED BY имя-таблицы.имя-столбца; 53 Глава 4. Типы данных СУБД PostgreSQL Для пояснения вышеприведенных команд нам придется немного забежать впе- ред. Одним из видов объектов в базе данных являются так называемые последова- тельности. Это, по сути, генераторы уникальных целых чисел. Для работы с этими последовательностями-генераторами используются специальные функции. Одна из них — это функция nextval, которая как раз и получает очередное число из последо- вательности, имя которой указано в качестве параметра функции. В команде CREATE TABLE ключевое слово DEFAULT предписывает, чтобы СУБД использовала в качестве значения по умолчанию то значение, которое формирует функция nextval. Поэтому если в команде вставки строки в таблицу INSERT INTO не будет передано значение для поля типа serial, то СУБД обратится к услугам этой функции. В том случае, ко- гда в таблице поле типа serial является суррогатным первичным ключом, тогда нет необходимости указывать явное значение для вставки в это поле. В заключение скажем, что кроме типа serial существуют еще два аналогичных типа: bigserial и smallserial. Им фактически, за кадром, соответствуют типы bigint и smallint. Поэтому при выборе конкретного последовательного типа нуж- но учитывать предполагаемое число строк в таблице и частоту удаления и вставки строк, поскольку даже для небольшой таблицы может потребоваться большой диа- пазон, если операции удаления и вставки строк выполняются часто. 4.2. Символьные (строковые) типы Стандартные представители строковых типов — это типы character varying(n) и character(n), где параметр указывает максимальное число символов в строке, которую можно сохранить в столбце такого типа. При работе с многобайтовыми ко- дировками символов, например UTF-8, нужно учитывать, что речь идет о символах, а не о байтах. Если сохраняемая строка символов будет короче, чем указано в опреде- лении типа, то значение типа character будет дополнено пробелами до требуемой длины, а значение типа character varying будет сохранено так, как есть. Типы character varying(n) и character(n) имеют псевдонимы varchar(n) и char(n) соответственно. На практике, как правило, используют именно эти краткие псевдонимы. PostgreSQL дополнительно предлагает еще один символьный тип — text. В столбец этого типа можно ввести сколь угодно большое значение, конечно, в пределах, уста- новленных при компиляции исходных текстов СУБД. 54 4.2. Символьные (строковые) типы Документация рекомендует использовать типы text и varchar, поскольку такое отличительное свойство типа character, как дополнение значений пробелами, на практике почти не востребовано. В PostgreSQL обычно используется тип text. Константы символьных типов в SQL-командах заключаются в одинарные кавычки. SELECT 'PostgreSQL'; ?column? ------------ PostgreSQL (1 строка) В том случае, когда в константе содержится символ одинарной кавычки или обратной косой черты, их необходимо удваивать. Например: SELECT 'PGDAY''17'; ?column? ---------- PGDAY'17 (1 строка) В том случае, когда таких символов в константе много, все выражение становится трудно воспринимать. На помощь может прийти использование удвоенного символа «$». Эти символы выполняют ту же роль, что и одинарные кавычки, когда в них за- ключается строковая константа. При использовании символов «$» в качестве ограни- чителей уже не нужно удваивать никакие символы, содержащиеся в самой константе: ни одинарные кавычки, ни символы обратной косой черты. Например: SELECT $$PGDAY'17$$; ?column? ---------- PGDAY'17 (1 строка) Возможность использования символов доллара в роли ограничителей символь- ной константы не является частью стандарта SQL. Это расширение, предлагаемое PostgreSQL. Подробно об этом написано в разделе документации 4.1.2.4 «Строковые константы, заключенные в доллары». 55 Глава 4. Типы данных СУБД PostgreSQL PostgreSQL предлагает еще одно расширение стандарта SQL — строковые константы в стиле языка C. Чтобы иметь возможность их использовать, нужно перед начальной одинарной кавычкой написать символ «E». Например, для включения в константу символа новой строки нужно сделать так: SELECT E'PGDAY\n17'; ?column? ---------- PGDAY + 17 (1 строка) При использовании C-стиля необходимо удваивать обратную косую черту, если тре- буется поместить ее в константу буквально. А для включения в содержимое констан- ты символа обратной кавычки можно либо удвоить ее, либо сделать так: SELECT E'PGDAY\'17'; ?column? ---------- PGDAY'17 (1 строка) Подробно об использовании C-стиля написано в разделе документации 4.1.2.2 «Стро- ковые константы со спецпоследовательностями в стиле C». 4.3. Типы «дата/время» PostgreSQL поддерживает все типы данных, предусмотренные стандартом SQL для даты и времени. Даты обрабатываются в соответствии с григорианским календарем, причем это делается даже в тех случаях, когда дата относится к тому моменту време- ни, когда этот календарь в данной стране еще не был принят. Для этих типов данных предусмотрены определенные форматы для ввода значений и для вывода. Причем эти форматы могут не совпадать. Важно помнить, что при вводе значений их нужно заключать в одинарные кавычки, как и текстовые строки. Начнем рассмотрение с типа date. Рекомендуемый стандартом ISO 8601 формат вво- да дат таков: «yyyy-mm-dd», где символы «y», «m» и «d» обозначают цифру года, ме- сяца и дня соответственно. PostgreSQL позволяет использовать и другие форматы 56 4.3. Типы «дата/время» для ввода, например: «Sep 12, 2016», что означает 12 сентября 2016 года. При выво- де значений PostgreSQL использует формат по умолчанию, если не предписан другой формат. По умолчанию используется формат, рекомендуемый стандартом ISO 8601: «yyyy-mm-dd». SELECT '2016-09-12'::date; date ------------ 2016-09-12 (1 строка) А в следующем примере используется другой формат ввода, но формат вывода оста- ется тот же самый, поскольку мы его не изменяли: SELECT 'Sep 12, 2016'::date; date ------------ 2016-09-12 (1 строка) Чтобы «сказать» СУБД, что введенное значение является датой, а не простой сим- вольной строкой, мы использовали операцию приведения типа. В PostgreSQL она оформляется с использованием двойного символа «двоеточие» и имени того типа, к которому мы приводим данное значение. Важно учесть, что при выполнении при- ведения типа производится проверка значения на соответствие формату целевого типа и множеству его допустимых значений. В PostgreSQL предусмотрен целый ряд функций для работы с датами и временем. На- пример, для получения значения текущей даты служит функция current_date. Ее особенностью является то, что при ее вызове круглые скобки не используются. SELECT current_date; date ------------ 2016-09-21 (1 строка) Если нам требуется вывести дату в другом формате, то для разового преобразования формата можно использовать функцию to_char, например: SELECT to_char( current_date, 'dd-mm-yyyy' ); 57 Глава 4. Типы данных СУБД PostgreSQL СУБД выведет: to_char ------------ 21-09-2016 (1 строка) Обратите внимание, что для демонстрации возможностей СУБД по работе с датами нам даже не потребовалось создавать таблицу, а оказалось достаточно лишь команды SELECT. Для хранения времени суток служат два типа данных: time и time with time zone. Первый из них хранит только время суток, а второй — дополнительно — еще и ча- совой пояс. Однако документация на PostgreSQL не рекомендует использовать тип time with time zone, поскольку смещение (offset), соответствующее конкретному часовому поясу, может зависеть от даты перехода на летнее время и обратно, но в этом типе дата отсутствует. При вводе значений времени допустимы различные фор- маты, например: SELECT '21:15'::time; При выводе СУБД дополнит введенное значение, в котором присутствуют только ча- сы и минуты, секундами. time ---------- 21:15:00 (1 строка) Чтобы «сказать» СУБД, что введенное значение является значением времени, а не простой символьной строкой, мы опять использовали операцию приведения типа. Предложим СУБД заведомо недопустимое значение времени, например: SELECT '25:15'::time; Получим такое сообщение об ошибке: ОШИБКА: значение поля типа date/time вне диапазона: "25:15" СТРОКА 1: select '25:15'::time; ^ А теперь возьмем значение, которое включает еще и секунды. SELECT '21:15:26'::time; 58 4.3. Типы «дата/время» time ---------- 21:15:26 (1 строка) А если число секунд недопустимое, то опять получим сообщение об ошибке. SELECT '21:15:69'::time; ОШИБКА: значение поля типа date/time вне диапазона: "21:15:69" СТРОКА 1: select '21:15:69'::time; ^ Время можно вводить не только в 24-часовом формате, но и в 12-часовом, при этом нужно использовать дополнительные суффиксы am и pm. Например: SELECT '10:15:16 am'::time; time ---------- 10:15:16 (1 строка) SELECT '10:15:16 pm'::time; time ---------- 22:15:16 (1 строка) Для получения значения текущего времени служит функция current_time. При ее вызове круглые скобки не используются. SELECT current_time; timetz -------------------- 23:51:57.293522+03 (1 строка) Текущее время выводится с высокой точностью и дополняется числовым значением, соответствующим локальному часовому поясу, который установлен в конфигураци- онном файле сервера PostgreSQL. В приведенном примере значение часового пояса равно +03, но если ваш компьютер находится в другом часовом поясе, то это значе- ние будет другим, например, для регионов Сибири оно может быть +08. 59 Глава 4. Типы данных СУБД PostgreSQL В результате объединения типов даты и времени получается интегральный тип — временная отметка. Этот тип существует в двух вариантах: с учетом часового поя- са — timestamp with time zone, либо без учета часового пояса — timestamp. Для первого варианта существует сокращенное наименование — timestamptz, которое является расширением PostgreSQL. При вводе и выводе значений этого типа данных используются соответствующие форматы ввода и вывода даты и времени. Вот при- мер с учетом часового пояса: SELECT timestamp with time zone '2016-09-21 22:25:35'; timestamptz ------------------------ 2016-09-21 22:25:35+03 (1 строка) Обратите внимание, что хотя мы не указали явно значение часового пояса при вводе данных, при выводе это значение +03 было добавлено. А это пример без учета часового пояса: SELECT timestamp '2016-09-21 22:25:35'; timestamp --------------------- 2016-09-21 22:25:35 (1 строка) В рассмотренных примерах мы использовали синтаксис тип 'строка' для указа- ния конкретного типа простой литеральной константы. Имя типа мы указывали не после преобразуемого литерала, а перед ним, например, timestamp '2016-09-21 22:25:35'. Строго говоря, это не является операцией приведения типа, хотя и по- хоже на нее. Подробно данный вопрос рассмотрен в разделах документации 4.1.2.7 «Константы других типов» и 4.2.9 «Приведения типов». Для получения значения текущей временн´ой отметки (т. е. даты и времени в одном значении) служит функция current_timestamp. Она также вызывается без указа- ния круглых скобок. Приведем пример ее использования. SELECT current_timestamp; now ------------------------------- 2016-09-27 18:27:37.767739+03 (1 строка) 60 4.3. Типы «дата/время» Здесь в выводе присутствует и часовой пояс: +03. Оба типа — timestamp и timestamptz — занимают один и тот же объем 8 байтов, но значения типа timestamptz хранятся, будучи приведенными к нулевому часовому поясу (UTC), а перед выводом приводятся к часовому поясу пользователя. На практике при принятии решения о том, какой из этих двух типов — timestamp или timestamptz — использовать, необходимо учитывать, требуется ли значения, хранящиеся в таблице, приводить к местному часовому поясу или не требуется. На- пример, в расписании авиарейсов указывается местное время как для аэропорта от- правления, так и для аэропорта прибытия. Поэтому в таком случае нужно использо- вать тип timestamp, чтобы это время не приводилось к текущему часовому поясу пользователя, где бы он ни находился. Из двух этих типов данных чаще используется timestamptz. Последним типом является interval, который представляет продолжительность от- резка времени между двумя моментами времени. Его формат ввода таков: |