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

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

 

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

• вставка новых группированных данных командой INSERT INTO;
• вставка существующих данных из другой таблицы командой INSERT INTO в сочетании с командой SELECT;
• вставка данных из внешнего файла командой COPY (или \copy).

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

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

INSERT INTO таблица

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

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

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

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

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

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

booktown=# INSERT INTO books (Id, title, author_id, subject_id)

booktown-# VALUES (41472, ‘Practical PostgreSQL’, 1212, 4);

INSERT 3574037 1

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

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

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

booktown=# INSERT INTO books (subjected, author_id, id, title)

booktown-# 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. Пример решения этой задачи продемонстрирован в листинге 4.18.

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

booktown-# INSERT INTO books (id, title, author_id, subject_id)

booktown-# SELECT nextval(‘book_ids’, title, author_id, subject_id

booktown-# FROM book_queue WHERE approved;

INSERT 0 2

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

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

 

Копирование данных из внешних файлов командой COPY

В PostgreSQL поддерживается и такая полезная возможность, как прямое импортирование данных в таблицу из внешних файлов командой COPY. Файл, содержащий входные данные команды COPY, хранится либо в стандартном текстовом формате ASCII с ограничением полей специальным символом-разделителем, либо в двоичном формате таблиц PostgreSQL. В ASCII-файлах в качестве разделителя обычно используется символ табуляции или запятая. При импортировании данных из ASCII-файла каждая строка файла интерпретируется как отдельная запись данных, а каждый компонент строки — как значение соответствующего поля записи.

Команда COPY FROM работает значительно быстрее обычной команды INSERT, поскольку данные передаются прямо в приемную таблицу за одну транзакцию. С другой стороны, к формату исходного файла предъявляются чрезвычайно жесткие требования, поэтому ошибка всего в одной строке приводит к сбою всей команды COPY.

Синтаксис команды COPY FROM:

COPY [ BINARY ] таблица [ WITH 0IDS ]
FROM { ‘имя_файла’ \ stdin }
[ [USING] DELIMITERS ‘разделитель’ ]
[ WITH NULL AS ‘строка_nulГ ]

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

• BINARY. Признак импортирования входных данных из двоичного файла, ранее созданного командой COPY TO.
• таблица. Имя таблицы, в которую импортируются данные.
• WITH OIDS. Из первой строки файла загружаются значения всех идентификаторов OID импортируемой таблицы.
• FROM { ‘имя_файла’ \ stdin }. Источник, из которого PostgreSQL получает входные данные — файл с заданным именем либо стандартный ввод (stdin).
• [ USING ] DELIMITERS ‘разделитель’. Символ, используемый в качестве разделителя при разборе входных данных. Не используется для файлов, выведенных в двоичном формате PostgreSQL.
• WITH NULL AS ‘ строка_null. Заданная строка должна интерпретироваться как значение NULL. He используется для файлов, выведенных в двоичном формате PostgreSQL.

При подготовке к импортированию файла проследите за тем, чтобы этот файл был доступен для чтения процессом postmaster (то есть пользователем, запустившим PostgreSQL). Кроме того, разрешены только абсолютные имена файлов; при попытке передать относительное имя происходит ошибка.

При работе с входными файлами в формате ASCII в секции DELIMITERS передается символ, используемый в качестве разделителя значений полей в строках файла. Если разделитель не указан, PostgreSQL считает, что значения разделяются символом табуляции. Необязательная секция WITH NULL определяет формат, в котором передаются значения NULL. Если секция отсутствует, PostgreSQL интерпретирует последовательность \N как NULL (например, пустые поля исходного файла по умолчанию интерпретируются как пустые строковые константы, а не как NULL).

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

В листинге 4.19 приведено содержимое файла, выведенного PostgreSQL в формате ASCII. Поля разделяются запятыми, а для представления NULL используется строка \null.B файле сохранены данные из таблицы subjects базы данных booktown.

Листинг 4.19. Пример копируемого ASCII-файла

1.Business.Productivity Ave

2.Children’s Books,Kids Ct

3.Classics.Academic Rd

4,Computers,Productivity Ave

5,Cooking.Creativity St

12.Religion.\null

8.Hi story.Academic Rd

9.Horror.Black Raven Dr

10.Mystery.Black Raven Dr

11.Poetry.Sunset Dr

13.Romance.Main St

14.Science.Productivity Ave

15.Science Fiction.Main St

0.Arts.Creativity St

6.Drama.Main St

7.Entertainment.Main St

Следующая команда (листинг 4.20) импортирует содержимое файла /tmp/ subjects.sql в таблицу subjects базы данных booktown.

Листинг 4.20. Копирование ASCII-файла

booktown=# COPY subjects FROM ‘/tmp/subjects.sql’

booktown-# USING DELIMITERS ‘.’ WITH NULL AS ‘\null;

COPY

Двоичный формат

Команда COPY также позволяет выполнять операции ввода и вывода с данными в двоичном формате. Если команда COPY FROM содержит ключевое слово BINARY, входной файл должен быть создан командой COPY TO в двоичном формате PostgreSQL. Двоичные файлы загружаются быстрее ASCII-файлов, но в отличие от последних их нельзя читать и редактировать в простых текстовых редакторах.

В листинге 4.21 приведена команда COPY, предназначенная для вставки записей из двоичного файла в таблицу subjects базы данных booktown.

Листинг 4.21. Копирование двоичного файла

booktown=# COPY BINARY subjects FROM ‘/tmp/subjects.sql’;

COPY

Различия команд COPY и \copy

Команда SQL COPY не эквивалентна команде psql \copy. Команда \copy имеет тот же синтаксис (хотя и не завершается точкой с запятой), она выполняет операцию че-

рез клиента psql, а не через серверный процесс postmaster. В результате команда \copy выполняется с правами пользователя, запустившего psql, а не с теми правами, с которыми работает процесс postmaster.

 

COPY TO

Синтаксис команды COPY FROM практически аналогичен синтаксису команды, предназначенной для экспортирования данных в файл, просто ключевое слово FROM заменяется ключевым словом ТО. Кроме того, ключевое слово stdin заменяется ключевым словом stdout, если выходные данные вместо файла направляются в стандартный вывод (например, на экран в psql). В листинге 4.22 приведен пример экспортирования таблицы books в ASCII-файл.

Листинг 4.22. Экспортирование таблицы books в файл ASCII

booktown=# COPY books TO ‘filename’;

COPY

Секция WITH OIDS

Файлы, содержащие идентификаторы объектов (созданные командой COPY TO с секцией WITH OIDS), загружаются командой COPY FROM, в которую также включается секция WITH OIDS. Попытки использования команды COPY FROM с секцией WITH OIDS по отношению к файлу, в котором значения OID не были сохранены при создании, завершаются неудачей.

Экспортирование данных с идентификаторами объектов принадлежит к числу специальных возможностей, присущих только команде COPY. Значения OID являются системными и поэтому не могут изменяться командами INSERT и UPDATE. Если не принять меры предосторожности, в таблице могут появиться две записи с одинаковыми значениями OID.