COPY

Описание

COPY перемещает данные между таблицами PostgreSQL и традиционными файлами. COPY TO копирует содержимое таблицы в файл, а COPY FROM — из файла в таблицу (добавляет данные к тем, что уже содержались в таблице). COPY TO может также скопировать результаты запроса SELECT.

Если указывается список колонок, COPY скопирует в или из файла только данные указанных колонок. Если в таблице есть колонки, отсутствующие в этом списке, COPY FROM заполнит эти колонки значениями по умолчанию.

COPY с именем файла указывает серверу PostgreSQL читать или записывать непосредственно этот файл. Заданный файл должен быть доступен пользователю PostgreSQL (тому пользователю, от имени которого работает сервер) и это имя должно быть определено с точки зрения сервера. Когда указывается параметр PROGRAM, сервер выполняет заданную команду и читает данные из стандартного вывода программы, либо записывает их в стандартный ввод. Команда должна определяться с точки зрения сервера и быть доступной для исполнения пользователю PostgreSQL. Когда указывается STDIN или STDOUT, данные передаются через соединение клиента с сервером.

Параметры

Выводимая информация

В случае успешного завершения, COPY возвращает метку команды в виде

COPY число

Здесь число — количество скопированных записей.

Замечания

COPY может использоваться только с обычными таблицами, но не с представлениями. Однако, при необходимости можно скопировать представление так: COPY (SELECT * FROM имя_представления) TO ....

COPY обрабатывает только явно заданную таблицу, дочерние таблицы при копировании данных не затрагиваются. Поэтому, например COPY таблица TO выводит те же данные, что и запрос SELECT * FROM ONLY table. Для выгрузки всех данных в иерархии наследования можно применить COPY (SELECT * FROM table) TO ....

В таблице, данные которой читает команда COPY TO, требуется иметь право на выборку данных, а в таблице, куда вставляет значения COPY FROM, требуется право на добавление. При этом, если в команде перечисляются избранные колонки, достаточно иметь права только для них.

Файлы, указанные в команде COPY, читаются или записываются непосредственно сервером, не клиентским приложением. Поэтому они должны располагаться на сервере или быть доступными серверу, а не клиенту. Они должны быть доступны на чтение или запись пользователю PostgreSQL (пользователю, от имени которого работает сервер), не клиенту. Аналогично, команда, указанная параметром PROGRAM, выполняется непосредственно сервером, а не клиентским приложением, и должна быть доступна на выполнение пользователю PostgreSQL. Выполнять команду COPY с файлом (или командой) разрешено только суперпользователям базы данных, так как она позволяет прочитать и записать любой файл, к которому имеет доступ сервер.

Не путайте команду COPY с реализованной в psql метакомандой \copy. Метакоманда \copy вызывает COPY FROM STDIN или COPY TO STDOUT, а затем работает с данными в файле, доступном клиенту psql. Таким образом, когда применяется команда \copy, доступность файла и права доступа зависят от клиента, а не от сервера.

Путь файла, указываемый в COPY, рекомендуется всегда задавать как абсолютный, а не относительный. Это обязательное условие для команды COPY TO, но COPY FROM позволяет прочитать файл, заданный и относительным путём. Такой путь будет интерпретироваться относительно рабочего каталога серверного процесса (обычно это каталог данных кластера), а не рабочего каталога клиента.

Выполнение команды в PROGRAM может быть ограничено и другими работающими в ОС механизмами контроля доступа, например SELinux.

COPY FROM вызывает все триггеры и обрабатывает все ограничения-проверки в целевой таблице. Однако правила при загрузке данных не вызываются.

При вводе и выводе данных COPY учитывается DateStyle. Для обеспечения переносимости на другие инсталляции PostgreSQL, в которых могут использоваться нестандартные значения DateStyle, значение DateStyle следует установить равным ISO до вызова COPY TO. Также рекомендуется не выгружать данные с IntervalStyle равным sql_standard, так как сервер с другим значением IntervalStyle может неправильно воспринимать отрицательные интервалы в таких данных.

Входные данные интерпретируются согласно кодировке, заданной параметром ENCODING, или текущей кодировке клиента, а выходные кодируются в кодировке ENCODING или текущей кодировке клиента, даже если данные не проходят через клиента, а считываются или записываются в файл непосредственно сервером.

