Descrição

O comando COPY move dados entre as tabelas do PostgreSQL e arquivos do sistema de arquivos. 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 (anexando os dados aos que já se encontram na tabela). O comando COPY TO também pode copiar os resultados de uma consulta SELECT.

Se for especificada uma lista de colunas, o comando COPY TO irá copiar para o arquivo apenas os dados das colunas listadas. Para o comando COPY FROM, cada campo no arquivo é inserido, em ordem, na coluna especificada. As colunas da tabela não especificadas na lista de colunas do comando COPY FROM, recebem seus valores padrão.

O comando COPY com nome de arquivo instrui o servidor PostgreSQL a ler ou escrever diretamente no arquivo. O arquivo deve poder ser acessado pelo usuário do PostgreSQL (o ID de usuário com o qual o servidor executa), e o nome deve ser especificado do ponto de vista do servidor. Quando é especificado PROGRAM, o servidor executa o comando indicado, e lê a saída padrão do programa, ou escreve na entrada padrão do programa. O comando deve ser especificado do ponto de vista do servidor, e poder ser executado pelo usuário do PostgreSQL. Quando é especificado STDIN ou STDOUT, os dados são transmitidos através da conexão entre o cliente e o servidor.

Cada processo servidor executando o comando COPY relata seu progresso na visão pg_stat_progress_copy. Veja Relatório de progresso do COPY para obter detalhes.

Por padrão, o comando COPY irá falhar se encontrar um erro durante o processamento. Para casos de uso em que se deseja uma tentativa de carregar o arquivo inteiro da melhor maneira possível, pode ser usada a cláusula ON_ERROR para especificar algum outro comportamento.

Parâmetros

Saídas

Após a conclusão bem-sucedida, o comando COPY retorna uma etiqueta de comando no formato:

COPY contador

O contador é o número de linhas copiadas.

Notas

O comando COPY TO pode ser usado apenas com tabelas simples e visões materializadas preenchidas. Por exemplo, o comando COPY tabela TO copia as mesmas linhas que SELECT * FROM ONLY tabela. Entretanto, não oferece suporte direto a outros tipos de relações, como tabelas particionadas, tabelas filhas de herança ou visões. Para copiar todas as linhas dessas relações, deve ser usado COPY (SELECT * FROM tabela) TO.

O comando COPY FROM pode ser usado com tabelas simples, estrangeiras ou particionadas, ou com visões que possuem gatilhos INSTEAD OF INSERT.

É necessário possuir o privilégio de seleção na tabela cujos valores serão lidos pelo comando COPY TO, e o privilégio de inserção na tabela onde os valores serão inseridos pelo comando COPY FROM. É suficiente ter os privilégios de coluna na(s) coluna(s) listada(s) no comando.

Se estiver ativa a segurança no nível de linha para a tabela, as políticas relevantes para o comando SELECT serão aplicadas ao comando COPY tabela TO. No momento, não há suporte para o comando COPY FROM para tabelas com segurança no nível de linha. Em vez disso, devem ser usados comandos INSERT equivalentes.

Os arquivos especificados no comando COPY são lidos ou escritos diretamente pelo servidor, e não pela aplicação cliente. Portanto, os arquivos devem residir, ou serem acessíveis, ao servidor de banco de dados, e não ao cliente. Os arquivos devem ser acessíveis, e legíveis ou graváveis, pelo usuário do PostgreSQL (o ID de usuário com o qual o servidor é executado), e não pelo cliente. Da mesma forma, o comando especificado por PROGRAM é executado diretamente pelo servidor, e não pela aplicação cliente, devendo ser executável pelo usuário do servidor PostgreSQL. O comando COPY especificando um arquivo ou um comando só é permitido para os superusuários do banco de dados, ou usuários que receberam uma das funções de banco de dados (roles) pg_read_server_files, pg_write_server_files, ou pg_execute_server_program, porque permite ler ou escrever qualquer arquivo, ou executar um programa, que o servidor tenha privilégios de acesso.

Não se deve confundir o comando COPY com o meta-comando \copy do psql. O meta-comando \copy chama COPY FROM STDIN ou COPY TO STDOUT, e então busca/armazena os dados em um arquivo acessível ao cliente psql. Portanto, a acessibilidade do arquivo e os direitos de acesso dependem do cliente e não do servidor, quando é usado \copy.

Recomenda-se que o nome do arquivo usado no comando COPY seja sempre especificado como um caminho absoluto. Isto é imposto pelo servidor no caso de COPY TO, mas para COPY FROM tem-se a opção de ler de um arquivo especificado por um caminho relativo. O caminho será interpretado em relação ao diretório de trabalho do processo do servidor (normalmente o diretório de dados da instância), e não ao diretório de trabalho do cliente.

