Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
|
SELECT * FROM pilots WHERE NOT ( schedule && ARRAY[ 2, 5 ] ); pilot_name | schedule ------------+----------- Ivan | {1,3,6,7} (1 строка) 50 Иногда требуется развернуть массив в виде столбца таблицы. В таком случае поможет функция unnest: SELECT unnest( schedule ) AS days_of_week FROM pilots WHERE pilot_name = 'Ivan'; days_of_week -------------- 1 3 6 7 (4 строки) Подробно использование массивов рассмотрено в документации в разделах 8.15 «Массивы» и 9.18 «Функции и операторы для работы с массивами». 4.6 Типы JSON Типы JSON предназначены для сохранения в столбцах таблиц базы данных таких зна- чений, которые представлены в формате JSON (JavaScript Object Notation). Существу- ет два типа: json и jsonb. Основное различие между ними заключается в быстродей- ствии. Если столбец имеет тип json, тогда сохранение значений происходит быстрее, потому что они записываются в том виде, в котором были введены. Но при после- дующем использовании этих значений в качестве операндов или параметров функ- ций будет каждый раз выполняться их разбор, что замедляет работу. При использо- вании типа jsonb разбор производится однократно, при записи значения в таблицу. Это несколько замедляет операции вставки строк, в которых содержатся значения данного типа. Но все последующие обращения к сохраненным значениям выполня- ются быстрее, т. к. выполнять их разбор уже не требуется. Есть еще ряд отличий, в частности, тип json сохраняет порядок следования ключей в объектах и повторяющиеся значения ключей, а тип jsonb этого не делает. Рекомен- дуется в приложениях использовать тип jsonb, если только нет каких-то особых аргу- ментов в пользу выбора типа json. Для иллюстрации использования типов JSON обратимся к тематике авиаперевозок. Предположим, что руководство авиакомпании всемерно поддерживает стремление пилотов улучшать свое здоровье, повышать уровень культуры и расширять круго- зор. Поэтому разработчики базы данных авиакомпании получили задание создать специальную таблицу, в которую будут заноситься сведения о тех видах спорта, ко- торыми занимается пилот, будет отмечаться наличие у него домашней библиотеки, а также фиксироваться количество стран, которые он посетил в ходе туристических поездок. CREATE TABLE pilot_hobbies ( pilot_name text, hobbies jsonb ); CREATE TABLE 51 INSERT INTO pilot_hobbies VALUES ( 'Ivan', '{ "sports": [ "футбол", "плавание" ], "home_lib": true, "trips": 3 }'::jsonb ), ( 'Petr', '{ "sports": [ "теннис", "плавание" ], "home_lib": true, "trips": 2 }'::jsonb ), ( 'Pavel', '{ "sports": [ "плавание" ], "home_lib": false, "trips": 4 }'::jsonb ), ( 'Boris', '{ "sports": [ "футбол", "плавание", "теннис" ], "home_lib": true, "trips": 0 }'::jsonb ); INSERT 0 4 SELECT * FROM pilot_hobbies; pilot_name | i hobbies ------------+-------------------------------------------------------- Ivan | {"trips": 3, "sports": ["футбол", "плавание"], "home_lib": true} Petr | {"trips": 2, "sports": ["теннис", "плавание"], "home_lib": true} Pavel | {"trips": 4, "sports": ["плавание"], "home_lib": false} Boris | {"trips": 0, "sports": ["футбол", "плавание", "теннис"], "home_lib": true} (4 строки) Как видно, при выводе строк из таблицы порядок ключей в JSON-объектах не был сохранен. Предположим, что нужно сформировать футбольную сборную команду нашей авиа- компании для участия в турнире. Мы можем выбрать всех футболистов таким спосо- бом: SELECT * FROM pilot_hobbies WHERE hobbies @> '{ "sports": [ "футбол" ] }'::jsonb; pilot_name | i hobbies ------------+-------------------------------------------------------- Ivan | {"trips": 3, "sports": ["футбол", "плавание"], "home_lib": true} Boris | {"trips": 0, "sports": ["футбол", "плавание", "теннис"], "home_lib": true} (2 строки) Можно было эту задачу решить и таким способом: SELECT pilot_name, hobbies->'sports' AS sports FROM pilot_hobbies WHERE hobbies->'sports' @> '[ "футбол" ]'::jsonb; 52 pilot_name | sports ------------+---------------------------------- Ivan | ["футбол", "плавание"] Boris | ["футбол", "плавание", "теннис"] (2 строки) В этом решении мы выводим только информацию о спортивных предпочтениях пи- лотов. Внимательно посмотрите, как используются одинарные и двойные кавычки. Операция «−>» служит для обращения к конкретному ключу JSON-объекта. При создании столбца с типом данных json или jsonb не требуется задавать структу- ру объектов, т. е. конкретные имена ключей. Поэтому в принципе возможна ситуа- ция, когда в разных строках в JSON-объектах будут использоваться различные набо- ры ключей. В нашем примере структуры JSON-объектов во всех строках совпадают. А если бы они не совпадали, то как можно было бы проверить наличие ключа? Проде- монстрируем это. Ключа «sport» в наших объектах нет. Что покажет вызов функции count? SELECT count( * ) FROM pilot_hobbies WHERE hobbies ? 'sport'; count ------- 0 (1 строка) А вот ключ «sports» присутствует. Выполним ту же проверку: SELECT count( * ) FROM pilot_hobbies WHERE hobbies ? 'sports'; Да, так и есть. Такие записи найдены. count ------- 4 (1 строка) А как выполнять обновление JSON-объектов в строках таблицы? Предположим, что пилот по имени Boris решил посвятить себя только хоккею. Тогда в базе данных мы выполним такую операцию: UPDATE pilot_hobbies SET hobbies = hobbies || '{ "sports": [ "хоккей" ] }' WHERE pilot_name = 'Boris'; UPDATE 1 Проверим, что получилось: SELECT pilot_name, hobbies FROM pilot_hobbies WHERE pilot_name = 'Boris'; 53 pilot_name | hobbies ------------+------------------------------------------------------ Boris | {"trips": 0, "sports": ["хоккей"], "home_lib": true} (1 строка) Если впоследствии Boris захочет возобновить занятия футболом, то с помощью функ- ции jsonb_set можно будет обновить сведения о нем в таблице: UPDATE pilot_hobbies SET hobbies = jsonb_set( hobbies, '{ sports, 1 }', '"футбол"' ) WHERE pilot_name = 'Boris'; UPDATE 1 Второй параметр функции указывает путь в пределах JSON-объекта, куда нужно до- бавить новое значение. В данном случае этот путь состоит из имени ключа (sports) и номера добавляемого элемента в массиве видов спорта (номер 1). Нумерация эле- ментов начинается с нуля. Третий параметр имеет тип jsonb, поэтому его литерал заключается в одинарные кавычки, а само добавляемое значение берется в двойные кавычки. В результате получается — '"футбол"'. Проверим успешность выполнения этой операции: SELECT pilot_name, hobbies FROM pilot_hobbies WHERE pilot_name = 'Boris'; pilot_name | i hobbies ------------+-------------------------------------------------------- Boris | {"trips": 0, "sports": ["хоккей", "футбол"], "home_lib": true} (1 строка) Подробно использование типов JSON рассмотрено в документации в разделах 8.14 «Типы JSON» и 9.15 «Функции и операторы JSON». Контрольные вопросы и задания 1. Создайте таблицу, содержащую атрибут типа numeric(precision, scale). Пусть это будет таблица, содержащая результаты каких-то измерений. Команда может быть, например, такой: CREATE TABLE test_numeric ( measurement numeric(5, 2), description text ); Попробуйте с помощью команды INSERT продемонстрировать округление вво- димого числа до той точности, которая задана при создании таблицы. Подумайте, какая из следующих команд вызовет ошибку и почему? Проверьте свои предположения, выполнив эти команды. 54 INSERT INTO test_numeric VALUES ( 999.9999, 'Какое-то измерение ' ); INSERT INTO test_numeric VALUES ( 999.9009, 'Еще одно измерение' ) INSERT INTO test_numeric VALUES ( 999.1111, 'И еще измерение' ); INSERT INTO test_numeric VALUES ( 998.9999, 'И еще одно' ); Продемонстрируйте генерирование ошибки при попытке ввода числа, количе- ство цифр в котором слева от запятой (десятичной точки) превышает допусти- мое. 2. Предположим, что возникла необходимость хранить в одном столбце таблицы данные, представленные с различной точностью. Это могут быть, например, ре- зультаты физических измерений разнородных показателей или различные ме- дицинские показатели здоровья пациентов (результаты анализов). В таком слу- чае можно использовать тип numeric без указания масштаба и точности. Коман- да для создания таблицы может быть, например, такой: CREATE TABLE test_numeric ( measurement numeric, description text ); Если у вас в базе данных уже есть таблица с таким же именем, то можно пред- варительно ее удалить с помощью команды DROP TABLE test_numeric; Вставьте в таблицу несколько строк: INSERT INTO test_numeric VALUES ( 1234567890.0987654321, 'Масштаб 20 знаков, точность 10 знаков' ); INSERT INTO test_numeric VALUES ( 1.5, 'Масштаб 2 знака, точность 1 знак' ); INSERT INTO test_numeric VALUES ( 0.12345678901234567890, 'Масштаб 21 знак, точность 20 знаков' ); INSERT INTO test_numeric VALUES ( 1234567890, 'Масштаб 10 знаков, точность 0 знаков (целое число)' ); Теперь сделайте выборку из таблицы и посмотрите, что все эти разнообразные значения сохранены именно в том виде, как вы их вводили. 3. Тип данных numeric поддерживает специальное значение NaN, которое означа- ет «не число» (not a number). В документации утверждается, что значение NaN считается равным другому значению NaN, а также что значение NaN считается б´ольшим любого другого «нормального» значения, т. е. не-NaN. Проверьте эти утверждения с помощью SQL команды SELECT. В качестве примера приведем команду: 55 SELECT 'NaN'::numeric > 10000; ?column? ---------- t (1 строка) 4. При работе с числами типов real и double precision нужно помнить, что сравне- ние двух чисел с плавающей точкой на предмет равенства их значений может привести к неожиданным результатам. Например, сравним два очень малень- ких числа (они представлены в экспоненциальной форме записи): SELECT '5e-324'::double precision > '4e-324'::double precision; ?column? ---------- f (1 строка) Чтобы понять, почему так получается, выполните еще два запроса. SELECT '5e-324'::double precision; float8 ----------------------- 4.94065645841247e-324 (1 строка) SELECT '4e-324'::double precision; float8 ----------------------- 4.94065645841247e-324 (1 строка) Самостоятельно проведите аналогичные эксперименты с очень большими чис- лами, находящимися на границе допустимого диапазона для чисел типов real и double precision. 5. Типы данных real и double precision поддерживают специальные значения Infinity (бесконечность) и -Infinity (отрицательная бесконечность). Проверьте с помощью SQL-команды SELECT ожидаемые свойства этих значений. Например, сравните Infinity с наибольшим значением, которое допускается для типа double precision (можно использовать сокращенное написание Inf): SELECT 'Inf'::double precision > 1E+308; ?column? ---------- t (1 строка) Выполните аналогичный запрос для наименьшего возможного значения типа double precision. 56 6. Типы данных real и double precision поддерживают специальное значение NaN, которое означает «не число» (not a number). В математике существует такое понятие, как неопределенность. В качестве од- ного из ее вариантов служит результат операции умножения нуля на бесконеч- ность. Посмотрите, что выдаст в результате PostgreSQL: SELECT 0.0 * 'Inf'::real; ?column? ---------- NaN (1 строка) В документации утверждается, что значение NaN считается равным другому значению NaN, а также что значение NaN считается б´ольшим любого другого «нормального» значения, т. е. не-NaN. Проверьте эти утверждения с помощью SQL-команды SELECT. Например, сравните значения NaN и Infinity. select 'NaN'::real > 'Inf'::real; ?column? ---------- t (1 строка) 7. Тип serial может применяться для столбцов, содержащих числовые значения, которые должны быть уникальными в пределах таблицы, например, иденти- фикаторы каких-то объектов. В качестве иллюстрация применения типа serial предложим таблицу, содержащую наименования улиц и площадей: CREATE TABLE test_serial ( id serial, name text ); Введите несколько строк. Обратите внимание, что значение для столбца id ука- зывать не обязательно (и даже не нужно). Но поскольку мы задаем значения не для всех столбцов, имеющихся в таблице, мы должны указать в команде INSERT не только список значений, но и список столбцов. Конечно, в данном простом случае эти списки состоят лишь из одного элемента. INSERT INTO test_serial ( name ) VALUES ( 'Вишневая' ); INSERT INTO test_serial ( name ) VALUES ( 'Грушевая' ); INSERT INTO test_serial ( name ) VALUES ( 'Зеленая' ); Сделайте выборку данных из таблицы, вы увидите, что значения столбца id име- ют последовательные значения, начиная с 1. Давайте проведем эксперимент со столбцом id. Выполните команду INSERT, в которой укажите явное значение столбца id: INSERT INTO test_serial ( id, name ) VALUES ( 10, 'Прохладная' ); А теперь добавьте еще одну строку, но уже не указывая явно значение для столб- ца id (как мы поступали в предыдущих командах): INSERT INTO test_serial ( name ) VALUES ( 'Луговая' ); 57 Вы увидите, что явное задание значения для столбца id не влияет на автомати- ческое генерирование значений этого столбца. 8. Немного усложним определение таблицы из предыдущего задания. Пусть те- перь столбец id будет первичным ключом этой таблицы. CREATE TABLE test_serial ( id serial PRIMARY KEY, name text ); Теперь выполните следующие команды для добавления строк в таблицу и уда- ления одной строки из нее. Для пошагового управления этим процессом выпол- няйте выборку данных из таблицы с помощью команды SELECT после каждой команды вставки или удаления. INSERT INTO test_serial ( name ) VALUES ( 'Вишневая' ); Явно зададим значение столбца id: INSERT INTO test_serial ( id, name ) VALUES ( 2, 'Прохладная' ); При выполнении этой команды СУБД выдаст сообщение об ошибке. Почему? INSERT INTO test_serial ( name ) VALUES ( 'Грушевая' ); Повторим эту же команду. Теперь все в порядке. Почему? INSERT INTO test_serial ( name ) VALUES ( 'Грушевая' ); Добавим еще одну строку. INSERT INTO test_serial ( name ) VALUES ( 'Зеленая' ); А теперь удалим ее же. DELETE FROM test_serial WHERE id = 4; Добавим последнюю строку. INSERT INTO test_serial ( name ) VALUES ( 'Луговая' ); Теперь сделаем выборку. SELECT * FROM test_serial; Вы увидите, что в нумерации образовалась «дыра». Это из-за того, что при фор- мировании нового значения из последовательности поиск максимального зна- чения, уже имеющегося в столбце, не выполняется. id | name ----+------------ 1 | Вишневая 2 | Прохладная 3 | Грушевая 5 | Луговая (4 строки) 9. Какой календарь используется в PostgreSQL для работы с датами: юлианский или григорианский? 58 10. Каждый тип данных из группы «дата/время» имеет ограничение на минималь- ное и максимальное допустимое значение. Найдите в документации в разделе 8.5 «Типы даты/времени» эти значения и подумайте, почему они таковы. 11. Типы timestamp, time и interval позволяют задать точность ввода и вывода зна- чений. Точность предписывает количество десятичных цифр в поле секунд. Проиллюстрируем эту возможность на примере типа time, выполнив три запро- са: в первом запросе вообще не используем параметр точности, во втором на- значим его равным 0, в третьем запросе сделаем его равным 3: SELECT current_time; timetz -------------------- 19:46:14.584641+03 (1 строка) SELECT current_time::time( 0 ); time ---------- 19:39:45 (1 строка) SELECT current_time::time( 3 ); time -------------- 19:39:54.085 (1 строка) Выполните подобные команды для типов timestamp и interval. Тип date такой возможности — задавать точность — не имеет. Как вы думаете, почему? 12.* Формат ввода и вывода даты можно изменить с помощью параметра datestyle. Значение этого параметра состоит из двух компонентов: первый управляет форматом вывода даты, а второй регулирует порядок следования составных ча- стей даты (год, месяц, день) при вводе и выводе. Текущее значение этого пара- метра можно узнать с помощью команды SHOW: SHOW datestyle; По умолчанию он имеет такое значение: DateStyle ----------- ISO, DMY (1 строка) Продемонстрируем влияние этого параметра на работу с типами date и timestamp. Для экспериментов возьмем дату, в которой число (день) превыша- ет 12, чтобы нельзя было день перепутать с номером месяца. Пусть это будет, например, 18 мая 2016 г. SELECT '18-05-2016'::date; 59 Хотя порядок следования составных частей даты задан в виде «DMY», т. е. «день, месяц, год», но при выводе он изменяется на «год, месяц, день». date ------------ 2016-05-18 (1 строка) Попробуем ввести дату в порядке «месяц, день, год»: SELECT '05-18-2016'::date; В ответ получим сообщение об ошибке. Если бы мы выбрали дату, в которой число (день) было бы не больше 12, например, 9, то сообщение об ошибке не было бы сформировано, т. е. мы с такой датой не смогли бы проиллюстрировать влияние значения «DMY» параметра datestyle. Но главное, что в таком случае мы бы просто не заметили допущенной ошибки. А вот использовать порядок «год, месяц, день» при вводе можно несмотря на то, что параметр datestyle предписывает «день, месяц, год». Порядок «год, месяц, день» является универсальным, его можно использовать всегда, независимо от настроек параметра datestyle. |