Introdução a COPY
No PostgreSQL, o comando SQL COPY é utilizado para fazer duplicações de tabelas, registos e outros objectos, mas também é útil para transferir dados de um formato para outro. Por exemplo, o comando COPY pode ser utilizado para inserir dados CSV numa tabela como registos PostgreSQL. Este artigo fornecerá vários exemplos de COPY do PostgreSQL que ilustram como utilizar este comando como parte da administração da sua base de dados.
Declaração COPY do PostgreSQL
O comando SQL COPY é semelhante ao comando pg_dump e pg_restore comandos que são utilizados para fazer o backup e restaurar tabelas e bases de dados. Pode utilizar o comando COPY para mover ficheiros dentro de uma tabela PostgreSQL ou para colocar os dados de uma tabela PostgreSQL num ficheiro.
Prerequisites
Antes de começarmos a ver os nossos exemplos de CÓPIO do PostgreSQL, teremos de rever alguns dos pré-requisitos para este tutorial:
- li>Primeiro, terá de ter o PostgreSQL instalado e a funcionar na sua máquina. Se estiver a utilizar Linux, tente o comando
service postgresql status num terminal para ver se o PostgreSQL está instalado e a correr. Pode então usar CTRL + C para sair do processo. Em macOS ou Linux, também pode canalizar o comando grep através de ps para procurar qualquer processo PostgreSQL actualmente em execução:|
1
|
ps aux | grep postgres
|
Também pode usar psql -V para confirmar que o comando psql…A interface de linha está instalada e a funcionar; este comando devolve a versão do utilitário e a informação de instalação.
Deverá ver a saída que se parece com o seguinte:
|
1
|
psql (PostgreSQL) 11.5
|
Conectar à interface de linha de comando psql para PostgreSQL
Após todos os pré-requisitos estarem em vigor, pode tentar ligar-se à interface psql. Use o sudo su - postgres num terminal seguido de psql. O comando psql --help pode ser utilizado para obter mais informações sobre a ligação a uma base de dados PostgreSQL com um utilizador.
Criar uma base de dados PostgreSQL
Aqui está o comando que usaremos na interface psql para criar uma base de dados PostgreSQL:
|
1
|
CRIAR BASE DE DADOS somedb;
|
Agora podemos ligar-nos à nossa base de dados usando o comando \c para que possamos começar a introduzir consultas.
Criar uma tabela PostgreSQL
Next, vamos criar uma tabela para usar nos nossos exemplos:
|
1
2 3 4 5 |
Exemplos de cópia PostgreSQL
Neste ponto, estamos prontos para mergulhar em alguns exemplos de como podemos usar o comando SQL COPY para duplicar ou transferir dados.
Copiar dados de ficheiro de texto em Postgres
A TEXT formato de ficheiro consiste em linhas constituídas por colunas separadas por um caracter delimitador; os dados são então escritos ou lidos como um ficheiro de texto com uma linha de uma tabela por linha.
|
1
2 |
COPY TABLE_NAME (col, col2)
FROM / TO ‘filename’ DELIMITER ‘ ‘; |
Podemos usar o comando PostgreSQL COPY para exportar um ficheiro CSV para a nossa tabela de demonstração com um formato de ficheiro TEXT:
|
1
2 |
COPY demo(primeiro nome, último nome) TO
‘demo.txt’ DELIMITER ‘ ‘ ‘; |
Copiar dados binários em Postgres
A BINARY formato de ficheiro é identificado como um ficheiro ‘não-texto’ uma vez que todos os dados são armazenados ou lidos como formato binário. Isto pode ser mais rápido que o formato de texto e csv.
P>Tentemos usar o comando COPY para exportar um único registo de uma tabela num ficheiro binário. Aqui está a sintaxe básica:
|
1
2 3 4 5 6 |
Para o nosso exemplo, o comando terá este aspecto:
|
1
2 3 4 5 |
Inserir dados CSV no PostgreSQL usando COPY
Agora vimos alguns exemplos de exportação de dados, vamos tentar usar COPY para inserir dados de linha CSV numa tabela PostgreSQL. Vamos usar as duas linhas seguintes de dados de teste CSV para ilustrar como se pode usar COPY para inserir dados em Postgres a partir de um ficheiro CSV:
|
1
2 3 |
id, str_col, int_col, bool_col
1, “Sou de um ficheiro CSV”, 112233, verdadeiro 2, “olá, Postgres”, 8765, false |
Terminologia CSV
Quando discutimos ficheiros CSV, há um par de termos-chave a entender:
-
DELIMITER– Um delimitador é um caracter que separa cada linha do ficheiro em colunas; num ficheiro CSV, o delimitador é uma vírgula. Os delimitadores não podem ser utilizados em formato binário. -
HEADER– Quando um ficheiro CSV é criado, a linha do cabeçalho é a primeira linha do ficheiro que contém os nomes das colunas.
Usa as palavras-chave COPY e FROM para inserir dados Postgres de um ficheiro
A seguinte instrução SQL irá inserir linhas de dados CSV numa tabela chamada some_table:
|
1
2 3 |
NOTE: Certifique-se de passar o caminho absoluto para o ficheiro como uma string no seu FROM cláusula para evitar quaisquer erros.
Utilizar COPY para cortar espaço em branco da tabela de dados PostgreSQL
Um pequeno problema com o uso de COPY para inserir dados CSV é que resultará em espaço em branco de chumbo e de rasto na nossa coluna de string. Felizmente, existe uma solução simples para este problema. Vamos tentar inserir novamente os nossos dados CSV, e desta vez vamos usar uma tabela temporária para armazenar os dados. Usaremos então a função SQL LTRIM para retirar o espaço em branco da nossa coluna de string antes de o inserirmos na nossa tabela permanente PostgreSQL.
NOTE: Certifique-se de truncar a tabela de teste digitando TRUNCATE some_table; em psql antes de reinserir os dados CSV de modo a evitar qualquer erro de chave duplicada.
Criar uma tabela temporária para armazenar os dados CSV
A seguinte instrução SQL irá criar uma tabela temporária para os nossos dados CSV:
|
1
2 |
CREATE TEMP TABLE temp_tbl AS
SELECT * FROM some_table LIMIT 0; |
Este comando SQL retornará SELECT 0 se bem sucedido.
Agora, vamos tentar o comando COPY novamente, excepto desta vez vamos copiar as linhas do CSV para a tabela temporária:
|
1
2 3 |
A instrução SQL mostrada acima deve retornar COPY seguido pelo número de linhas CSV que copiou.
Utilizar LTRIM() para retirar os espaços em branco dos dados da tabela PostgreSQL
O nosso próximo exemplo irá inserir os dados da tabela temporária na tabela permanente. Quando executamos esta INSERT declaração, devemos especificar explicitamente cada cabeçalho de coluna e certificarmo-nos de passar a coluna de string para a função LTRIM(), a fim de retirar os espaços em branco:
|
1
2 3 4 |
>div> INSERIR EM alguma_tabela (id, str_col, int_col, bool_col) SELECT id, LTRIM(str_col), int_col, bool_col FROM temp_tbl; |
Após executar este comando INSERT, os registos devem ser copiados da tabela temporária para o nosso some_table tabela PostgreSQL. Agora vamos utilizar o comando SELECT * FROM some_table para verificar se os dados do CSV foram inseridos sem os espaços em branco de entrada e de saída.
O comando mostrado abaixo só é necessário se necessitar de libertar memória ou espaço a curto prazo; caso contrário, a tabela temporária será automaticamente descartada quando terminar a sua sessão psql:
|
1
|
DROP TABLE temp_tbl;
|
Conclusion
Não há dúvida que o comando SQL COPY pode ser utilizado para uma variedade de tarefas de administração de bases de dados. Não só pode usar o comando para duplicar tabelas e registos, mas também pode usar COPY para transferir dados de um formato para outro. Neste artigo, mostramos-lhe exemplos do PostgreSQL COPY que ilustram como importar e exportar dados entre um ficheiro CSV e uma tabela PostgreSQL. Com estes exemplos para começar, poderá aproveitar o poder do comando COPY na sua própria implementação do PostgreSQL.
0 comentários