Учебнопрактическое пособие москва 2017 удк 004. 655 Ббк 32. 973. 26018. 2 М79 Моргунов, Е. П
 Скачать 0.9 Mb.
|
FROM airports WHERE city <> 'Москва' ? SELECT arrival_city FROM routes WHERE departure_city = 'Москва' ORDER BY city; 22. В тексте главы мы рассматривали такой запрос: получить перечень аэропортов в тех городах, в которых больше одного аэропорта. SELECT aa.city, aa.airport_code, aa.airport_name FROM ( SELECT city, count( * ) FROM airports GROUP BY city HAVING count( * ) > 1 ) AS a JOIN airports AS aa ON a.city = aa.city ORDER BY aa.city, aa.airport_name; Как вы думаете, обязательно ли наличие функции count в подзапросе в предло- жении SELECT или можно написать просто ... FROM ( SELECT city FROM airports ... Сначала попробуйте дать ответ теоретически, а потом проверьте вашу гипотезу на компьютере. 158 23. Предположим, что департамент развития нашей авиакомпании задался вопро- сом: каким будет общее число различных маршрутов, которые теоретически можно проложить между всеми городами? Если в каком-то городе имеется более одного аэропорта, то это учитывать не будем, т. е. маршрутом будем считать путь между городами, а не между аэро- портами . Здесь мы используем соединение таблицы с самой собой на основе неравенства значений атрибутов. SELECT count( * ) FROM ( SELECT DISTINCT city FROM airports ) AS a1 JOIN ( SELECT DISTINCT city FROM airports ) AS a2 ON a1.city <> a2.city; count ------- 10100 (1 строка) Задание. Перепишите этот запрос с использованием общего табличного выра- жения. 24. В тексте главы мы рассмотрели использование подзапросов в предикатах EXISTS и IN. Существуют также предикаты многократного сравнения ANY и ALL. Они представлены в документации в разделе 9.22 «Выражения подзапросов». Самостоятельно ознакомьтесь с этими предикатами и напишите несколько за- просов с их применением. Предикаты ANY и ALL имеют некоторую связь с предикатом IN. В частности, использование IN эквивалентно использованию конструкции = ANY, а исполь- зование NOT IN эквивалентно использованию конструкции <> ALL. Пример двух эквивалентных запросов, выбирающих аэропорты в часовых поя- сах «Asia/Novokuznetsk» и «Asia/Krasnoyarsk»: SELECT * FROM airports WHERE timezone IN ( 'Asia/Novokuznetsk', 'Asia/Krasnoyarsk' ); SELECT * FROM airports WHERE timezone = ANY ( VALUES ( 'Asia/Novokuznetsk' ), ( 'Asia/Krasnoyarsk' ) ); Еще один пример. В тексте главы мы рассматривали запрос, подсчитывающий количество маршрутов, проложенных из самых восточных аэропортов. SELECT departure_city, count( * ) FROM routes GROUP BY departure_city HAVING departure_city IN ( SELECT city FROM airports WHERE longitude > 150 159 ) ORDER BY count DESC; В этом запросе можно заменить IN на ANY таким образом: ... HAVING departure_city = ANY ( SELECT city ... 25.* При планировании новых маршрутов и оценке экономической эффективности уже существующих может потребоваться информация о том, какова усреднен- ная степень заполнения самолетов на всех направлениях. Будем учитывать только уже прибывшие рейсы. WITH tickets_seats AS ( SELECT f.flight_id, f.flight_no, f.departure_city, f.arrival_city, f.aircraft_code, count( tf.ticket_no ) AS fact_passengers, ( SELECT count( s.seat_no ) FROM seats s WHERE s.aircraft_code = f.aircraft_code ) AS total_seats FROM flights_v f JOIN ticket_flights tf ON f.flight_id = tf.flight_id WHERE f.status = 'Arrived' GROUP BY 1, 2, 3, 4, 5 ) SELECT ts.departure_city, ts.arrival_city, sum( ts.fact_passengers ) AS sum_fact_passengers, sum( ts.total_seats ) AS sum_total_seats, round( sum( ts.fact_passengers )::numeric / sum( ts.total_seats )::numeric, 2 ) AS fraction FROM tickets_seats ts GROUP BY ts.departure_city, ts.arrival_city ORDER BY ts.departure_city; --[ RECORD 1 ]-------+------------------------- departure_city | Абакан arrival_city | Москва sum_fact_passengers | 466 sum_total_seats | 1044 fraction | 0.45 --[ RECORD 2 ]-------+------------------------- departure_city | Абакан arrival_city | Новосибирск sum_fact_passengers | 217 sum_total_seats | 348 fraction | 0.62 --[ RECORD 3 ]-------+------------------------- departure_city | Абакан arrival_city | Томск sum_fact_passengers | 258 sum_total_seats | 360 fraction | 0.72 160 --[ RECORD 361 ]-----+------------------------- departure_city | Якутск arrival_city | Санкт-Петербург sum_fact_passengers | 352 sum_total_seats | 3596 fraction | 0.10 Для того чтобы лучше уяснить, как работает запрос в целом, вычлените из него отдельные подзапросы и выполните их, посмотрите, что они выводят. Как вы считаете, равносильно ли в данном запросе SELECT count( s.seat_no ) и SELECT count( s.* ) Почему? Задание. Модифицируйте этот запрос, чтобы он выводил те же отчетные дан- ные, но с учетом классов обслуживания, т. е. Business, Comfort и Economy. 26.* Предположим, что некая контролирующая организация потребовала информа- цию о размещении пассажиров одного из рейсов Кемерово — Москва в салоне самолета. Для определенности выберем конкретный рейс из тех рейсов, кото- рые уже прибыли на момент времени, соответствующий текущему моменту. Те- кущий момент времени в базе данных «Авиаперевозки» определяется с помо- щью функции bookings.now. Выполним запрос SELECT * FROM flights_v WHERE departure_city = 'Кемерово' AND arrival_city = 'Москва' AND actual_arrival < bookings.now(); Выберем для дальнейшей работы рейс, у которого значения атрибутов flight_id — 27584, aircraft_code — SU9. Получим список пассажиров этого рейса с местами, которые им были назначе- ны в салоне самолета. SELECT t.passenger_name, b.seat_no FROM ( ticket_flights tf JOIN tickets t ON tf.ticket_no = t.ticket_no ) JOIN boarding_passes b ON tf.ticket_no = b.ticket_no AND tf.flight_id = b.flight_id WHERE tf.flight_id = 27584 ORDER BY t.passenger_name; 161 passenger_name | seat_no ---------------------+--------- ALEKSANDR ABRAMOV | 1A ALEKSANDR GRIGOREV | 5C ALEKSANDR SERGEEV | 6F ALEKSEY FEDOROV | 11D ALEKSEY MELNIKOV | 18A VLADIMIR POPOV | 11A YAROSLAV KUZMIN | 18F YURIY ZAKHAROV | 10F (44 строки) Отсортируем строки по фамилиям пассажиров: SELECT t.passenger_name, substr( t.passenger_name, strpos( t.passenger_name, ' ' ) + 1 ) AS lastname, left( t.passenger_name, strpos( t.passenger_name, ' ' ) - 1 ) AS firstname, b.seat_no FROM ( ticket_flights tf JOIN tickets t ON tf.ticket_no = t.ticket_no ) JOIN boarding_passes b ON tf.ticket_no = b.ticket_no AND tf.flight_id = b.flight_id WHERE tf.flight_id = 27584 ORDER BY 2, 3; passenger_name | lastname | firstname | seat_no ---------------------+-----------+-----------+--------- ALEKSANDR ABRAMOV | ABRAMOV | ALEKSANDR | 1A NIKITA ANDREEV | ANDREEV | NIKITA | 6D ANTONINA ANISIMOVA | ANISIMOVA | ANTONINA | 11F YURIY ZAKHAROV | ZAKHAROV | YURIY | 10F ELENA ZOTOVA | ZOTOVA | ELENA | 20E (44 строки) Получим список мест в салоне самолета и пассажиров, которые сидели на этих местах. При этом незанятые места также должны быть выведены (поэтому ис- пользуем левое внешнее соединение ... FROM seats s LEFT OUTER JOIN ...). SELECT s.seat_no, p.passenger_name FROM seats s LEFT OUTER JOIN ( SELECT t.passenger_name, b.seat_no FROM ( ticket_flights tf JOIN tickets t ON tf.ticket_no = t.ticket_no ) 162 JOIN boarding_passes b ON tf.ticket_no = b.ticket_no AND tf.flight_id = b.flight_id WHERE tf.flight_id = 27584 ) AS p ON s.seat_no = p.seat_no WHERE s.aircraft_code = 'SU9' ORDER BY s.seat_no; seat_no | passenger_name ---------+--------------------- 10A | 10C | 10D | NATALYA POPOVA 10E | 10F | YURIY ZAKHAROV 11A | VLADIMIR POPOV 11C | ANNA KUZMINA 11D | ALEKSEY FEDOROV 11E | 11F | ANTONINA ANISIMOVA 8F | 9A | MAKSIM CHERNOV 9C | 9D | LYUDMILA IVANOVA 9E | 9F | SOFIYA KULIKOVA (97 строк) Предположим, что нас попросили отсортировать места в порядке их расположе- ния в салоне самолета и вывести также адреса электронной почты пассажиров (у кого они были указаны при бронировании). Для выполнения второго требо- вания воспользуемся столбцом contact_data. В нем содержатся JSON-объекты, содержащие контактные данные пассажиров. Ряд из них имеет ключ «email». Модифицированный запрос будет таким: SELECT s.seat_no, p.passenger_name, p.email FROM seats s LEFT OUTER JOIN ( SELECT t.passenger_name, b.seat_no, t.contact_data->'email' AS email FROM ( ticket_flights tf JOIN tickets t ON tf.ticket_no = t.ticket_no ) JOIN boarding_passes b ON tf.ticket_no = b.ticket_no AND tf.flight_id = b.flight_id WHERE tf.flight_id = 27584 ) AS p ON s.seat_no = p.seat_no WHERE s.aircraft_code = 'SU9' ORDER BY 163 left( s.seat_no, length( s.seat_no ) - 1 )::integer, right( s.seat_no, 1 ); seat_no | passenger_name | email ---------+-------------------+------------------------------------ 1A | ALEKSANDR ABRAMOV | 1C | | 1D | DENIS PETROV | 1F | LEONID BARANOV | "baranov.l.1967@postgrespro.ru" 2A | | 2C | | 2D | | 2F | FEDOR TIKHONOV | "tikhonov_fedor_1957@postgres... " 9F | SOFIYA KULIKOVA | "sofiya.kulikova_041963@postgre..." 10A | | 10C | | 10D | NATALYA POPOVA | "popova.n_13031976@postgrespro.ru" 20E | ELENA ZOTOVA | 20F | LILIYA OSIPOVA | (97 строк) Задание. Перепишите последний запрос с использованием общего табличного выражения и добавьте столбец «Класс обслуживания» (fare_conditions). 164 7 Изменение данных Эта глава будет посвящена операциям изменения данных: вставке новых строк в таблицы, об- новлению уже существующих строк и их удалению. С простыми приемами использования ко- манд INSERT, UPDATE и DELETE, предназначенных для выполнения указанных операций, вы уже познакомились, поэтому мы расскажем о некоторых более интересных способах приме- нения этих команд. 7.1 Вставка строк в таблицы Для работы нам потребуется создать еще две таблицы в базе данных «Авиаперевоз- ки» (demo). Мы будем создавать их как временные таблицы, которые будут удаляться при отключении от базы данных. Использование временных таблиц позволит нам проводить эксперименты, будучи уверенными в том, что данные в постоянных таб- лицах модифицированы не будут, поэтому все запросы, которые вы выполняли ра- нее, будут работать так, как и работали. Итак, создадим две копии таблицы «Самолеты» (aircrafts). Первая таблица-копия бу- дет предназначена для хранения данных, взятых из таблицы-прототипа, а вторая таблица-копия будет использоваться в качестве журнальной таблицы: будем запи- сывать в нее все операции, проведенные с первой таблицей. Создадим первую таблицу, причем, копировать данные из постоянной таблицы aircrafts не будем, о чем говорит предложение WITH NO DATA. Если бы мы решили скопировать в новую таблицу и все строки, содержащиеся в таблице-прототипе, то- гда в команде CREATE TABLE мы могли бы использовать предложение WITH DATA или вообще не указывать его: по умолчанию строки копируются в создаваемую таб- лицу. CREATE TEMP TABLE aircrafts_tmp AS SELECT * FROM aircrafts WITH NO DATA; Наложим на таблицу необходимые ограничения: они не создаются при копировании таблицы. При массовом вводе данных гораздо более эффективным с точки зрения производительности было бы сначала добавить строки в таблицу, а уже потом накла- дывать ограничения на нее. Однако в нашем случае речь о массовом вводе не идет, поэтому мы начнем с наложения ограничений, а уже потом добавим строки с табли- цу. ALTER TABLE aircrafts_tmp ADD PRIMARY KEY ( aircraft_code ); ALTER TABLE aircrafts_tmp ADD UNIQUE ( model ); Теперь создадим вторую таблицу, копировать данные из постоянной таблицы aircrafts в нее также не будем. CREATE TEMP TABLE aircrafts_log AS SELECT * FROM aircrafts WITH NO DATA; 165 Ограничения в виде первичного и уникального ключей этой таблице не требуются, но потребуются еще два столбца: первый будет содержать дату/время выполнения опе- рации над таблицей aircrafts_tmp, а второй — наименование этой операции (INSERT, UPDATE или DELETE). ALTER TABLE aircrafts_log ADD COLUMN when_add timestamp; ALTER TABLE aircrafts_log ADD COLUMN operation text; Поскольку в рассматриваемой ситуации копировать данные из постоянных таблиц во временные не требуется, то в качестве альтернативного способа создания временных таблиц можно было бы воспользоваться командой CREATE TEMP TABLE с предложе- нием LIKE. Например: CREATE TEMP TABLE aircrafts_tmp ( LIKE aircrafts INCLUDING CONSTRAINTS INCLUDING INDEXES ); Но так как уникального индекса по столбцу model в таблице aircrafts нет, то для вре- менной таблицы его пришлось бы сформировать с помощью команды ALTER TABLE, как и при использовании первого способа ее создания. Добавим, что предложение LIKE можно применять для создания не только временных таблиц, но и постоян- ных. Поскольку у нас есть журнальная таблица aircrafts_log, мы можем все операции с таб- лицей aircrafts_tmp записывать в таблицу aircrafts_log, т. е. вести историю изменений данных в таблице aircrafts_tmp. Начнем работу с того, что скопируем в таблицу aircrafts_tmp все данные из таблицы aircrafts. Для выполнения не только «полезной» работы, но и ведения журнала изме- нений воспользуемся командой INSERT с общим табличным выражением. Вообще, при классическом подходе для ведения учета изменений, внесенных в таб- лицы, используют триггеры или правила (rules), но их рассмотрение выходит за рам- ки настоящего пособия. Поэтому наш пример нужно рассматривать как иллюстрацию возможностей общих табличных выражений (CTE), а не как единственно верный под- ход. WITH add_row AS ( INSERT INTO aircrafts_tmp SELECT * FROM aircrafts RETURNING * ) INSERT INTO aircrafts_log SELECT add_row.aircraft_code, add_row.model, add_row.range, CURRENT_TIMESTAMP, 'INSERT' FROM add_row; INSERT 0 9 Давайте рассмотрим эту команду более подробно. Обратите внимание, что вся «по- лезная» работа выполняется в рамках конструкции WITH add_row AS (...). Здесь строки 166 с помощью команды SELECT выбираются из таблицы aircrafts и вставляются в табли- цу aircrafts_tmp. При вставке строк, выбранных из одной таблицы, в другую табли- цу необходимо, чтобы число атрибутов и их типы данных во вставляемых строках были согласованы с числом столбцов и их типами данных в целевой таблице. За- вершается конструкция WITH add_row AS (...) предложением RETURNING *, которое просто возвращает внешнему запросу все строки, успешно добавленные в таблицу aircrafts_tmp. Конечно же, при этом из таблицы aircrafts_tmp добавленные строки ни- куда не исчезают. Запрос получает имя add_row, на которое может ссылаться внешний запрос, когда он «хочет» обратиться к строкам, возвращенным с помощью предложе- ния RETURNING *. Теперь обратимся к внешнему запросу. В нем также присутствует команда INSERT, которая получает данные для ввода в таблицу aircrafts_log от запроса SELECT. А этот запрос, в свою очередь, получает данные от временной таблицы add_row, ука- занной в предложении FROM. Поскольку в предложении RETURNING мы указали в качестве возвращаемого значения «*», то будут возвращены все столбцы таблицы aircrafts_tmp, т. е. той таблицы, в которую строки были вставлены. Следовательно, в команде SELECT внешнего запроса можно ссылаться на имена этих столбцов: SELECT add_row.aircraft_code, add_row.model, add_row.range, ... Поскольку в таблице aircrafts_log существует еще два столбца, то для них мы допол- нительно передаем значения CURRENT_TIMESTAMP и 'INSERT'. Проверим, что получилось: SELECT * FROM aircrafts_tmp ORDER BY model; aircraft_code | model | range ---------------+---------------------+------- 319 | Airbus A319-100 | 6700 320 | Airbus A320-200 | 5700 321 | Airbus A321-200 | 5600 733 | Boeing 737-300 | 4200 763 | Boeing 767-300 | 7900 773 | Boeing 777-300 | 11100 CR2 | Bombardier CRJ-200 | 2700 CN1 | Cessna 208 Caravan | 1200 SU9 | Sukhoi SuperJet-100 | 3000 (9 строк) Проверим также и содержимое журнальной таблицы: SELECT * FROM aircrafts_log ORDER BY model; --[ RECORD 1 ]-+--------------------------- aircraft_code | 319 model | Airbus A319-100 range | 6700 when_add | 2017-01-31 18:28:49.230179 operation | INSERT --[ RECORD 2 ]-+--------------------------- aircraft_code | 320 model | Airbus A320-200 range | 5700 167 when_add | 2017-01-31 18:28:49.230179 operation | INSERT При вставке новых строк могут возникать ситуации, когда нарушается ограничение первичного или уникального ключей, поскольку вставляемые строки могут иметь значения ключевых атрибутов, совпадающие с теми, что уже имеются в таблице. Для таких случаев предусмотрено специальное средство — предложение ON CONFLICT, оно предусматривает два варианта действий на выбор программиста. Первый вари- ант — отменять добавление новой строки, для которой имеет место конфликт зна- чений ключевых атрибутов, и при этом не порождать сообщения об ошибке. Второй вариант заключается в замене операции добавления новой строки операцией обнов- ления существующей строки, с которой конфликтует добавляемая строка. Начнем с первого варианта. Попробуем добавить строку, которая гарантированно бу- дет конфликтовать с уже существующей строкой, причем, как по первичному ключу aircraft_code, так и по уникальному ключу model. WITH add_row AS ( INSERT INTO aircrafts_tmp VALUES ( 'SU9', 'Sukhoi SuperJet-100', 3000 ) ON CONFLICT DO NOTHING RETURNING * ) INSERT INTO aircrafts_log SELECT add_row.aircraft_code, add_row.model, add_row.range, CURRENT_TIMESTAMP, 'INSERT' FROM add_row; Обратите внимание, что не будет выведено никаких сообщений об ошибках, как это и предполагалось. Строка добавлена не будет: INSERT 0 0 Нужно учитывать, что это сообщение относится к таблице aircrafts_log, т. е. к команде в главном запросе, а не в общем табличном выражении WITH add_row AS (...), в кото- ром мы работаем с таблицей aircrafts_tmp. Проверьте, не была ли добавлена строка в таблицу aircrafts_tmp. В том случае, когда в предложении ON CONFLICT не указана дополнительная инфор- мация об именах столбцов или ограничений, по которым предполагается возможный конфликт, проверка выполняется по первичному ключу и по уникальным ключам. Давайте укажем конкретный столбец для проверки конфликтующих значений. Пусть это будет столбец aircraft_code, т. е. первичный ключ. Для упрощения команды не бу- дем использовать общее табличное выражение. Добавляемая строка будет иметь кон- фликт с существующей строкой как по столбцу aircraft_code, так и по столбцу model. INSERT INTO aircrafts_tmp VALUES ( 'SU9', 'Sukhoi SuperJet-100', 3000 ) ON CONFLICT ( aircraft_code ) DO NOTHING RETURNING *; Получим только такое сообщение: 168 aircraft_code | model | range ---------------+-------+------- (0 строк) INSERT 0 0 Это сообщение было выведено потому, что в команду включено предложение RETURNING *. Сообщение о дублировании значений столбца model не выводится. Давайте в команде INSERT изменим значение столбца aircraft_code, чтобы оно стало уникальным: INSERT INTO aircrafts_tmp VALUES ( 'S99', 'Sukhoi SuperJet-100', 3000 ) ON CONFLICT ( aircraft_code ) DO NOTHING RETURNING *; Поскольку конфликта по столбцу aircraft_code нет, то далее проверяется выполнение требования уникальности по столбцу model. В результате мы получим традиционное сообщение об ошибке, относящееся к столбцу model: ОШИБКА: повторяющееся значение ключа нарушает ограничение уникальности "aircrafts_tmp_model_key" ,→ ПОДРОБНОСТИ: Ключ "(model)=(Sukhoi SuperJet-100)" уже существует. Теперь рассмотрим второй вариант обработки предложения ON CONFLICT, когда опе- рация вставки новой строки заменяется операцией обновления существующей стро- ки, с которой и возник конфликт значений столбцов. Для реализации этой возмож- ности служит предложение DO UPDATE. Давайте модифицируем команду и добавим предложение DO UPDATE. Выберем та- кую политику для работы с таблицей aircrafts_tmp: если при вставке новой строки имеет место дублирование по атрибутам первичного ключа со строкой, находящей- ся в таблице, тогда мы будем обновлять значения всех остальных атрибутов в этой строке, независимо от того, совпадают ли они со значениями в новой строке или нет. В качестве примера сделаем так: в добавляемой строке значение атрибута model сде- лаем отличающимся от того, которое уже есть в таблице (вместо «Sukhoi SuperJet-100» будет «Sukhoi SuperJet»), а значение атрибута range оставим без изменений (3000). Внесем в команду еще одно изменение, а именно: вместо имени столбца, образую- щего первичный ключ, с помощью предложения ON CONSTRAINT укажем наимено- вание ограничения первичного ключа. Вот так выглядит команда с предложением DO UPDATE: INSERT INTO aircrafts_tmp VALUES ( 'SU9', 'Sukhoi SuperJet', 3000 ) ON CONFLICT ON CONSTRAINT aircrafts_tmp_pkey DO UPDATE SET model = excluded.model, range = excluded.range RETURNING *; Поскольку мы включили в команду предложение RETURNING *, то СУБД сообщит о том, какие значения получат атрибуты обновленной строки. Как и планировалось, изменилось только значение атрибута model. 169 aircraft_code | model | range ---------------+-----------------+------- SU9 | Sukhoi SuperJet | 3000 (1 строка) В случае конфликта по столбцу aircraft_code будет обновлена та строка в таблице aircrafts_tmp, с которой конфликтовала вновь добавляемая строка. В результате новая строка добавлена не будет, а будет обновлено значение столбца model в строке, уже находящейся в таблице. А где PostgreSQL возьмет значение для использования в ко- манде UPDATE? Это значение будет взято из специальной таблицы excluded, которая поддерживается самой СУБД. В этой таблице хранятся все строки, предлагаемые для вставки в рамках текущей команды INSERT. Вот это значение — excluded.model. Зна- чение столбца range также будет обновлено, но его новое значение — excluded.range — совпадает со старым. Обратите внимание, что в предложении DO UPDATE не указывается имя таблицы, т. к. таблица будет та же самая, которая указана в предложении INSERT. Предложение ON CONFLICT DO UPDATE гарантирует атомарное выполнение опера- ции вставки или обновления строк. Атомарность означает, что проверка наличия конфликта и последующее обновление выполняются как неделимая операция, т. е. другие транзакции не могут изменить значение столбца, вызывающее конфликт, так, чтобы в результате конфликт исчез и уже стало возможным выполнить операцию INSERT, а не UPDATE, или, наоборот, в случае отсутствия конфликта он вдруг появил- ся, и уже операция INSERT стала бы невозможной. Такая атомарная операция даже имеет название UPSERT — «UPDATE или INSERT». Для массового ввода строк в таблицы используется команда COPY. Эта команда может копировать данные из файла в таблицу. Причем, в качестве файла может служить и стандартный ввод. Хотя в этом разделе пособия мы, в основном, говорим о вставке строк в таблицы, но нужно сказать и о том, что эта команда может также выводить данные из таблиц в файлы и на стандартный вывод. В качестве примера ввода данных из файла давайте добавим две строки в таблицу aircrafts_tmp. Сначала необходимо подготовить текстовый файл, содержащий новые данные. В этом файле каждая строка соответствует одной строке таблицы. Значения атрибутов разделяются символами табуляции, поэтому пробелы, которые в нашем примере есть в столбце model, можно вводить в файл без каких-либо дополнительных экранирующих символов. Заключать строковые значения в одинарные кавычки не нужно, иначе они также будут введены в таблицу. Завершить файл нужно строкой, содержащей только символы «\.». Получим файл следующего содержания: IL9 Ilyushin IL96 9800 I93 Ilyushin IL96-300 9800 \. Теперь нужно ввести команду COPY, указав полный путь к вашему файлу: COPY aircrafts_tmp FROM '/home/postgres/aircrafts.txt'; В результате будет выведено сообщение об успешном добавлении двух строк: COPY 2 Давайте проверим, что получилось: 170 SELECT * FROM aircrafts_tmp; Вы увидите, что новые строки были добавлены, но все те, что уже находились в таб- лице, удалены не были. При добавлении строк с помощью команды COPY выполняются проверки всех огра- ничений, наложенных на таблицу, поэтому ввести дублирующие данные не получит- ся. Эту команду можно использовать и для вывода данных из таблицы в файл: COPY aircrafts_tmp TO '/home/postgres/aircrafts_tmp.txt' WITH ( FORMAT csv ); Предложение FORMAT csv говорит о том, что при выводе данных значения столбцов разделяются запятыми (CSV — Comma Separated Values). Получим файл такого вида: 773,Boeing 777-300,11100 763,Boeing 767-300,7900 SU9,Sukhoi SuperJet-100,3000 Если формат не указывать, то данные будут выведены с использованием символов табуляции в качестве разделителей значений атрибутов. 7.2 Обновление строк в таблицах Команда UPDATE предназначена для обновления данных в таблицах. Начнем с того, что покажем, как и при изучении команды INSERT, как можно организовать запись выполненных операций в журнальную таблицу. Эта команда аналогична команде, уже рассмотренной в предыдущем разделе. В ней также «полезная» работа выполня- ется в общем табличном выражении, а запись в журнальную таблицу — в основном запросе. WITH update_row AS ( UPDATE aircrafts_tmp SET range = range * 1.2 WHERE model '^Bom' RETURNING * ) INSERT INTO aircrafts_log SELECT ur.aircraft_code, ur.model, ur.range, CURRENT_TIMESTAMP, 'UPDATE' FROM update_row ur; Выполнив команду, в ответ получим сообщение INSERT 0 1 171 Напомним, что выведенное сообщение относится непосредственно к внешнему за- просу, в котором выполняется операция INSERT, добавляющая строку в журнальную таблицу. Конечно, если бы строка в таблице aircrafts_tmp не была успешно обновле- на, тогда предложение RETURNING * не возвратило бы внешнему запросу ни одной строки, и, следовательно, тогда просто не было бы данных для формирования новой строки в таблице aircrafts_log. При использовании команды UPDATE в общем табличном выражении нужно учиты- вать, что главный запрос может получить доступ к обновленным данным только че- рез временную таблицу , которую формирует предложение RETURNING: ... FROM update_row ur; ... Можно выполнить выборку из журнальной таблицы aircrafts_log, чтобы посмотреть — правда, не очень длинную — историю изменений строки с описанием самолета Bombardier CRJ-200. SELECT * FROM aircrafts_log WHERE model '^Bom' ORDER BY when_add; --[ RECORD 1 ]-+--------------------------- aircraft_code | CR2 model | Bombardier CRJ-200 range | 2700 when_add | 2017-02-05 00:27:38.591958 operation | INSERT --[ RECORD 2 ]-+--------------------------- aircraft_code | CR2 model | Bombardier CRJ-200 range | 3240 when_add | 2017-02-05 00:27:56.688933 operation | UPDATE Представим себе такую ситуацию: руководство компании хочет иметь возможность видеть динамику продаж билетов по всем направлениям, а именно: общее число про- данных билетов и дату/время последнего увеличения их числа для конкретного на- правления. Создадим временную таблицу, которую назовем tickets_directions. В ней будет четыре столбца: – город отправления — departure_city; – город прибытия — arrival_city; – дата/время последнего увеличения числа проданных билетов — last_ticket_time; – число проданных билетов на этот момент времени по данному направлению — tickets_num. Создадим таблицу с помощью запроса к представлению «Маршруты» (routes) и за- полним данными, однако в ней сначала будет только два первых столбца. 172 CREATE TEMP TABLE tickets_directions AS SELECT DISTINCT departure_city, arrival_city FROM routes; Ключевое слово DISTINCT является здесь обязательным: ведь нам нужны только уни- кальные пары городов отправления и прибытия. Добавим еще два столбца и заполним столбец-счетчик нулевыми значениями. ALTER TABLE tickets_directions ADD COLUMN last_ticket_time timestamp; ALTER TABLE tickets_directions ADD COLUMN tickets_num integer DEFAULT 0; Поскольку PostgreSQL не требует обязательного создания первичного ключа, то не будем создавать его. Это не помешает нам однозначно идентифицировать строки в таблице tickets_directions. Поскольку в команде UPDATE нет предложения WHERE, в котором было бы условие, ограничивающее множество обновляемых строк, то будут обновлены все строки таб- лицы — во все будет записано значение 0 в столбец tickets_num. Для того чтобы не усложнять изложение материала, создадим временную таблицу, являющуюся аналогом таблицы «Перелеты» (ticket_flights), однако без внешних клю- чей. Поэтому мы сможем добавлять в нее строки, не заботясь о добавлении строк в таблицы «Билеты» (tickets) и «Бронирования» (bookings). Тем не менее, первич- ный ключ все же создадим, чтобы продемонстрировать, что в случае попытки ввода строк с дубликатными значениями первичного ключа значения счетчиков в таблице tickets_directions наращиваться не будут. CREATE TEMP TABLE ticket_flights_tmp AS SELECT * FROM ticket_flights WITH NO DATA; ALTER TABLE ticket_flights_tmp ADD PRIMARY KEY ( ticket_no, flight_id ); Теперь представим команду, которая и будет добавлять новую запись о про- даже билета и увеличивать значение счетчика проданных билетов в таблице tickets_directions. WITH sell_ticket AS ( INSERT INTO ticket_flights_tmp ( ticket_no, flight_id, fare_conditions, amount ) VALUES ( '1234567890123', 30829, 'Economy', 12800 ) RETURNING * ) UPDATE tickets_directions td SET last_ticket_time = CURRENT_TIMESTAMP, tickets_num = tickets_num + 1 WHERE ( td.departure_city, td.arrival_city ) = ( SELECT departure_city, arrival_city FROM flights_v WHERE flight_id = ( SELECT flight_id FROM sell_ticket ) ); 173 UPDATE 1 Этот запрос работает следующим образом. Добавление новой записи о бронировании авиаперелета производится в общем табличном выражении, а наращивание соответ- ствующего счетчика — в главном запросе. Поскольку в общем табличном выраже- нии присутствует предложение RETURNING *, значения атрибутов добавленной стро- ки будут доступны в главном запросе посредством обращения к временной таблице sell_ticket. Конечно, если строка фактически не будет добавлена из-за дублирования значения первичного ключа, тогда будет сгенерировано сообщение об ошибке, в ре- зультате главный запрос выполнен не будет, следовательно, таблица tickets_directions не будет обновлена. В главном запросе мы обновляем всего два атрибута, причем, значение атри- бута tickets_num может увеличиться только на единицу, поскольку в таблицу ticket_flights_tmp добавляется одна строка. Остается выяснить, каким образом мы сможем определить ту строку в таблице tickets_directions, атрибуты которой нуж- но обновить. Нам требуется на основе значения идентификатора рейса flight_id, на который был забронирован билет (перелет), определить города отправления и при- бытия, которые как раз и идентифицируют строку в таблице tickets_directions. Эти три атрибута присутствуют в представлении flights_v. Подзапрос обращается к этому представлению, а вложенный подзапрос возвращает значение идентификатора рей- са flight_id, на который был забронирован билет (перелет). Назначение вложенного подзапроса в том, чтобы в условии WHERE flight_id = ... не дублировать значение ат- рибута flight_id, использованное в команде INSERT (в данном примере это 30829). Тем самым должен быть снижен риск ошибки при вводе данных. Обратите внимание, что подзапрос в предложении WHERE возвращает два столбца, и сравнение выполняется также сразу с двумя столбцами. Посмотрим, что получилось: SELECT * FROM tickets_directions WHERE tickets_num > 0; --[ RECORD 1 ]----+--------------------------- departure_city | Сочи arrival_city | Красноярск last_ticket_time | 2017-02-04 21:15:32.903687 tickets_num | 1 Представим другой вариант этой команды. Его принципиальное отличие от перво- го варианта заключается в том, что для определения обновляемой строки в таблице tickets_directions используется операция соединения таблиц. Здесь в главном за- просе UPDATE присутствует предложение FROM, однако в этом предложении указы- вается только таблица (представление) flights_v, а таблицу tickets_directions в пред- ложение FROM включать не нужно, хотя она и участвует в выполнении соединения таблиц. Конечно, в предложении SET присваивать новые значения можно только ат- рибутам таблицы tickets_directions, поскольку именно она приведена в предложении UPDATE. WITH sell_ticket AS ( INSERT INTO ticket_flights_tmp (ticket_no, flight_id, fare_conditions, amount ) VALUES ( '1234567890123', 7757, 'Economy', 3400 ) RETURNING * 174 ) UPDATE tickets_directions td SET last_ticket_time = CURRENT_TIMESTAMP, tickets_num = tickets_num + 1 FROM flights_v f WHERE td.departure_city = f.departure_city AND td.arrival_city = f.arrival_city AND f.flight_id = ( SELECT flight_id FROM sell_ticket ); UPDATE 1 Посмотрим, что получилось. SELECT * FROM tickets_directions WHERE tickets_num > 0; --[ RECORD 1 ]----+--------------------------- departure_city | Сочи arrival_city | Красноярск last_ticket_time | 2017-02-04 21:15:32.903687 tickets_num | 1 --[ RECORD 2 ]----+--------------------------- departure_city | Москва arrival_city | Сочи last_ticket_time | 2017-02-04 21:18:40.353408 tickets_num | 1 Чтобы увидеть комбинированную строку, которая получилась при соединении таб- лиц tickets_directions и flights_v, можно включить в команду UPDATE предложение RETURNING *. 7.3 Удаление строк из таблиц Начнем рассмотрение команды DELETE, предназначенной для удаления данных из таблиц, с того, что, как и при изучении команды INSERT, покажем, как можно орга- низовать запись выполненных операций в журнальную таблицу. Эта команда анало- гична команде, уже рассмотренной в предыдущем разделе. В ней также «полезная» работа выполняется в общем табличном выражении, а запись в журнальную табли- цу — в основном запросе. WITH delete_row AS ( DELETE FROM aircrafts_tmp WHERE model '^Bom' RETURNING * ) INSERT INTO aircrafts_log SELECT dr.aircraft_code, dr.model, dr.range, CURRENT_TIMESTAMP, 'DELETE' FROM delete_row dr; Выполнив команду, в ответ получим сообщение INSERT 0 1 175 Напомним, что выведенное сообщение относится непосредственно к внешнему за- просу, в котором выполняется операция INSERT, добавляющая строку в журнальную таблицу. Посмотрим историю изменений строки с описанием самолета Bombardier CRJ-200: SELECT * FROM aircrafts_log WHERE model '^Bom' ORDER BY when_add; --[ RECORD 1 ]-+--------------------------- aircraft_code | CR2 model | Bombardier CRJ-200 range | 2700 when_add | 2017-02-05 00:27:38.591958 operation | INSERT --[ RECORD 2 ]-+--------------------------- aircraft_code | CR2 model | Bombardier CRJ-200 range | 3240 when_add | 2017-02-05 00:27:56.688933 operation | UPDATE --[ RECORD 3 ]-+--------------------------- aircraft_code | CR2 model | Bombardier CRJ-200 range | 3240 when_add | 2017-02-05 00:34:59.510911 operation | DELETE Для удаления конкретных строк из данной таблицы можно использовать информа- цию не только из нее, но также и из других таблиц. Выбирать строки для удаления можно двумя способами: использовать подзапросы к этим таблицам в предложении WHERE или указать дополнительные таблицы в предложении USING, а затем в пред- ложении WHERE записать условия соединения таблиц. Поскольку первый способ яв- ляется традиционным, то мы покажем второй из них. Предположим, что руководство авиакомпании решило удалить из парка самолетов машины компаний Boeing и Airbus, имеющие наименьшую дальность полета. Решим эту задачу следующим образом. В общем табличном выражении с помощью условия model '^Airbus' OR model '^Boeing' в предложении WHERE отберем моде- ли только компаний Boeing и Airbus. Затем воспользуемся оконной функцией rank и произведем ранжирование моделей каждой компании по возрастанию дальности полета. Те модели, ранг которых окажется равным 1, будут иметь наименьшую даль- ность полета. В предложении USING сформируем соединение таблицы aircrafts_tmp с временной таблицей min_ranges, а затем в предложении WHERE зададим условия для отбора строк. WITH min_ranges AS ( SELECT aircraft_code, rank() OVER ( |