COPY

Descrição

O comando COPY copia dados entre tabelas do PostgreSQL e arquivos do sistema operacional. O comando COPY TO copia o conteúdo de uma tabela para um arquivo, enquanto o comando COPY FROM copia dados de um arquivo para uma tabela (adicionando os dados aos já existentes na tabela).

Se for especificada uma lista de colunas, o comando COPY somente copia os dados das colunas especificadas de/para o arquivo. Havendo colunas na tabela que não estejam na lista de colunas, o comando COPY FROM insere o valor padrão destas colunas.

O comando COPY com um nome de arquivo instrui o servidor PostgreSQL a ler ou escrever diretamente no arquivo. O arquivo deve ser acessível ao servidor, e o nome deve ser especificado sob o ponto de vista do servidor. Quando é especificado STDIN ou STDOUT, os dados são transmitidos através da conexão entre o cliente e o servidor.

Parâmetros

Observações

O comando COPY só pode ser utilizado em tabelas, não podendo ser utilizado em visões.

A palavra chave BINARY faz todos os dados serem armazenados/lidos no formato binário em vez de texto. É um pouco mais rápido que o modo texto normal, mas o arquivo no formato binário é menos portável entre arquiteturas de máquinas e versões do PostgreSQL.

É necessário possuir o privilégio de seleção na tabela cujos valores são lidos pelo COPY TO, e o privilégio inserção na tabela onde os valores são inseridos pelo COPY FROM.

Os arquivos declarados no comando COPY são lidos ou escritos diretamente pelo servidor, e não pelo aplicativo cliente. Portanto, devem residir ou serem acessíveis pela máquina servidora de banco de dados, e não pela estação cliente. Os arquivos devem ser acessíveis e poderem ser lidos ou escritos pelo usuário do PostgreSQL (o ID do usuário sob o qual o servidor executa), e não pelo cliente. O COPY especificando um nome de arquivo só é permitido aos superusuários do banco de dados, porque permite ler e escrever em qualquer arquivo que o servidor possua privilégio de acesso.

Não deve ser confundido o comando COPY com a instrução \copy do psql. A instrução \copy executa COPY FROM STDIN ou COPY TO STDOUT e, portanto, lê/grava os dados em um arquivo acessível ao cliente psql. Por esta razão, quando se utiliza \copy a acessibilidade e os direitos de acesso ao arquivo dependem do cliente, e não do servidor.

Recomenda-se que o nome do arquivo utilizado no comando COPY seja sempre especificado como um caminho absoluto, o que é exigido pelo servidor no caso do COPY TO, mas para o COPY FROM existe a opção de ler um arquivo especificado pelo caminho relativo. O caminho é interpretado com relação ao diretório de trabalho do processo servidor (normalmente o diretório de dados do agrupamento), e não o diretório de trabalho do cliente.

O comando COPY FROM chama os gatilhos e as restrições de verificação da tabela de destino. Entretanto, não chama as regras.

A entrada e a saída do COPY são afetadas por DateStyle. Para garantir a portabilidade com outras instalações do PostgreSQL, que podem utilizar definições para DateStyle diferentes do padrão, deve ser definido DateStyle como ISO antes de usar COPY TO.

O COPY pára de executar no primeiro erro, o que não deve causar problemas no caso do COPY TO, mas a tabela de destino já terá recebido as linhas anteriores no caso do COPY FROM. Estas linhas não são visíveis nem acessíveis, mas ainda assim ocupam espaço em disco, podendo causar o desperdício de uma quantidade considerável de espaço em disco se o erro ocorrer durante a cópia de uma grande quantidade de dados. Deve ser executado o comando VACUUM para recuperar o espaço desperdiçado.

Formatos dos arquivos

Exemplos

O exemplo a seguir copia uma tabela para o cliente utilizando a barra vertical (|) como delimitador de campo:

COPY paises TO STDOUT WITH DELIMITER '|';

Para copiar os dados de um arquivo para a tabela paises:

COPY paises FROM '/usr1/proj/bray/sql/dados_dos_paises';

Abaixo estão mostrados dados adequados para serem copiados para uma tabela a partir da STDIN:

AF      AFGHANISTAN
AL      ALBANIA
DZ      ALGERIA
ZM      ZAMBIA
ZW      ZIMBABWE

Deve ser observado que em cada linha o espaço em branco é, na verdade, o caractere de tabulação.

Abaixo estão os mesmos dados escritos no formato binário. Os dados mostrados foram filtrados utilizando o utilitário do Unix od -c. A tabela possui três colunas: a primeira é do tipo char(2); a segunda é do tipo text; a terceira é do tipo integer. Todas as linhas possuem o valor nulo na terceira coluna.

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

No próximo exemplo [1] é utilizado o comando COPY, chamado a partir do psql, para copiar as últimas cinco linhas do arquivo /tmp/linhas.dat, que contém três colunas separadas por |, para a primeira, a segunda e a quarta colunas de uma tabela de quatro colunas. A tabela foi criada através do comando:

CREATE TABLE linhas (
coluna1 TEXT PRIMARY KEY,
coluna2 TEXT,
coluna3 TEXT,
coluna4 TEXT);

O conteúdo do arquivo carregado está mostrado abaixo:

# cat /tmp/linhas.dat
linha1a|linha1b|linha1d
linha2a|linha2b|linha2d
linha3a|linha3b|linha3d
linha4a|linha4b|linha4d
linha5a|linha5b|linha5d
linha6a|linha6b|linha6d
linha7a|linha7b|linha7d
linha8a|linha8b|linha8d

E o comando utilizado para copiar os dados do arquivo para a tabela foi:

tail --lines=5 /tmp/linhas.dat | psql -U teste teste -c \
"COPY linhas(coluna1,coluna2,coluna4) FROM STDIN WITH DELIMITER '|'"

Como resultado os seguintes dados foram incluídos na tabela:

SELECT * FROM linhas;

 coluna1 | coluna2 | coluna3 | coluna4
---------+---------+---------+---------
 linha4a | linha4b |         | linha4d
 linha5a | linha5b |         | linha5d
 linha6a | linha6b |         | linha6d
 linha7a | linha7b |         | linha7d
 linha8a | linha8b |         | linha8d
(5 linhas)

Compatibilidade

Não existe o comando COPY no padrão SQL.

A sintaxe mostrada abaixo era utilizada nas versões do PostgreSQL anteriores a 7.3, sendo ainda aceita:

COPY [ BINARY ] nome_da_tabela [ WITH OIDS ]
    FROM { 'nome_do_arquivo' | STDIN }
    [ [USING] DELIMITERS 'delimitador' ]
    [ WITH NULL AS 'cadeia_de_caracteres_nula' ]

COPY [ BINARY ] nome_da_tabela [ WITH OIDS ]
    TO { 'nome_do_arquivo' | STDOUT }
    [ [USING] DELIMITERS 'delimitador' ]
    [ WITH NULL AS 'cadeia_de_caracteres_nula' ]