A execução de um comando por PROGRAM pode ser restringida pelos mecanismos de controle de acesso do sistema operacional, como o SELinux.

O comando COPY FROM dispara quaisquer gatilhos, e verifica as restrições na tabela de destino. No entanto, não chama as regras.

Para colunas de identidade, o comando COPY FROM sempre escreve os valores da coluna fornecidos nos dados de entrada, como a opção OVERRIDING SYSTEM VALUE do comando INSERT .

A entrada e a saída do comando COPY são afetadas pelo parâmetro DateStyle. Para garantir a portabilidade para outras instalações do PostgreSQL que podem usar configurações não padrão do parâmetro DateStyle, o parâmetro DateStyle deve ser definido como ISO antes de executar o comando COPY TO. Também é uma boa ideia evitar escrever os dados com o parâmetro IntervalStyle definido como sql_standard, porque os valores de intervalo negativo podem ser mal interpretados por um servidor que tenha uma configuração diferente para IntervalStyle.

Os dados de entrada são interpretados segundo a opção ENCODING, ou a codificação corrente do cliente, e os dados de saída são codificados usando ENCODING, ou a codificação corrente do cliente, mesmo que os dados não passem pelo cliente, mas sejam lidos ou escritos em arquivo diretamente pelo servidor.

O comando COPY FROM insere fisicamente as linhas de entrada na tabela à medida que avança. Se o comando falhar, estas linhas permanecerão no estado de excluída; estas linhas não ficarão visíveis, mas ainda ocuparão espaço em disco. Isto poderá representar um desperdício considerável de espaço em disco se a falha ocorrer durante uma operação de cópia volumosa. Deve ser usado o comando VACUUM para recuperar o espaço desperdiçado.

FORCE_NULL e FORCE_NOT_NULL podem ser usados ​​simultaneamente na mesma coluna. Isto resulta na conversão de cadeias de caracteres nulas delimitadas em valores nulos, e cadeias de caracteres nulas não delimitadas em cadeias de caracteres vazias.

Formatos de arquivo

Exemplos

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

COPY country
    TO STDOUT (DELIMITER '|');

Para copiar dados de um arquivo para a tabela country:

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

Para copiar para o arquivo apenas os países cujos nomes começam com 'A':

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

Para copiar em um arquivo comprimido, pode-se canalizar a saída por meio de um programa de compressão externo:

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

Aqui está uma amostra de dados adequada para copiar de STDIN para uma tabela:

AF      AFGHANISTAN
AL      ALBANIA
DZ      ALGERIA
ZM      ZAMBIA
ZW      ZIMBABWE

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

A seguir estão os mesmos dados, produzidos em formato binário. Os dados são mostrados após a filtragem através do utilitário do Unix od -c. A tabela tem três colunas; a primeira tem o tipo char(2), a segunda tem o tipo text, e a terceira tem o tipo integer. Todas as linhas têm um 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

Conformidade

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

A seguinte sintaxe era usada antes do PostgreSQL versão 9.0, e ainda tem suporte:

COPY nome_da_tabela [ ( nome_da_coluna [, ...] ) ]
    FROM { 'nome_do_arquivo' | STDIN }
    [ [ WITH ]
          [ BINARY ]
          [ DELIMITER [ AS ] 'caractere_delimitador' ]
          [ NULL [ AS ] 'cadeia_de_caracteres_nula' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'caractere_demarcador_de_campo' ]
                [ ESCAPE [ AS ] 'caractere_de_escape' ]
                [ FORCE NOT NULL nome_da_coluna [, ...] ] ] ]

COPY { nome_da_tabela [ ( nome_da_coluna [, ...] ) ] | ( consulta ) }
    TO { 'nome_do_arquivo' | STDOUT }
    [ [ WITH ]
          [ BINARY ]
          [ DELIMITER [ AS ] 'caractere_delimitador' ]
          [ NULL [ AS ] 'cadeia_de_caracteres_nula' ]
          [ CSV [ HEADER ]
                [ QUOTE [ AS ] 'caractere_demarcador_de_campo' ]
                [ ESCAPE [ AS ] 'caractere_de_escape' ]
                [ FORCE QUOTE { nome_da_coluna [, ...] | * } ] ] ]

Note que nesta sintaxe, BINARY e CSV são tratados como palavras-chave independentes, e não como argumentos da opção FORMAT.

A seguinte sintaxe era usada antes do PostgreSQL versão 7.3, e ainda tem suporte:

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

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

Veja também