Реструктуризация таблиц

Несмотря на возможность включения новых полей в существующую таблицу, следует помнить, что в PostgreSQL не поддерживается удаление полей. Существует два относительно простых способа реструктуризации существующих таблиц. Первый способ основан на использовании команды CREATE TABLE A, а во втором способе команда CREATE TABLE объединяется с командой INSERT INTO.

Фактически оба способа сводятся к созданию новой таблицы требуемой структуры, заполнению ее данными из существующей таблицы и переименованию. Таким образом, новая таблица занимает место старой.

Внимание! При реструктуризации таблиц следует помнить, что идентификаторы объектов (OID) изменятся, а старые индексы, сгенерированные для исходной таблицы, не будут автоматически действовать в новых таблицах. Все индексы необходимо удалить и построить заново.

Реструктуризация таблиц командой CREATE TABLE AS

Распространенная методика реструктуризации таблиц основана на использовании команды CREATE TABLE с секцией AS в сочетании с запросом SQL. Команда создает временную таблицу на основании существующей таблицы, после чего временная таблица переименовывается. Физическое создание новой таблицы может сопровождаться удалением полей и изменением порядка их следования с одновременным заполнением данными из исходной таблицы.

В приведенном ниже описании синтаксиса этой усеченной версии команды CREATE TABLE запрос представляет собой команду SELECT для выборки данных, переносимых в новую таблицу. Типы данных всех создаваемых полей определяются типами данных соответствующих полей, выбранных в результате выполнения запроса.

CREATE [ TEMPORARY | TEMP ] TABLE таблица [ (имя_поля [….])] AS запрос

Преимущество такого подхода заключается в том, что создание таблицы и заполнение ее данными происходит в одной команде SQL. Самый заметный недостаток – отсутствие полноценных возможностей для установки ограничений в созданной таблице. После того как таблица создана, в нее можно добавлять только ограничения внешних ключей и проверки. После создания новой таблицы старую таблицу можно переименовать (или уничтожить) и присвоить новой таблице имя старой.

Допустим, из таблицы books удаляется лишнее поле publication, созданное ранее. Для этого мы создаем усеченную версию таблицы (с перечислением нужных полей) с указанием соответствующей команды SELECT в секции AS команды CREATE TABLE, после чего старая таблица удаляется командой DROP TABLE (листинг 8).

Листинг 8. Реструктуризация таблицы командой CREATE TABLE AS.

booktown=# \d books
Table "books"
Attribute | Type | Modifier
id        | integer | not null
title     |   text  | not null
author_id | integer |
subject_id| integer |
publication | date |

Index: books_id_pkey

booktown=# CREATE TABLE new_books (id, title, author_id, subject_id) AS SELECT id, title, author_id, subject_id  FROM books;
SELECT

booktown=# ALTER TABLE books RENAME TO old_books;
ALTER

booktown=# ALTER TABLE new_books RENAME TO books;
ALTER

booktown=# \d books
Table "books"
Attribute | Type | Modifier
id        | integer |
title     | text |
author_id | integer |
subject_id| integer |

booktown=# DROP TABLE books;
DROP

Внимание пользователям старых версий! В версии PostgreSQL 7.1.x присутствие в команде SELECT необязательного списка полей, заключенного в круглые скобки, исключает использование специального символа *.

Если таблица, создаваемая командой CREATE TABLE AS, вас почему-либо не устраивает (например, если в таблице необходимо установить ограничения полей), то одну команду CREATE TABLE AS можно заменить двумя командами SQL. Сначала команда CREATE TABLE создает новую таблицу, а затем команда INSERT INTO с запросом SELECT заполняет ее данными (листинг 9).

Листинг 9. Реструктуризация таблицы командами CREATE TABLE и INSERT INTO.

booktown=# CREATE TABLE new_books (id integer UNIQUE, title text NOT NULL, author_id integer, subject_id integer, CONSTRAINT books_id_pkey PRIMARY КЕУ (id);

NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey'
for table 'new_books'
CREATE

booktown=# INSERT INTO new_books SELECT id, title, author_id, subject_id FROM books;
INSERT 0 12

booktown=# ALTER TABLE books RENAME TO old_books;
ALTER

booktown=# ALTER TABLE new_books RENAME TO books:
ALTER

booktown=# \d books
Table "books"
Attribute | Type    | Modifier
id        | integer | not null
title     | text    | not null
author_id |integer |
subject_id integer |

Index: books_id_pkey

Удаление таблиц командой DROP TABLE

В SQL таблицы удаляются командой DROP TABLE. Команда имеет следующий синтаксис (таблица – имя удаляемой таблицы):

DROP TABLE таблица

