Como exportar dados do PostgreSQL para um arquivo CSV

Você precisa enviar dados rapidamente para um cliente ou compartilhar um relatório para análise posterior? Um arquivo CSV é uma ótima opção de compartilhamento! Vamos dar uma olhada em como você pode usar esse formato para exportar dados de um banco de dados PostgreSQL.

Neste artigo, primeiro analisaremos o que é o formato de arquivo CSV e por que ele é útil ao exportar seu banco de dados PostgreSQL. Em seguida, exportaremos alguns dados de exemplo de um banco de dados real usando o psql na linha de comando e o pgAdmin, uma interface gratuita e de código aberto para bancos de dados PostgreSQL.

Antes de começarmos, certifique-se de ter configurado seu banco de dados PostgreSQL. Se você ainda não trabalhou com o PostgreSQL, confira nosso curso SQL para Iniciantes in PostgreSQL. Ele inclui 130 desafios de codificação interativos criados para que você inicie sua jornada no Postgres. Mas se tudo o que você precisa é de uma rápida atualização, aqui está uma maneira de praticar suas habilidades com o PostgreSQL.

O que são arquivos CSV?

CSV é a abreviação de Comma-Separated Values (valores separados por vírgula). Esse formato de arquivo permite armazenar dados em texto simples, o que o torna excelente para compartilhar dados entre aplicativos.

Um arquivo CSV contém linhas de dados correspondentes a registros individuais; a primeira linha geralmente contém os nomes das colunas. Os valores em cada linha são separados por vírgula. Embora as vírgulas sejam o separador mais comumente usado (como sugere o nome do formato), outros caracteres (por exemplo, ponto e vírgula, tabulações ou espaços) também podem separar os valores.

Aqui está um exemplo de arquivo CSV. Exportaremos um arquivo igual a esse nas próximas seções:

store_id,revenue,day
1,100.42,2023-05-01
1,148.89,2023-05-02
2,238.98,2023-05-03

Como você pode ver, a primeira coluna armazena o ID da loja, a segunda coluna armazena a receita e a terceira coluna armazena a data. Cada linha é um registro. A organização é muito parecida com a de planilhas e bancos de dados, mas está toda em texto!

Por que exportar um banco de dados como um arquivo CSV?

Os arquivos CSV podem ser abertos por praticamente qualquer software orientado a dados. Exportar seus dados do PostgreSQL para um arquivo CSV significa que você pode compartilhar facilmente informações com colegas, mesmo que eles usem ferramentas diferentes.

O formato CSV também é nativo de muitas ferramentas de análise de dados e planilhas. A exportação por meio desse formato permite que você transfira dados de forma rápida e fácil para análises aprofundadas, visualizações e relatórios.

Exportação de dados do PostgreSQL para CSV

Os dados

Esta será a nossa tabela de exemplo chamada sales. Cada linha representa o quanto cada loja ganhou em um determinado dia.

sales

Exportação de dados com consultas SQL

Usaremos duas consultas SQL diferentes para testar diferentes métodos de exportação. (Vale a pena ter nossa Folha de dicas do PostgreSQL por perto para aumentar a produtividade quando estiver escrevendo suas próprias consultas). Esta é a primeira:

SELECT * FROM sales

Essa primeira consulta é simples e seleciona tudo na tabela. A ideia é garantir que não perderemos dados durante a exportação.

Aqui está a segunda consulta (muito mais complexa):

WITH max_revs (store_id, day, revenue, max_rev) AS (
	SELECT
		store_id,
		day,
		revenue, 
		MAX(revenue) OVER(PARTITION BY day) 
	FROM sales 
)
SELECT
	store_id,
	day,
	max_rev
FROM max_revs
WHERE revenue = max_rev;

A segunda consulta simula um relatório simples; para cada dia, ela gera a loja principal por receita e a própria receita mais alta. Isso é o que você pode escrever ao trabalhar com análise de dados. Usamos uma expressão de tabela comum para selecionar primeiro a maior receita do dia; a consulta externa (segunda) seleciona a(s) loja(s) que corresponde(m) à receita retornada pelo CTE.

Se você ainda não estiver familiarizado com CTEs e funções de janela, consulte a trilha SQL Reporting e nosso curso sobre funções de janela no SQL. Mas, por enquanto, vamos voltar à exportação de dados do PostgreSQL para um arquivo CSV. Quando tivermos os dados que desejamos, precisaremos iniciar o processo de exportação.

Exportando o banco de dados usando a linha de comando

Primeiro, vamos explorar a exportação de um banco de dados PostgreSQL por meio da linha de comando. Você tem duas opções aqui: usar o comando \copy ou a instrução COPY; explicaremos as diferenças entre os dois em breve.

Embora o uso de qualquer um desses métodos seja menos simples do que o uso do pgAdmin, você provavelmente já tem tudo o que precisa para isso. Então, vamos começar.

Conexão ao banco de dados com o psql

Para ambas as opções de linha de comando, usaremos o psql. Essa é uma ferramenta de linha de comando para bancos de dados PostgreSQL.

Para se conectar ao banco de dados, digite o seguinte comando:

psql -h <hostname> -p <port> -d <database name> -U <username>

