Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
SELECT '2016-05-18'::date; date ------------ 2016-05-18 (1 строка) Продолжим экспериментирование с параметром datestyle. Давайте изменим его значение. Сделать это можно многими способами, но мы упомянем лишь некоторые: – изменив его значение в конфигурационном файле postgresql.conf, который в нашей инсталляции PostgreSQL, описанной в главе 2, находится в каталоге /usr/local/pgsql/data; – назначив переменную системного окружения PGDATESTYLE; – воспользовавшись командой SET. Сейчас выберем третий способ, а первые два рассмотрим при выполнении дру- гих заданий. Поскольку параметр datestyle состоит фактически из двух частей, которые можно задавать не только обе сразу, но и по отдельности, изменим только порядок следования составных частей даты, не изменяя формат выво- да с «ISO» на какой-либо другой. SET datestyle TO 'MDY'; Повторим одну из команд, выполненных ранее. Теперь она должна вызвать ошибку. Почему? SELECT '18-05-2016'::date; А такая команда, наоборот, теперь будет успешно выполнена: 60 SELECT '05-18-2016'::date; Теперь приведите настройку параметра datestyle в исходное состояние: SET datestyle TO DEFAULT; Самостоятельно выполните команды SELECT, приведенные выше, но замените в них тип date на тип timestamp. Вы увидите, что дата в рамках типа timestamp обрабатывается аналогично типу date. Сейчас изменим сразу обе части параметра datestyle: SET datestyle TO 'Postgres, DMY'; Проверьте полученный результат с помощью команды SHOW. Самостоятельно выполните команды SELECT, приведенные выше, как для зна- чения типа date, так и для значения типа timestamp. Обратите внимание, что если выбран формат «Postgres», то порядок следования составных частей даты (день, месяц, год), заданный в параметре datestyle, используется не только при вводе значений, но и при выводе. Напомним, что вводом мы считаем команду SELECT, а выводом — результат ее выполнения, выведенный на экран. В документации (см. раздел 8.5.2 «Вывод даты/времени») сказано, что формат вывода даты может принимать значения «ISO», «Postgres», «SQL» и «German». Первые два варианта мы уже рассмотрели. Самостоятельно поэксперименти- руйте с двумя оставшимися по той же схеме, по которой вы уже действовали ранее при выполнении этого задания. Можно воспользоваться и стандартными функциями current_date и current_timestamp. 13. Установить новое значение параметра datestyle можно с помощью создания переменной системного окружения PGDATESTYLE. Назначить эту переменную можно в конфигурационных файлах операционной системы. Но если нам нуж- но сделать это только на время текущего сеанса работы клиентской программы, например, утилиты psql, то можно ввести значение этой переменной непосред- ственно в командной строке: PGDATE psql -d test -U имя_пользователя Проделайте эти действия, а затем уже из командной строки утилиты psql про- верьте текущее значение параметра datestyle с помощью команды SHOW. 14. Назначить значение параметра datestyle можно в конфигурационном файле postgresql.conf, который находится в каталоге /usr/local/pgsql/data. Предвари- тельно сохраните текущую (корректно работающую) версию этого файла, а затем измените в нем значение параметра datestyle, например, на «Postgres, YMD». Перезапустите сервер PostgreSQL, чтобы изменения вступили в силу. Для проверки полученного результата выполните несколько команд SELECT, напри- мер: SELECT '05-18-2016'::timestamp; SELECT current_timestamp; 61 15. В документации в разделе 9.8 «Функции форматирования данных» представ- лены описания множества полезных функций, позволяющих преобразовать в строку данные других типов, например, timestamp. Одна из таких функций — to_char(). Приведем несколько команд, иллюстрирующих использование этой функции. Ее первым параметром является форматируемое значение, а вторым — шаблон, описывающий формат, в котором это значение будет представлено при вводе или выводе. Сначала попробуйте разобраться, не обращаясь к документации, в том, что означает второй параметр этой функции в каждой из приведенных команд, а затем проверьте свои предположения по документации. SELECT to_char( current_timestamp, 'mi:ss' ); to_char --------- 47:43 (1 строка) SELECT to_char( current_timestamp, 'dd' ); to_char --------- 12 (1 строка) SELECT to_char( current_timestamp, 'yyyy-mm-dd' ); to_char ------------ 2017-03-12 (1 строка) Поэкспериментируйте с этой функцией, извлекая из значения типа timestamp различные поля и располагая их в нужном вам порядке. 16. При выполнении приведения типа данных производится проверка значения на допустимость. Попробуйте ввести недопустимое значение даты, например, 29 февраля в невисокосном году. SELECT 'Feb 29, 2015'::date; Получите сообщение об ошибке. 17. При выполнении приведения типа данных производится проверка значения на допустимость. Попробуйте ввести недопустимое значение времени, например, с нарушением формата. SELECT '21:15:16:22'::time; ОШИБКА: неверный синтаксис для типа time: "21:15:16:22" СТРОКА 1: select '21:15:16:22'::time; ^ 18. Как вы думаете, значение какого типа будет получено при вычитании одной даты из другой? Например: 62 SELECT ( '2016-09-16'::date - '2016-09-01'::date ); Сначала попробуйте получить ответ, рассуждая логически, а затем проверьте на практике в утилите psql. 19. С типами даты и времени можно выполнять различные арифметические опера- ции. Как правило, их применение является интуитивно понятным. Выполните следующую команду и проанализируйте результат. SELECT ( '20:34:35'::time - '19:44:45'::time ); А теперь попробуйте предположить, какой результат будет получен, если в этой команде знак «минус» заменить на знак «плюс»? Проверьте ваши предположе- ния с помощью утилиты psql. Подробное описание всех допустимых арифмети- ческих операций с датами и временем приведено в документации в разделе 9.9 «Операторы и функции даты/времени». 20. Значение типа interval можно получить при вычитании одной временн´ой от- метки из другой, например: SELECT ( current_timestamp - '2016-01-01'::timestamp ) AS new_date; new_date ------------------------- 278 days 00:10:33.33236 (1 строка) А что получится, если прибавить интервал к временн´ой отметке? Сначала по- пробуйте дать ответ, не прибегая к помощи утилиты psql, а затем проверьте свой ответ с помощью этой утилиты. Например, прибавим интервал длитель- ностью в 1 месяц к текущей к временн´ой отметке: SELECT ( current_timestamp + '1 mon'::interval ) AS new_date; В этой команде с помощью ключевого слова AS мы назначили псевдоним для того столбца, который будет выведен в результате. Выполните эту же команду, убрав псевдоним, и найдите отличия. 21. Можно с высокой степенью уверенности предположить, что при прибавлении интервалов к датам и временным отметкам PostgreSQL учитывает тот факт, что различные месяцы имеют различное число дней. Но как это реализуется на практике? Например, что получится при прибавлении интервала в 1 месяц к последнему дню января и к последнему дню февраля? Сначала сделайте обос- нованные предположения о результатах следующих двух команд, а затем про- верьте предположения на практике и проанализируйте полученные результаты: SELECT ( '2016-01-31'::date + '1 mon'::interval ) AS new_date; SELECT ( '2016-02-29'::date + '1 mon'::interval ) AS new_date; 22. Форматом ввода и вывода интервалов управляет параметр intervalstyle. Его можно изменить с помощью способов, аналогичных тем, что были описаны вы- ше для параметра datestyle. Самостоятельно поэкспериментируйте с различны- ми значениями параметра intervalstyle аналогично тому, как вы это делали с па- раметром datestyle. Используйте раздел 8.5 «Типы даты/времени» в документа- ции. 63 Напомним, что вернуть исходное значение этого параметра в psql можно с по- мощью команды SET intervalstyle TO DEFAULT; 23. Выполните следующие две команды и объясните различия в выведенных ре- зультатах: SELECT ( '2016-09-16'::date - '2015-09-01'::date ); SELECT ( '2016-09-16'::timestamp - '2015-09-01'::timestamp ); 24. Выполните следующие две команды и объясните различия в выведенных ре- зультатах: SELECT ( '20:34:35'::time - 1 ); SELECT ( '2016-09-16'::date - 1 ); Почему при выполнении первой команды возникает ошибка? Как можно моди- фицировать эту команду, чтобы ошибка исчезла? Для получения полной информации обратитесь к разделу «9.9. Операторы и функции даты/времени» в документации. 25. Значения временных отметок можно усекать с той или иной точностью с помо- щью функции date_trunc(). Например, с помощью следующей команды можно «отрезать» дробную часть секунды: SELECT ( date_trunc( 'sec', timestamp '1999-11-27 12:34:56.987654' ) ); date_trunc --------------------- 1999-11-27 12:34:56 (1 строка) Напомним, что в данной команде используется операция приведения типа. Выполните эту команду, последовательно указывая в качестве первого пара- метра значения microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium (которые обозначают соответственно микро- секунды, миллисекунды, секунды, минуты, часы, дни, недели, месяцы, годы, десятилетия, века и тысячелетия). Допустимы сокращения sec, min, mon, dec, cent, mil. Обратите внимание, что результирующее значение получается не пу- тем округления исходного значения, а именно путем отбрасывания более мел- ких единиц. При этом поля времени (часы, минуты и секунды) заменяются ну- лями, а поля даты (годы, месяцы и дни) — заменяются цифрами «01». Однако при использовании параметра week картина получается более интересная. 26. Функция date_trunc() может работать не только с данными типа timestamp, но также и данными типа interval. Самостоятельно ознакомьтесь с этими воз- можностями по документации (см. раздел 9.9 «Операторы и функции да- ты/времени»). 64 27. Весьма полезна функция extract(). С ее помощью можно извлечь значение от- дельного поля из временной отметки timestamp. Наименование поля задается в первом параметре. Эти наименования такие же, что и для функции date_trunc(). Выполните следующую команду SELECT extract( 'microsecond' from timestamp '1999-11-27 12:34:56.123459' ); Она выводит не просто значение поля микросекунд, т. е. 123459, а дополнитель- но преобразует число секунд в микросекунды и добавляет значение поля мик- росекунд. date_part ----------- 56123459 (1 строка) Выполните эту команду, последовательно указывая в качестве первого пара- метра значения microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium. Можно использовать сокращения этих наиме- нований, которые приведены в предыдущем задании. Обратите внимание, что в ряде случаев выводится не просто конкретное поле (фрагмент) из временно й отметки, а некоторый продукт переработки этого поля. Например, если в каче- стве первого параметра функции extract() в вышеприведенной команде указать cent (век), то мы получим в ответ не 19 (что и было бы буквальным значением поля «век»), а 20, поскольку 1999 год принадлежит двадцатому веку. 28. Функция extract() может работать не только с данными типа timestamp, но также и данными типа interval. Самостоятельно ознакомьтесь с этими возможностями по документации (см. раздел 9.9 «Операторы и функции даты/времени»). 29.* В тексте главы мы создавали таблицу с помощью команды CREATE TABLE databases ( is_open_source boolean, dbms_name text ); и заполняли ее данными. 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 WHERE NOT is_open_source; SELECT * FROM databases WHERE is_open_source <> 'yes'; SELECT * FROM databases WHERE is_open_source <> 't'; SELECT * FROM databases WHERE is_open_source <> '1'; SELECT * FROM databases WHERE is_open_source <> 1; 30.* Обратимся к таблице, создаваемой с помощью команды CREATE TABLE test_bool ( a boolean, b text ); 65 Как вы думаете, какие из приведенных ниже команд содержат ошибку? INSERT INTO test_bool VALUES ( TRUE, 'yes' ); INSERT INTO test_bool VALUES ( yes, 'yes' ); INSERT INTO test_bool VALUES ( 'yes', true ); INSERT INTO test_bool VALUES ( 'yes', TRUE ); INSERT INTO test_bool VALUES ( '1', 'true' ); INSERT INTO test_bool VALUES ( 1, 'true' ); INSERT INTO test_bool VALUES ( 't', 'true' ); INSERT INTO test_bool VALUES ( 't', truth ); INSERT INTO test_bool VALUES ( true, true ); INSERT INTO test_bool VALUES ( 1::boolean, 'true' ); INSERT INTO test_bool VALUES ( 111::boolean, 'true' ); Проверьте свои предположения практически, выполнив эти команды. 31.* Пусть в таблице birthdays хранятся даты рождения какой-то группы людей. Со- здайте эту таблицу с помощью команды CREATE TABLE birthdays ( person text NOT NULL, birthday date NOT NULL ); Добавьте в нее несколько строк, например: INSERT INTO birthdays VALUES ( 'Ken Thompson', '1955-03-23' ); INSERT INTO birthdays VALUES ( 'Ben Johnson', '1971-03-19' ); INSERT INTO birthdays VALUES ( 'Andy Gibson', '1987-08-12' ); Давайте выберем из таблицы birthdays строки для всех людей, родившихся в каком-то конкретном месяце, например, в марте: SELECT * FROM birthdays WHERE extract( 'mon' from birthday ) = 3; В этой команде в вызове функции extract имеет место неявное приведение ти- пов, т. к. ее вторым параметром должно быть значение типа timestamp. Пола- гаться на неявное приведение типов можно не всегда. person | birthday --------------+------------ Ken Thompson | 1955-03-23 Ben Johnson | 1971-03-19 (2 строки) Если нам потребуется выяснить, кто из этих людей достиг возраста, скажем, 40 лет на момент выполнения запроса, то команда может быть такой (в последнем столбце показана дата достижения возраста 40 лет): SELECT *, birthday + '40 years'::interval FROM birthdays WHERE birthday + '40 years'::interval < current_timestamp; 66 person | birthday | ?column? --------------+------------+--------------------- Ken Thompson | 1955-03-23 | 1995-03-23 00:00:00 Ben Johnson | 1971-03-19 | 2011-03-19 00:00:00 (2 строки) Можно заменить current_timestamp на current_date: SELECT *, birthday + '40 years'::interval FROM birthdays WHERE birthday + '40 years'::interval < current_date; А вот если мы захотим определить точный возраст каждого человека на теку- щий момент времени, то как получить этот результат? Первый вариант таков: SELECT *, ( current_date::timestamp - birthday::timestamp )::interval FROM birthdays; person | birthday | interval --------------+------------+------------ Ken Thompson | 1955-03-23 | 22477 days Ben Johnson | 1971-03-19 | 16637 days Andy Gibson | 1987-08-12 | 10647 days (3 строки) Этот вариант не дает результата, представленного в удобной форме: он показы- вает возраст в днях, а для пересчета числа дней в число лет нужны дополнитель- ные действия. Хотя, наверное, возможны ситуации, когда требуется определить возраст именно в днях. В PostgreSQL предусмотрена специальная функция, позволяющая решить нашу задачу простым способом. Самостоятельно найдите ее описание в документа- ции (см. раздел 9.9 «Операторы и функции даты/времени») и напишите команду с ее использованием. 32. Изучая приемы работы с массивами, можно, как и в других случаях, пользовать- ся способностью команды SELECT обходиться без создания таблиц. Покажем лишь два примера. Для объединения (конкатенации) массивов служит функция array_cat: SELECT array_cat( ARRAY[ 1, 2, 3 ], ARRAY[ 3, 5 ] ); array_cat ------------- {1,2,3,3,5} (1 строка) Удалить из массива элементы, имеющие указанное значение, можно таким об- разом: SELECT array_remove( ARRAY[ 1, 2, 3 ], 3 ); array_remove -------------- {1,2} (1 строка) 67 Для работы с массивами предусмотрено много различных функций и операто- ров, представленных в разделе документации 9.18 «Функции и операторы для работы с массивами». Самостоятельно ознакомьтесь с ними, используя описан- ную технологию работы с командой SELECT. 33.* В разделе документации 8.15 «Массивы» сказано, что массивы могут быть мно- гомерными и в них могут содержаться значения любых типов. Давайте сначала рассмотрим одномерные массивы текстовых значений. Предположим, что пилоты авиакомпании имеют возможность высказывать свои пожелания насчет конкретных блюд, из которых должен состоять их обед во время полета. Для учета пожеланий пилотов необходимо модифицировать таблицу pilots, с которой мы работали в разделе 4.5. CREATE TABLE pilots ( pilot_name text, schedule integer[], meal text[] ); Добавим строки в таблицу: INSERT INTO pilots VALUES ( 'Ivan', '{ 1, 3, 5, 6, 7 }'::integer[], '{ "сосиска", "макароны", "кофе" }'::text[] ), ( 'Petr', '{ 1, 2, 5, 7 }'::integer [], '{ "котлета", "каша", "кофе" }'::text[] ), ( 'Pavel', '{ 2, 5 }'::integer[], '{ "сосиска", "каша", "кофе" }'::text[] ), ( 'Boris', '{ 3, 5, 6 }'::integer[], '{ "котлета", "каша", "чай" }'::text[] ); INSERT 0 4 Обратите внимание, что каждое из текстовых значений, включаемых в литерал массива, заключается в двойные кавычки, а в качестве типа данных указывается text[]. Вот что получилось: |