COPY [ BINARY ] tabela [ WITH OIDS ] FROM { 'nome_do_arquivo' | stdin } [ [USING] DELIMITERS 'delimitador' ] [ WITH NULL AS 'cadeia de caracteres nula' ] COPY [ BINARY ] tabela [ WITH OIDS ] TO { 'nome_do_arquivo' | stdout } [ [USING] DELIMITERS 'delimitador' ] [ WITH NULL AS 'cadeia de caracteres nula' ]
Altera o comportamento da formatação dos campos, fazendo todos os dados serem escritos ou lidos no formato binário em vez de texto. As opções DELIMITERS e WITH NULL não são pertinentes para o formato binário.
O nome de uma tabela existente.
Especifica a cópia do identificador interno do objeto (OID) para cada linha.
O nome do arquivo de entrada ou de saída no Unix, junto com o caminho absoluto.
Especifica que a entrada vem do aplicativo cliente.
Especifica que a saída vai para o aplicativo cliente.
O caractere que separa os campos dentro de cada linha do arquivo.
A cadeia de caracteres que representa o valor nulo. O padrão é "\N" (contrabarra-N). Pode-se preferir uma cadeia de caracteres vazia, por exemplo.
Note: Durante a leitura, qualquer item de dado que corresponda a esta cadeia de caracteres é armazenado com o valor nulo, portanto deve-se ter certeza de estar usando para ler a mesma cadeia de caracteres que foi usada para escrever.
O comando COPY copia dados entre tabelas do PostgreSQL e arquivos do sistema operacional. O comando COPY TO copia todo o conteúdo de uma tabela para um arquivo, enquanto o COPY FROM copia os dados de um arquivo para uma tabela (adicionando os dados aos existentes na tabela).
O comando COPY com um nome de arquivo instrui o servidor PostgreSQL para ler ou escrever diretamente de um arquivo. O arquivo deve ser acessível ao servidor e o nome deve ser especificado a partir do ponto de vista do servidor. Quando stdin ou stdout for especificado, os dados fluirão entre o cliente e o servidor.
Tip: Não confunda o comando COPY com a instrução \copy do psql. O \copy executa COPY FROM stdin ou COPY TO stdout e, então, lê/grava os dados em um arquivo acessível ao cliente psql. Portanto, a acessibilidade e os direitos de acesso dependem do cliente e não do servidor, quando o \copy é utilizado.
O comando COPY só pode ser utilizado em tabelas, não pode ser utilizado em visões.
A palavra chave BINARY força todos os dados serem escritos/lidos no formato binário em vez de texto. É um pouco mais rápido do que a cópia normal, mas o arquivo binário produzido não é portável entre máquinas com arquiteturas diferentes.
Por padrão, a cópia no formato texto usa o caractere de tabulação ("\t") como delimitador de campos. O delimitador de campo pode ser mudado para qualquer outro caractere único usando a cláusula USING DELIMITERS. Os caracteres nos campos de dados que forem idênticos ao caractere delimitador serão precedidos por uma contrabarra.
É necessário possuir permissão de leitura em todas as tabelas cujos valores são lidos pelo COPY, e permissão para inserir ou atualizar em uma tabela onde valores são inseridos pelo COPY. O servidor também necessita de permissões apropriadas no Unix para todos os arquivos lidos ou escritos pelo COPY.
O comando COPY TO não invoca as regras nem atua no padrão da coluna. Invoca os gatilhos e as restrições de verificação.
O COPY pára de executar no primeiro erro, o que não deve acarretar problemas para o COPY FROM, mas a tabela de destino já vai ter recebido linhas no caso do COPY TO. Estas linhas não são visíveis nem acessíveis, mas ocupam espaço em disco, podendo ocasionar o desperdício de uma grande quantidade de espaço em disco se o erro ocorrer durante a cópia de uma grande quantidade de dados. Deve-se executar o comando VACUUM para recuperar o espaço desperdiçado.
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 poder ser lidos ou escritos pelo usuário do PostgreSQL (o ID do usuário sob o qual o servidor processa), e não pelo cliente. O COPY com 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.
Tip: A instrução \copy do psql lê e escreve arquivos na estação cliente usando as permissões do cliente, portanto não é restrita aos superusuários.
Recomenda-se que os nomes dos arquivos usados no comando COPY sejam sempre especificados como um caminho absoluto, sendo exigido pelo servidor no caso do COPY TO, mas para COPY FROM existe a opção de ler um arquivo especificado pelo caminho relativo. O caminho é interpretado como sendo relativo ao diretório de trabalho do servidor (algum lugar abaixo de $PGDATA), e não relativo ao diretório de trabalho do cliente.
Quando o comando COPY é utilizado sem a opção BINARY, o arquivo lido ou escrito é um arquivo de texto com uma linha para cada linha da tabela. As colunas (atributos) são separadas na linha pelo caractere delimitador. Os valores dos atributos são cadeias de caracteres geradas pela função de saída, ou aceitáveis pela função de entrada, para cada tipo de dado de atributo. A cadeia de caracteres nula especificada é utilizada no lugar dos atributos que são nulos.
Se WITH OIDS for especificado, o OID é lido ou escrito como a primeira coluna, precedendo as colunas de dado do usuário (Vai acontecer um erro se WITH OIDS for especificado para uma tabela que não possua OIDs).
O fim dos dados pode ser representado por uma única linha contendo apenas contrabarra-ponto (\.). Um marcador de fim de dados não é necessário ao se ler de um arquivo Unix, porque o fim de arquivo serve perfeitamente bem; mas um marcador de fim dos dados deve ser fornecido para copiar os dados de ou para um aplicativo cliente.
O caractere contrabarra (\) pode ser usado nos dados do comando COPY para evitar que caracteres dos dados sejam interpretados como delimitadores de linha ou de coluna. Em particular, os seguintes caracteres devem ser precedidos por uma contrabarra se aparecerem como parte do valor de um atributo: a própria contrabarra, a nova-linha e o caractere delimitador corrente.
As seguintes seqüências especiais de contrabarra são reconhecidas pelo comando COPY FROM:
Seqüência | Representa |
---|---|
\b | Backspace (ASCII 8) |
\f | Avanço de página (ASCII 12) |
\n | Nova-linha (ASCII 10) |
\r | Retorno do carro (ASCII 13) |
\t | Tabulação (ASCII 9) |
\v | Tabulação Vertical (ASCII 11) |
\dígitos | A contrabarra seguida por um a três dígitos octais especifica o caractere com este código numérico |
Nunca coloque uma contrabarra antes dos caracteres de dado N ou ponto (.), senão os dois serão interpretados, erroneamente, como a cadeia de caracteres nula padrão e o marcador de fim dos dados, respectivamente. Qualquer outro caractere não mencionado na tabela acima é interpretado como representando a si próprio.
É fortemente recomendado que os aplicativos que geram dados para o COPY convertam os caracteres de nova-linha e de retorno-de-carro presentes nos dados em seqüências \n e \r respectivamente. No presente momento (PostgreSQL 7.2 e versões mais antigas) é possível se representar um retorno-de-carro nos dados sem nenhuma seqüência especial, e representar a nova-linha nos dados por uma contrabarra seguida por um caractere de nova-linha. Entretanto, estas representações não serão aceitas por padrão nas versões futuras.
Observe que o fim de cada linha é marcado por um caractere de nova-linha no estilo Unix ("\n"). Atualmente, o COPY FROM não se comporta de forma adequada quando o arquivo especificado contém o fim de linha no estilo MS-DOS ou Mac. Espera-se que isto mude em versões futuras.
O formato do arquivo usado pelo COPY BINARY mudou no PostgreSQL v7.1. O novo formato do arquivo consiste em uma cabeçalho, zero ou mais tuplas e um rodapé.
O cabeçalho do arquivo consiste de 24 bytes para campos fixos, seguidos por uma área de extensão do cabeçalho de tamanho variável. Os campos fixos são:
A seqüência de 12 bytes PGBCOPY\n\377\r\n\0 --- observe que o nulo é uma parte requerida da assinatura (A assinatura foi projetada para permitir a fácil identificação de arquivos corrompidos por uma transferência não apropriada. Esta assinatura é modificada por filtros de tradução de nova-linha, nulos suprimidos, bits altos suprimidos, ou mudanças de paridade).
Constante int32 0x01020304 na ordem dos bytes de origem. Se uma ordem errada dos bytes for detectada aqui, o leitor poderá se envolver em uma mudança na ordem dos bytes dos campos posteriores.
Máscara de bits int32 usada para caracterizar aspectos importantes do formato do arquivo. Os bits são numerados de 0 (LSB) até 31 (MSB) --- observe que este campo é armazenado na ordem dos bytes da plataforma de origem, assim como todos os campos inteiro seguintes. Os bits 16-31 são reservados para caracterizar questões críticas do formato do arquivo; o leitor deve abortar se for encontrado um bit não esperado neste intervalo. Os bits 0-15 são reservados para sinalizar questões de formato precedente-compatíveis; o leitor deve simplesmente ignorar qualquer bit não esperado neste intervalo. Atualmente somente um bit sinalizador está definido, os outros devem ser zero:
Se for igual a 1 os OIDs estão incluídos no arquivo; 0 caso contrário.
Valor int32 do comprimento em bytes do restante do cabeçalho, não se incluindo. Na versão inicial será zero, com a primeira tupla vindo a seguir. Mudanças futuras no formato podem permitir a presença de dados adicionais no cabeçalho. O leitor deve simplesmente desprezar qualquer dado da área de extensão do cabeçalho que não souber o que fazer com a mesma.
A área de extensão do cabeçalho é imaginada como contendo uma seqüência de blocos auto-identificantes. O campo de sinalizadores não tem por finalidade informar os leitores o que existe na área de extensão. O projeto específico do conteúdo da área de extensão do cabeçalho foi deixado para uma versão futura.
Este projeto permite tanto adições de cabeçalho precedente-compatíveis (adicionar blocos de extensão de cabeçalho, ou sinalizar com bits de baixa-ordem) e mudanças não precedente-compatíveis (usar bits de alta-ordem para sinalizar estas mudanças, e adicionar dados de apoio para a área de extensão se for necessário).
Cada tupla começa com um contador (int16) do número de campos na tupla (atualmente todas as tuplas da tabela possuem o mesmo contador, mas isto pode não ser verdade para sempre). Então, repetido para cada campo da tupla, existe uma palavra tipo-comprimento (typlen, int16) possivelmente seguida por um campo de dados. O campo tipo-comprimento é interpretado como:
O campo é nulo. Nenhum dado segue.
O campo possui um tipo de dado de comprimento fixo. Exatamente N bytes de dados seguem a palavra tipo-comprimento (typlen).
O campo possui um tipo de dado de comprimento variável (varlena). Os próximos quatro bytes são o cabeçalho, que contém o valor do comprimento total incluindo a si próprio.
Reservado para uso futuro.
Para campos não nulos, o leitor pode verificar se o tipo-comprimento (typlen) corresponde ao tipo-comprimento esperado para a coluna de destino, fornecendo uma maneira simples, mas muito útil, de verificar se o dado está como o esperado.
Não existe nenhum preenchimento de alinhamento ou qualquer dado extra entre os campos. Também observe que o formato não distingue se um tipo de dado é passado por referência ou passado por valor. Estas duas disposições são deliberadas: elas ajudam a melhorar a portabilidade dos arquivos (embora problemas relacionados com a ordem dos bytes ou o formato do ponto flutuante podem impedir a troca de arquivos binários entre computadores).
Se os OIDs são incluídos no arquivo, o campo OID segue imediatamente a palavra contador-de-campos. É um campo normal, exceto que não está incluído no contador-de-campos. Em particular possui um tipo-comprimento --- isto permite tratar OIDs de 4 bytes versus 8 bytes sem muita dificuldade, e permite os OIDs serem mostrados como NULL se por acaso for desejado.
O rodapé do arquivo consiste em uma palavra int16 contendo -1, sendo facilmente distinguível da palavra contador-de-campos da tupla.
O leitor deve relatar um erro se a palavra contador-de-campos não for -1 nem o número esperado de colunas, fornecendo uma verificação extra com relação a perda de sincronização com os dados.
O exemplo a seguir copia uma tabela para a saída padrão, usando a barra vertical (|) como delimitador de campo:
COPY paises TO stdout USING DELIMITERS '|';
Para copiar dados de um arquivo Unix para a tabela paises:
COPY paises FROM '/usr1/proj/bray/sql/paises.txt';
Abaixo está um exemplo de dados apropriados para se copiar para a tabela a partir de stdin (por isso possui a seqüência de terminação na última linha):
AF AFGHANISTAN AL ALBANIA DZ ALGERIA ZM ZAMBIA ZW ZIMBABWE \.
Observe que os espaços em branco em cada linha são, na verdade, o caractere de tabulação.
Abaixo são os mesmos dados, escritos no formato binário em uma máquina Linux/i586. Os dados mostrados foram filtrados através do utilitário do Unix od -c. A tabela possui três campos: o primeiro é char(2); o segundo é text; o terceiro é integer. Todas as linhas possuem um valor nulo no terceiro campo.
0000000 P G B C O P Y \n 377 \r \n \0 004 003 002 001 0000020 \0 \0 \0 \0 \0 \0 \0 \0 003 \0 377 377 006 \0 \0 \0 0000040 A F 377 377 017 \0 \0 \0 A F G H A N I S 0000060 T A N \0 \0 003 \0 377 377 006 \0 \0 \0 A L 377 0000100 377 \v \0 \0 \0 A L B A N I A \0 \0 003 \0 0000120 377 377 006 \0 \0 \0 D Z 377 377 \v \0 \0 \0 A L 0000140 G E R I A \0 \0 003 \0 377 377 006 \0 \0 \0 Z 0000160 M 377 377 \n \0 \0 \0 Z A M B I A \0 \0 003 0000200 \0 377 377 006 \0 \0 \0 Z W 377 377 \f \0 \0 \0 Z 0000220 I M B A B W E \0 \0 377 377