Описание

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

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

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

Параметры

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

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

COPY число

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

Примечания

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

COPY FROM можно применять с обычными, сторонними и секционированными таблицами или представлениями, в которых установлены триггеры INSTEAD OF INSERT.

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

Если для таблицы включена защита на уровне строк, соответствующие политики SELECT будут применяться и к операторам COPY таблица TO. Операторы COPY FROM для таблиц с защитой строк в настоящее время не поддерживаются. Вместо них следует использовать равнозначные операторы INSERT.

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

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

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

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

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

Для столбцов идентификации команда COPY FROM всегда переносит значения, содержащиеся во входных данных, как команда INSERT с указанием OVERRIDING SYSTEM VALUE.

При вводе и выводе данных 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 ]
          [ DELIMITER [ AS ] 'символ_разделитель' ]
          [ NULL [ AS ] 'маркер_NULL' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'символ_кавычек' ]
                [ ESCAPE [ AS ] 'символ_экранирования' ]
                [ FORCE NOT NULL имя_столбца [, ...] ] ] ]

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

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

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

COPY [ BINARY ] имя_таблицы
    FROM { 'имя_файла' | STDIN }
    [ [USING] DELIMITERS 'символ_разделитель' ]
    [ WITH NULL AS 'маркер_NULL' ]

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