Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
SELECT E'PGDAY\n17'; ?column? ---------- PGDAY + 17 (1 строка) 40 При использовании 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 позволяет использовать и другие форматы для ввода, например: «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 строка) 41 Чтобы «сказать» СУБД, что введенное значение является датой, а не простой сим- вольной строкой, мы использовали операцию приведения типа. В PostgreSQL она оформляется с использованием двойного символа «двоеточие» и имени того типа, к которому мы приводим данное значение. Важно учесть, что при выполнении приве- дения типа производится проверка значения на соответствие формату целевого типа и множеству его допустимых значений. В PostgreSQL предусмотрен целый ряд функций для работы с датами и временем. На- пример, для получения значения текущей даты служит функция current_date. Ее осо- бенностью является то, что при ее вызове круглые скобки не используются. SELECT current_date; date ------------ 2016-09-21 (1 строка) Если нам требуется вывести дату в другом формате, то для разового преобразования формата можно использовать функцию to_char(), например: SELECT to_char( current_date, 'mm-dd-yyyy' ); СУБД выведет: 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 строка) Чтобы «сказать» СУБД, что введенное значение является значением времени, а не простой символьной строкой, мы опять использовали операцию приведения типа. Предложим СУБД заведомо недопустимое значение времени, например: 42 SELECT '25:15'::time; Получим такое сообщение об ошибке: ОШИБКА: значение поля типа date/time вне диапазона: "25:15" СТРОКА 1: select '25:15'::time; ^ А теперь возьмем значение, которое включает еще и секунды. SELECT '21:15:26'::time; 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 строка) 43 Текущее время выводится с высокой точностью и дополняется числовым значением, соответствующим локальному часовому поясу, который установлен в конфигураци- онном файле сервера PostgreSQL. В приведенном примере значение часового пояса равно +03, но если ваш компьютер находится в другом часовом поясе, то это значение будет другим, например, для регионов Сибири оно может быть +08. В результате объединения типов даты и времени получается интегральный тип — временная отметка. Этот тип существует в двух вариантах: с учетом часового поя- са — 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 строка) В рассмотренных примерах мы использовали синтаксис type 'string' для указания конкретного типа простой литеральной константы. Имя типа данных мы указыва- ли не после преобразуемого литерала, а перед ним, например, 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 строка) Здесь в выводе присутствует и часовой пояс: «+03». Оба типа данных — timestamp и timestamptz — занимают один и тот же объем 8 бай- тов, но значения типа timestamptz хранятся, будучи приведенными к нулевому часо- вому поясу (UTC), а перед выводом приводятся к часовому поясу пользователя. 44 На практике при принятии решения о том, какой из этих двух типов — timestamp или timestamptz — использовать, необходимо учитывать, требуется ли значения, храня- щиеся в таблице, приводить к местному часовому поясу или не требуется. Например, в расписании авиарейсов указывается местное время как для аэропорта отправле- ния, так и для аэропорта прибытия. Поэтому в таком случае нужно использовать тип timestamp, чтобы это время не приводилось к текущему часовому поясу пользовате- ля, где бы он ни находился. Из двух этих типов данных чаще используется timestamptz. Последним типом является interval, который представляет продолжительность от- резка времени между двумя моментами времени. Его формат ввода таков: quantity unit [quantity unit ...] direction Здесь unit означает единицу измерения, а quantity — количество таких единиц. В ка- честве единиц измерения можно использовать следующие: microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium. Параметр direction может принимать значение ago (т. е. «тому назад») либо быть пустым. При- ведем примеры: SELECT '1 year 2 months ago'::interval; interval ------------------ -1 years -2 mons (1 строка) Обратите внимание, что параметр ago заставляет СУБД добавить знак «минус» перед всеми полями. Можно использовать альтернативный формат, предлагаемый стандартом ISO 8601: P [ years-months-days ] [ T hours:minutes:seconds ] Здесь строка должна начинаться с символа «P», а символ «T» разделяет дату и время (все выражение пишется без пробелов). Например: SELECT 'P0001-02-03T04:05:06'::interval; interval ------------------------------- 1 year 2 mons 3 days 04:05:06 (1 строка) Поскольку интервал — это отрезок времени между двумя временн ´ыми отметками, то значение этого типа можно получить при вычитании одной временн´ой отметки из другой. SELECT ('2016-09-16'::timestamp - '2016-09-01'::timestamp)::interval; interval ---------- 15 days (1 строка) 45 Как мы уже говорили ранее, в PostgreSQL предусмотрен целый ряд функций для рабо- ты с датами и временем. Например, для получения значений текущей даты, текущего времени и текущей временн´ой отметки (т. е. даты и времени в одном значении) слу- жат функции current_date, current_time, current_timestamp. Эти функции часто при- меняются для получения значений по умолчанию при вставке строк в таблицы. Их особенностью является то, что при их вызове круглые скобки не используются. Для получения полной информации обратитесь к документации (раздел 9.9 «Операторы и функции даты/времени»). Значения временн ´ых отметок можно усекать с той или иной точностью с помощью функции date_trunc(). Например, для получения текущей временн´ой отметки с точ- ностью до одного часа нужно сделать так: SELECT ( date_trunc( 'hour', current_timestamp ) ); date_trunc ------------------------ 2016-09-27 22:00:00+03 (1 строка) Из значений временн ´ых отметок можно с помощью функции extract() извлекать от- дельные поля, т. е. год, месяц, день, число часов, минут или секунд и т. д. Например, чтобы извлечь номер месяца, нужно сделать так: SELECT extract( 'mon' FROM timestamp '1999-11-27 12:34:56.123459' ); date_part ----------- 11 (1 строка) Напомним, что выражение timestamp '1999-11-27 12:34:56.123459' не означает опе- рацию приведения типа. Оно присваивает тип данных timestamp литеральной кон- станте. 4.4 Логический тип Логический (boolean) тип может иметь три состояния: «true» и «false», а также неопре- деленное состояние, которое можно представить значением NULL. Таким образом, тип boolean реализует трехзначную логику. В качестве состояния «true» могут служить следующие значения: TRUE, 't', 'true', 'y', 'yes', 'on', '1'. В качестве состояния «false» могут служить следующие значения: FALSE, 'f', 'false', 'n', 'no', 'off', '0'. В качестве примера создадим таблицу с двумя столбцами и добавим в нее несколько строк: CREATE TABLE databases ( is_open_source boolean, dbms_name text ); 46 INSERT INTO databases VALUES ( TRUE, 'PostgreSQL' ); INSERT INTO databases VALUES ( FALSE, 'Oracle' ); INSERT INTO databases VALUES ( TRUE, 'MySQL' ); INSERT INTO databases VALUES ( FALSE, 'MS SQL Server' ); Теперь выполним выборку всех строк из этой таблицы: SELECT * FROM databases; is_open_source | dbms_name ----------------+--------------- t | PostgreSQL f | Oracle t | MySQL f | MS SQL Server (4 строки) Выберем только СУБД с открытым исходным кодом: SELECT * FROM databases WHERE is_open_source; is_open_source | dbms_name ----------------+------------ t | PostgreSQL t | MySQL (2 строки) Обратите внимание, что в условии WHERE для проверки логических значений можно не писать выражение WHERE is_open_source = 'yes', а достаточно просто указать имя столбца, содержащего логическое значение: WHERE is_open_source. 4.5 Массивы PostgreSQL позволяет создавать в таблицах такие столбцы, в которых будут содер- жаться не скалярные значения, а массивы переменной длины. Эти массивы могут быть многомерными и могут содержать значения любого из встроенных типов, а так- же типов данных, определенных пользователем. Предположим, что нам необходимо сформировать и сохранить в базе данных в удоб- ной форме графики работы пилотов авиакомпании, т. е. номера дней недели, когда они совершают полеты. Создадим таблицу, в которой эти графики будут храниться в виде единых списков, т. е. в виде одномерных массивов. CREATE TABLE pilots ( pilot_name text, schedule integer[] ); CREATE TABLE 47 Для указания на то, что это массив, нужно добавить квадратные скобки к наимено- ванию типа данных. При этом задавать число элементов не обязательно. Давайте добавим в таблицу четыре строки. Массив в команде вставки представлен в виде строкового литерала с указанием типа данных и квадратных скобок, означаю- щих массив. Обратите внимание, что все массивы имеют различное число элемен- тов. INSERT INTO pilots VALUES ( 'Ivan', '{ 1, 3, 5, 6, 7 }'::integer[] ), ( 'Petr', '{ 1, 2, 5, 7 }'::integer[] ), ( 'Pavel', '{ 2, 5 }'::integer[] ), ( 'Boris', '{ 3, 5, 6 }'::integer[] ); INSERT 0 4 Посмотрим, что получилось: SELECT * FROM pilots; pilot_name | schedule ------------+------------- Ivan | {1,3,5,6,7} Petr | {1,2,5,7} Pavel | {2,5} Boris | {3,5,6} (4 строки) Предположим, что руководство компании решило, что каждый пилот должен летать 4 раза в неделю. Значит, нам придется обновить значения в таблице. Пилоту по имени Boris добавим один день с помощью операции конкатенации: UPDATE pilots SET schedule = schedule || 7 WHERE pilot_name = 'Boris'; UPDATE 1 Пилоту по имени Pavel добавим один день в конец списка (массива) с помощью функ- ции array_append: UPDATE pilots SET schedule = array_append( schedule, 6 ) WHERE pilot_name = 'Pavel'; UPDATE 1 Ему же добавим один день в начало списка с помощью функции array_ prepend (об- ратите внимание, что параметры функции поменялись местами): UPDATE pilots SET schedule = array_prepend( 1, schedule ) WHERE pilot_name = 'Pavel'; UPDATE 1 48 У пилота по имени Ivan имеется лишний день в графике. С помощью функции array_remove удалим из графика пятницу (второй параметр функции указывает зна- чение элемента массива, а не индекс): UPDATE pilots SET schedule = array_remove( schedule, 5 ) WHERE pilot_name = 'Ivan'; UPDATE 1 У пилота по имени Petr изменим дни полетов, не изменяя их общего количества. Вос- пользуемся индексами для работы на уровне отдельных элементов массива. По умол- чанию нумерация индексов начинается с единицы, а не с нуля. При необходимости ее можно изменить. К элементам одного и того же массива можно обращаться в пред- ложении SET по отдельности, как будто это разные столбцы. UPDATE pilots SET schedule[ 1 ] = 2, schedule[ 2 ] = 3 WHERE pilot_name = 'Petr'; UPDATE 1 А можно было бы, используя срез (slice) массива, сделать и так: UPDATE pilots SET schedule[ 1:2 ] = ARRAY[ 2, 3 ] WHERE pilot_name = 'Petr'; UPDATE 1 В вышеприведенной команде запись 1:2 означает индексы первого и последнего эле- ментов диапазона массива. Нотация с использованием ключевого слова ARRAY — это альтернативный способ создания массива (он соответствует стандарту SQL). Таким образом, присваивание новых значений производится сразу целому диапазону эле- ментов массива. SELECT * FROM pilots; pilot_name | schedule ------------+----------- Boris | {3,5,6,7} Pavel | {1,2,5,6} Ivan | {1,3,6,7} Petr | {2,3,5,7} (4 строки) Теперь продемонстрируем основные операции, которые можно применять к масси- вам, выполняя выборки из таблиц. Получим список пилотов, которые летают каждую среду: SELECT * FROM pilots WHERE array_position( schedule, 3 ) IS NOT NULL; 49 pilot_name | schedule ------------+----------- Boris | {3,5,6,7} Ivan | {1,3,6,7} Petr | {2,3,5,7} (3 строки) Функция array_position возвращает индекс первого вхождения элемента с указанным значением в массив. Если же такого элемента нет, она возвратит NULL. Выберем пилотов, летающих по понедельникам и воскресеньям: SELECT * FROM pilots WHERE schedule @> '{ 1, 7 }'::integer[]; pilot_name | schedule ------------+----------- Ivan | {1,3,6,7} (1 строка) Оператор @> означает проверку того факта, что в левом массиве содержатся все эле- менты правого массива. Конечно, при этом в левом массиве могут находиться и дру- гие элементы, что мы и видим в графике этого пилота. Еще аналогичный вопрос: кто летает по вторникам и/или по пятницам? Для получе- ния ответа воспользуемся оператором &&, который проверяет наличие общих эле- ментов у массивов, т. е. пересекаются ли их множества значений. В нашем примере число общих элементов, если они есть, может быть равно одному или двум. Здесь мы также использовали нотацию с ключевым словом ARRAY, а не '{ 2, 5 }'::integer[]. Вы можете применять ту, которая принята в рамках выполнения вашего проекта. SELECT * FROM pilots WHERE schedule && ARRAY[ 2, 5 ]; pilot_name | schedule ------------+----------- Boris | {3,5,6,7} Pavel | {1,2,5,6} Petr | {2,3,5,7} (3 строки) Сформулируем вопрос в форме отрицания: кто не летает ни во вторник, ни в пятни- цу? Для получения ответа добавим в предыдущую SQL-команду отрицание NOT: |