O Comando COPY

Este comando realiza a cópia de dados entre tabelas e arquivos. É muito utilizada para fazer cargas de dados e implementar rotinas de backup e migrações de bancos de dados. Não faz parte da especificação tradicional do SQL.

A sintaxe com a cláusula TO extrai de uma tabela seus dados e os armazena em um arquivo texto. A sintaxe com FROM lê um arquivo texto, binário ou CSV (texto separado por vírgulas) para uma tabela.

O COPY pode ser empregado um comando Select ou cláusula Values para ganho de maior controle sobre os dados gravados/ lidos.

Adicionalmente pode substituir delimitadores nos arquivos gerados e definir caracteres que substituam valores nulos.

Sintaxe:

COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]

COPY { tablename [ ( column [, ...] ) ] | ( query ) }
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ HEADER ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ HEADER ]
[ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

Exemplos de COPY de tabela para arquivo:

1 - Sintaxe que lê dados da tabela tstdel e os grava no arquivo teste.txt. Por padrão, o arquivo é criado em modo texto e a separação dos dados no arquivo gerado é feita por tabulações. Valores nulos são gravados como o caracter '\N'.

COPY tstdel TO 'c://teste.txt';

Arquivo gerado:
1 Teste1 2007-07-26 14:35:14
2 Teste2 2007-07-26 14:35:14
3 Teste3 2007-07-26 14:35:14
4 Teste4 2007-07-26 14:35:14
5 Teste5 2007-07-26 14:35:14
6 \N \N

2 - Usando o caracter '|' para separar os campos do arquivo gerado. A cláusula WITH é opcional.

COPY tstdel TO 'c://teste.txt' WITH DELIMITER '|';

Arquivo gerado:

1|Teste1|2007-07-26 14:35:14
2|Teste2|2007-07-26 14:35:14
3|Teste3|2007-07-26 14:35:14
4|Teste4|2007-07-26 14:35:14
5|Teste5|2007-07-26 14:35:14
6|\N|\N


3 - COPY gerando arquivo binário. O arquivo é criado mais rapidamente que em modo texto, mas a portabilidade pode ser menor entre versões do PostgreSQL ou em relação a outros bancos.

COPY tstdel TO 'c://teste.txt' BINARY;

4- A geração de arquivo pode causar problemas com valores nulos. Esta sintaxe troca valores nulos por um caracter "*".

COPY tstdel TO 'c://teste.txt' NULL '*';

5 - O COPY permite a escolha de um conjunto de colunas. Exemplo de cópia das colunas cod e nome.

COPY tstdel (cod, nome) TO 'c://teste.txt';

6 - Uso de select para definir os conjuntos de dados a serem copiados.

COPY (select * from tstdel where cod <= 500) TO 'c://teste.txt';

7 - Geração de arquivos em formato CSV.

COPY tstdel TO 'c://teste.txt' CSV;

8 - Geração de arquivos em formato CSV com HEADER, um cabeçalho com o nome das colunas.

COPY tstdel TO 'c://teste.txt' CSV HEADER;

Arquivo gerado:

cod,nome,date
1,Teste1,2007-07-26 14:35:14
2,Teste2,2007-07-26 14:35:14
3,Teste3,2007-07-26 14:35:14
4,Teste4,2007-07-26 14:35:14
5,Teste5,2007-07-26 14:35:14
6,,

Exemplos de COPY de arquivo (criado com COPY FROM) para tabela:

1 - Sintaxe que lê o arquivo teste.xtx e grava seus dados na tabela tstdel. Por padrão é feita a cópia de arquivo texto com um registro por linha, e campos separados por tabulações.

COPY tstdel FROM 'c://teste.txt';

2 - Sintaxe que lê e recupera um arquivo binário. Caso o arquivo lido não seja no formato esperado, é emitida mensagem de erro.

COPY tstdel FROM 'c://teste.txt' BINARY;

3 - Sintaxe que lê e recupera um arquivo de dados, trocando os valores '*' por nulos.

COPY tstdel FROM 'c://teste.txt' NULL '*';

4 - Exemplo que recupera apenas as colunas cod e nome.

COPY tst2 (cod, nome) FROM 'c://teste.txt';

5 - Definindo conjuntos de dados a serem copiados através de select. Uma cláusula VALUES também poderia ser empregada.

COPY (select * from tstdel where cod <= 500) FROM 'c://teste.txt';

6 - COPY Lendo arquivos CSV.

COPY tstdel FROM 'c://teste.txt' CSV;

7 - Lendo arquivos CSV com HEADER.

COPY tstdel FROM 'c://teste.txt' CSV HEADER;