Substitua hostname pelo nome do host (ou endereço) do banco de dados. A porta é especificada no servidor remoto juntamente com o nome do banco de dados e o nome de usuário. Depois disso, você será solicitado a fornecer uma senha.

Depois de se conectar ao banco de dados, você pode usar o comando \copy ou o método COPY para exportar os dados. Começaremos demonstrando o comando \copy.

1. exportação de dados usando \copy

O comando \copy copiará diretamente o resultado da consulta local para um arquivo local em seu computador. Ele funciona no lado do cliente, portanto, é uma ótima opção se você tiver apenas permissões de leitura do banco de dados.

Aqui está a sintaxe:

\copy (query) to ‘filename’ with cvs [header]

Você pode incluir o argumento opcional de cabeçalho quando quiser que a tabela gerada tenha uma linha extra na parte superior contendo todos os nomes das colunas. Para garantir a clareza dos resultados, usarei esse argumento em todos os comandos futuros.

Vamos dar uma olhada em alguns exemplos.

Exemplo 1: Copiar a tabela inteira

Código:

\copy (SELECT * FROM sales) to ‘/dbExport/sales_full_table.csv’ with csv header

Explicação: Após executar a consulta SELECT * FROM sales, seu computador copia os resultados para o arquivo CSV especificado. Nenhuma solicitação adicional ao banco de dados é enviada.

Exemplo 2: Copiar resultados da consulta

Código:

\copy (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) to ‘/dbExport/sales_report_query.csv’ with csv header

Explicação: O mesmo princípio está funcionando aqui. O banco de dados executa essa consulta de relatório e seu computador local copia os resultados em um arquivo.

O comando \copy é fácil de usar e, na maioria dos casos, é tão rápido quanto a consulta média. Isso ocorre porque o arquivo resultante é gerado localmente em vez de ser enviado pelo banco de dados.

2. usando a instrução COPY

Ao contrário do comando \copy, a instrução COPY é executada no banco de dados. Chamamos esse comando de instrução porque COPY é, na verdade, parte de um comando executado pelo banco de dados. Isso significa que o arquivo resultante será salvo no servidor remoto, portanto, tenha isso em mente ao escolher o caminho do arquivo. Salvar em um servidor remoto também significa que você deve ter privilégios de superusuário (root) do PostgreSQL.

A sintaxe do comando COPY é a seguinte:

COPY { table | (query) } 'filename' [ DELIMITER 'delimiter' ] [HEADER]

Omiti alguns argumentos para simplificar; se você quiser ver a sintaxe completa, visite a documentação do PostgreSQL

Exemplo 1: Copiar a tabela inteira

Código:

COPY sales TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Explicação: Como era de se esperar, isso copia toda a tabela de vendas para o arquivo sales_full_table.csv. Observe que definimos o delimitador como vírgula e incluímos a linha de cabeçalho.

Exemplo 2: Copiar os resultados da consulta

Código:

COPY (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Explicação: Como o subtítulo sugere, isso exporta os resultados da consulta para o arquivo sales_full_table.csv. Aqui está um exemplo dos dados em forma de tabela:

Como você pode ver, o resultado é o mesmo do comando \copy; a única diferença é o local onde o arquivo resultante é armazenado.

Exportação de dados usando o pgAdmin

Agora vamos dar uma olhada na segunda opção, que usa uma interface gráfica do usuário (GUI). Você já deve estar familiarizado com o pgAdmin; ele é um programa de gerenciamento de banco de dados PostgreSQL gratuito e de código aberto. Essa é uma maneira simples e direta de exportar dados de um banco de dados Postgres.

Conecte-se ao banco de dados

Abra o pgAdmin e clique em Add New Server (Adicionar novo servidor).

Na página General (Geral), escolha um nome para a conexão.

Na página Connection (Conexão ), digite o nome do host ("localhost" se estiver executando o banco de dados em seu próprio computador), o nome do banco de dados, o nome de usuário e a senha.

Por fim, clique em Salva. Se a conexão tiver sido estabelecida corretamente, a página do painel exibirá gráficos da atividade do banco de dados.

Exportar resultados de consulta

Primeiro, abriremos a ferramenta de consulta clicando no ícone do banco de dados (três discos) na barra de ferramentas superior ou usando Alt+Shift+Q. Agora você pode consultar o banco de dados escrevendo consultas na caixa de texto e executando-as usando o botão play na parte superior ou a tecla F5.

Depois de executar uma consulta, seu resultado será exibido na janela inferior. Para salvar o resultado em um arquivo CSV, pressione o botão de download logo acima dos dados resultantes.

Como você pode ver, os resultados são os mesmos que os das ferramentas de linha de comando. A principal diferença é que o pgAdmin facilita muito a alternância entre a criação de consultas e o salvamento dos resultados.

Saiba mais sobre a exportação de dados com arquivos CSV

Exportar bancos de dados para arquivos CSV é um prazer! Agora que você sabe como exportar todos os dados de que precisa, talvez tenha outra pergunta: Como faço para importar dados para um banco de dados Postgres? Leia este breve artigo sobre como importar dados para o PostgreSQL usando o pgAdmin para descobrir.

E se quiser expandir seus conhecimentos sobre o PostgreSQL, confira nossa trilha completa de A a Z com PostgreSQL, que inclui mais de 1.000 exercícios interativos. Bom aprendizado!