Использование команды DROP TABLE требует осторожности, поскольку удаление таблицы приводит к уничтожению всех хранящихся в ней данных.

Примечание. Уничтожение таблицы с неявно созданным индексом приводит к уничтожению всех связанных с ней индексов.

Добавление данных командами INSERT и COPY

После создания таблицы с заданной структурой наступает следующий этап – заполнение таблицы данными. В PostgreSQL имеются три общих способа заполнения таблиц данными:

• вставка новых группированных данных командой INSERT INTO;
• вставка существующих данных из другой таблицы командой INSERT INTO в сочетании с командой SELECT;

Вставка новых данных

Ниже приведен синтаксис команды INSERT INTO при вставке новых данных:

INSERT INTO таблица
[ (имя_поля [,…]) ] VALUES (значение [….])

Ниже перечислены параметры команды.

• таблица. Имя таблицы, в которую вставляются данные командой SQL INSERT.
• (имя_поля [….]). Необязательный группированный список полей новой записи, которым присваиваются значения.
• VALUES. Ключевое слово SQL, за которым следует группированный список значений.
• (значение [,… ]). Обязательный группированный список значений полей. Для каждого поля указывается ровно одно значение, элементы списка разделяются запятыми. Элемент списка может быть выражением (например, операцией с двумя операндами) или константой.

Тип каждого значения в секции VALUES должен соответствовать типу поля, которому оно присваивается. Если необязательный список полей отсутствует, PostgreSQL предполагает, что секция VALUES содержит значения всех полей в структуре таблицы в порядке их определения. Если количество значений меньше количества полей, PostgreSQL пытается использовать значение по умолчанию (или NULL при его отсутствии) для каждого пропущенного элемента.

В листинге 10 приведен пример создания новой записи в таблице books базы данных booktown.

Листинг 10. Вставка новой записи в таблицу books.

booktown=# INSERT INTO books (id, title, author_id, subject_id) VALUES (41472, 'Practical PostgreSQL', 1212, 432);

INSERT
3574037 1

Команда SQL, приведенная в листинге 10, вставляет новую запись с кодом (id) 41472, названием “Practical PostgreSQL”, кодом автора 1212 и кодом темы 432. Обратите внимание на завершающее сообщение, начинающееся со слова INSERT, – оно указывает на то, что операция вставки была выполнена успешно. Первое число после INSERT является идентификатором объекта (OID) созданной записи, а второе число обозначает количество созданных записей (в нашем примере,– 1).

В приведенном примере необязательный список полей совпадает с порядком следования полей в структуре таблицы (слева направо). В данном случае этот список можно опустить, поскольку команда INSERT предполагает, что значения присваиваются в естественном порядке следования полей таблицы. Поля в списке можно переставить, но в этом случае порядок значений в секции VALUES тоже должен измениться, как показано в листинге 11.

Листинг 11. Изменение порядка перечисления полей.

INSERT INTO books (subjected, author_id, id, title) VALUES (4, 7805, 41473, 'Programming Python');
INSERT 3574041 1

Вставка данных из других таблиц командой SELECT

Команда INSERT INTO применяется и в другой ситуации – когда данные, сохраняемые в таблице, уже присутствуют в другой таблице (или нескольких таблицах). В этом случае команда имеет следующий синтаксис:

INSERT INTO таблица
[ (имя_поля [….]) ] запрос

По аналогии с синтаксисом INSERT INTO, команда содержит необязательный список полей, которым присваиваются новые значение. Тем не менее в этой форме INSERT INTO секция VALUES заменяется полной командой SQL SELECT.

Предположим, база данных booktown содержит таблицу book_queue с информацией о книгах, ожидающих поступления в продажу. После подтверждения данные переносятся из таблицы book_queue в обычную таблицу books. Пример решения этой задачи продемонстрирован в листинге 12.

Листинг 12. Вставка данных из другой таблицы.

INSERT INTO books (id, title, author_id, subject_id) SELECT nextval('book_ids'), title, author_id, subject_id FROM book_queue WHERE approved;
INSERT 0 2

В приведенном примере запрос SELECT, включенный в команду INSERT INTO, переносит две записи из таблицы book_queue в таблицу books. В этом контексте допускается использование любой синтаксически правильной команды SELECT. В нашем примере в выборку включается результат вызова функции nextval () для последовательности book_ids, за которым следуют значения полей title, author_id и subject_id из таблицы book_queue.

На этот раз команда создает сразу несколько новых записей, поэтому в сообщении об успешном выполнении операции вместо значения OID, которое выводилось бы при вставке одной записи, выводится 0. Второе число, как и в случае с обычной командой INSERT INTO, равно количеству созданных записей (в данном случае – 2).