COPY прекращает операцию при первой ошибке. Это не должно приводить к проблемам в случае с COPY TO, но после COPY FROM в целевой таблице остаются ранее полученные строки. Эти строки не будут видимыми и доступными, но будут занимать место на диске. Если сбой происходит при копировании большого объёма данных, это может приводить к значительным потерям дискового пространства. При желании вернуть потерянный объём, это можно сделать с помощью команды VACUUM.

FORCE_NULL и FORCE_NOT_NULL можно применить одновременно к одной колонке. В результате NULL-значения в кавычках будут преобразованы в NULL, а NULL-значения без кавычек — в пустые строки.

Форматы файлов

Примеры

В следующем примере таблица передаётся клиенту с разделителем полей «вертикальная черта» (|):

COPY country TO STDOUT (DELIMITER '|');

Копирование данных из файла в таблицу country:

COPY country FROM '/usr1/proj/bray/sql/country_data';

Копирование в файл только данных стран, название которых начинается с 'A':

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO '/usr1/proj/bray/sql/a_list_countries.copy';

Для копирования данных в сжатый файл можно направить вывод через внешнюю программу сжатия:

COPY country TO PROGRAM 'gzip > /usr1/proj/bray/sql/country_data.gz';

Пример данных, подходящих для копирования в таблицу из STDIN:

AF      AFGHANISTAN
AL      ALBANIA
DZ      ALGERIA
ZM      ZAMBIA
ZW      ZIMBABWE

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

Ниже приведены те же данные, но выведенные в двоичном формате. Данные показаны после обработки Unix-утилитой od -c. Таблица содержит три колонки; первая имеет тип char(2), вторая — text, а третья — integer. Последняя колонка во всех строках содержит NULL.

0000000   P   G   C   O   P   Y  \n 377  \r  \n  \0  \0  \0  \0  \0  \0
0000020  \0  \0  \0  \0 003  \0  \0  \0 002   A   F  \0  \0  \0 013   A
0000040   F   G   H   A   N   I   S   T   A   N 377 377 377 377  \0 003
0000060  \0  \0  \0 002   A   L  \0  \0  \0 007   A   L   B   A   N   I
0000100   A 377 377 377 377  \0 003  \0  \0  \0 002   D   Z  \0  \0  \0
0000120 007   A   L   G   E   R   I   A 377 377 377 377  \0 003  \0  \0
0000140  \0 002   Z   M  \0  \0  \0 006   Z   A   M   B   I   A 377 377
0000160 377 377  \0 003  \0  \0  \0 002   Z   W  \0  \0  \0  \b   Z   I
0000200   M   B   A   B   W   E 377 377 377 377 377 377

Совместимость

Оператор COPY отсутствует в стандарте SQL.

До версии PostgreSQL 9.0 использовался и по-прежнему поддерживается следующий синтаксис:

COPY имя_таблицы [ ( имя_колонки [, ...] ) ]
    FROM { 'имя_файла' | STDIN }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'разделитель' ]
          [ NULL [ AS ] 'маркер_null' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'кавычки' ]
                [ ESCAPE [ AS ] 'спецсимвол' ]
                [ FORCE NOT NULL имя_колонки [, ...] ] ] ]

COPY { имя_таблицы [ ( имя_колонки [, ...] ) ] | ( запрос ) }
    TO { 'имя_файла' | STDOUT }
    [ [ WITH ]
          [ BINARY ]
          [ OIDS ]
          [ DELIMITER [ AS ] 'разделитель' ]
          [ NULL [ AS ] 'маркер_null' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'кавычки' ]
                [ ESCAPE [ AS ] 'спецсимвол' ]
                [ FORCE QUOTE { имя_колонки [, ...] | * } ] ] ]

Заметьте, что в этом синтаксисе ключевые слова BINARY и CSV обрабатываются как независимые, а не как аргументы параметра FORMAT.

До версии PostgreSQL 7.3 использовался и по-прежнему поддерживается следующий синтаксис:

COPY [ BINARY ] table_name [ WITH OIDS ]
    FROM { 'имя_файла' | STDIN }
    [ [USING] DELIMITERS 'разделитель' ]
    [ WITH NULL AS 'маркер_null' ]

COPY [ BINARY ] имя_таблицы [ WITH OIDS ]
    TO { 'имя_файла' | STDOUT }
    [ [USING] DELIMITERS 'разделитель' ]
    [ WITH NULL AS 'маркер_null' ]