Postgresql - Importar dados de um arquivo csv ou txt com o comando psql \copy

Através do comando psql \copy é possível importar dados de um arquivo para uma tabela. O arquivo pode estar localizado tanto no servidor quanto em uma máquina cliente.

Neste exemplo, vamos executar os comandos do cliente psql através do prompt de comando do Windows.

Em breve, será postado como executar os comandos no terminal linux.

Não confunda com o comando sql copy, onde o arquivo deve estar obrigatoriamente no servidor.

Para saber mais sobre o comando sql copy acesse o link: Postgresql - Importar arquivo csv e txt

1º) Exemplo

Vamos importar os dados do arquivo "estoque.csv" localizado no diretório "C:\temp" da máquina cliente para a tabela "estoque", contida no banco de dados "db_rem", localizado no servidor "104.197.20.162".
Caso tenha interesse, faça o download deste exemplo no Google Drive ou veja no GitHub.

Note que este arquivo possui cabeçalho na 1ª linha:
O cabeçalho é:

codigo;produto;categoria;marca;preco;quantidade

Arquivo: estoque.csv

Diretório C:\temp

Localização e script da tabela "estoque"

Para a importação siga as etapas abaixo:

a) Abra o prompt de comando.

Prompt de Comando

b) Faça a autenticação no servidor.

Autenticação

Segue o comando:

psql -h 104.197.20.162 -d db_rem -U postgres

Legenda:
-h: ip ou nome do host;
-d: nome do banco de dados;
-U: nome do usuário;

c) Será solicitada a senha, repare que ao digitar a senha não serão exibidos os caracteres. Digite a senha e pressione "Enter".

d) Agora utilize o comando \copy para importar os dados para a tabela estoque:

\copy estoque(codigo, produto, categoria, marca, preco, quantidade)  from 'C:/temp/estoque.csv' with delimiter as ';' CSV HEADER

Comando \copy

Legenda:

• \copy: comando psql;
• estoque: nesta tabela serão incluídos os registros que estão no arquivo;
• codigo, produto, categoria, marca, preco e quantidade: nome das colunas;
• from 'C:/temp/estoque.csv': após o "from" deverá ser colocado o nome do diretório, seguido pelo nome do arquivo e a extensão. Neste exemplo, a extensão é ".csv" mas poderia ser ".txt". Importante!!! Até mesmo no sistema operacional Windows, repare que NÃO se deve utilizar a barra invertida "\", e sim a barra normal "/" para identificar o diretório;
• with delimiter as ';': o delimitador serve para separar as colunas. Após o "with  delimiter as", coloque o delimitador. Neste exemplo é o ";" mas o delimitador pode ser "|" ou "," entre outros;
• CSV HEADER: este parâmetro indica que o arquivo possui cabeçalho;

Após executar o comando, 3 registros foram inseridos na tabela.

Registros importados

2º) Exemplo

Neste exemplo, vamos importar um arquivo sem cabeçalho chamado "estoque_sc.csv". O procedimento é o mesmo do anterior com exceção do comando \copy que NÃO será utilizado o parâmetro CSV HEADER.

Caso tenha interesse, faça o download deste exemplo no Google Drive ou veja no GitHub.

Arquivo: estoque_sc.csv

Segue o comando \copy para importar os dados para a tabela estoque:

\copy estoque(codigo, produto, categoria, marca, preco, quantidade)  from 'C:/temp/estoque_sc.csv' with delimiter as ';' 

Comando \copy

Legenda:

• \copy: comando psql;
• estoque: nesta tabela serão incluídos os registros que estão no arquivo;
• codigo, produto, categoria, marca, preco e quantidade: nome das colunas;
• from 'C:/temp/estoque_sc.csv': após o "from" deverá ser colocado o nome do diretório, seguido pelo nome do arquivo e a extensão. Neste exemplo, a extensão é ".csv" mas poderia ser ".txt". Importante!!! Até mesmo no sistema operacional Windows, repare que NÃO se deve utilizar a barra invertida "\", e sim a barra normal "/" para identificar o diretório;
• with delimiter as ';': o delimitador serve para separar as colunas. Após o "with  delimiter as", coloque o delimitador. Neste exemplo é o ";" mas o delimitador pode ser "|"  ou ","  entre outros;

Após executar o comando, 3 registros foram inseridos na tabela.

Registros Importados

Postgresql - Listar colunas de uma tabela

Neste post vamos mostrar como listar as colunas de uma tabela no Postgresql.

Para você que está familiarizado com o mysql ou oracle, é equivalente ao comando "describe".

Há duas formas de exibir as colunas de uma tabela.  A primeira é utilizar um comando psql e a segunda é fazer uma consulta utilizando o "information_schema".

1ª ) Utilizar comando psql;

\d+ nome_da_tabela

Exemplo

Listar as colunas da tabela "tb_uf".

\d+ tb_uf

2ª ) Utilizar a view "columns". Esta view já está localizada por padrão no catálogo de objetos do Postgresql, em information_schema.
O information_schema é um padrão ANSI, que contém views que fornecem informações sobre o banco de dados: tabelas, views, colunas e procedures.

Veja a sua localização na imagem abaixo:

Exemplo

Agora vamos listar as colunas da tabela "tb_uf", utilizando a view "columns" , em information_schema.

SELECT
column_name,
data_type,
table_catalog,
table_name,
ordinal_position,
is_nullable,
table_schema
FROM
information_schema.columns
WHERE table_name = 'tb_uf';

Veja a imagem abaixo:

Descrevemos algumas informações sobre as colunas da view "columns". Listamos a seguir:

• column_name: armazena o nome da coluna. Neste exemplo temos as colunas "uf_cod" e  "uf_descricao";
• data_type: armazena o tipo de dado da coluna. Neste exemplo temos os tipos character varying (varchar), utilizados para armazenar caracteres alfanuméricos e o tipo smallint utilizados para armazenar números inteiros no intervalo de -32768 até +32767;  
• table_catalog: em qual banco a coluna foi criada. Neste exemplo as colunas foram criadas no banco "db_01";
• table_name: em qual tabela a coluna foi criada. Neste exemplo, as colunas pertencem a tabela "tb_uf";
• ordinal_position: ordem de criação de uma coluna em uma tabela, ou seja, 1ª coluna, 2ª coluna, e assim por diante. No exemplo acima, a coluna "uf_cod" é a 1ª coluna e a coluna "uf_descricao" é a 2ª coluna;
• is_nullable: indica se o preenchimento da coluna é obrigatório ou não. Se o conteúdo for "YES", significa, que o preenchimento é opcional. Se "NO", indica que a coluna deve ser preenchida obrigatoriamente. Neste exemplo, as duas colunas tem preenchimento opcional; 
• table_schema: nome do schema que pertence a coluna. Neste exemplo, as colunas pertencem ao schema public, que é o schema padrão do postgresql;

Para visualizar toda as colunas, execute o comando:

SELECT
*
FROM
information_schema.columns
WHERE table_name = 'tb_uf';

Para saber mais sobre o assunto acesse:

https://www.postgresql.org/docs/9.3/static/infoschema-columns.html

Procurar tabelas que contenham uma coluna com um nome especifico - Postgresql

Para pesquisar quais as tabelas contém uma coluna com um nome específico, vamos utilizar a view "columns".

Esta view já está localizada por padrão no catálogo de objetos do Postgresql, em information_schema.

O information_schema é um padrão ANSI, que contém views que fornecem informações sobre o banco de dados: tabelas, views, colunas e procedures.

 Veja a sua localização na imagem abaixo:

1º) Exemplo:

Procurar todas as tabelas que contenham a coluna "cod_uf "?

SELECT 
table_name
FROM information_schema.columns
WHERE column_name  =  'cod_uf';

Ao executarmos a consulta acima, será retornado o resultado exibido na imagem abaixo:

Logo, a coluna "cod_uf ", pode ser encontrada na tabela "tb_municipio" e "tb_estado".

Legenda:

• table_name: indica qual tabela a coluna pertence;
• column_name: armazena o nome da coluna;
• columns: esta view por padrão armazena informações sobre as colunas do banco; 

2º) Exemplo:

Procurar todas as tabelas que contenham colunas iniciadas por "cod_"?

SELECT 
table_name,
column_name
FROM information_schema.columns
WHERE column_name  LIKE  'cod_%';

Ao executarmos a consulta acima, será retornado o resultado exibido na imagem abaixo:

Logo existem colunas iniciadas por "cod_ " nas tabelas "tb_ordem_servico", "tb_municipio" e "tb_estado".

3º) Exemplo:

Além de procurar todas as tabelas que contenham colunas iniciadas por "cod_", vamos mostrar o tipo de dado de cada coluna.

Para isso vamos incluir na consulta a coluna data_type

SELECT 
table_name,
column_name,
data_type 
FROM information_schema.columns
WHERE column_name  LIKE  'cod_%'

Ao executarmos a consulta acima, será retornado o resultado exibido na imagem abaixo:

Observações finais:

Existem mais colunas na view "columns", que podem ser utilizadas na sua consulta. Para visualizar todas, utilize "*":

SELECT 
*
FROM information_schema.columns;

Para saber mais sobre o assunto acesse:

https://www.postgresql.org/docs/9.3/static/infoschema-columns.html

Google Cloud VM Debian - Instalação do Postgresql

1º) Clique na seta, para escolher um projeto.Veja a imagem abaixo: 

Caso não tenha VM Debian configurada, e tenha dúvida, veja o post:

Google Cloud - Criar Máquina Virtual (VM) - Debian 9

2º) Clique no link com o nome do projeto.

3º) No menu escolha a opção "Compute Engine", será aberto um submenu, escolha a opção "Intâncias da VM":

4º) Conecte-se a sua VM Debian via SSH, neste exemplo,  a conexão será feita através do navegador.

5º) Conecte-se como usuário root (super-usuário) do Debian. Caso tenha dúvida, como configurar a senha de root na VM Debian, veja o artigo:

Google Cloud VM - Senha de root do Debian

6º) Atualize os pacotes do Debian, antes de instalar o postgresql. Com o comando:

apt-get update

Veja a imagem abaixo:

7º) Agora instalaremos o Postgresql, o cliente do Postgresql (psql) e o pacote adicional "postgresql-contrib", que contém comandos e funções que contribuirão para manipulação de dados.

apt-get -y install postgresql postgresql-client postgresql-contrib

Legenda do comando:

• apt-get install: comando de instalação;
• -y: durante a instalação, deverá ser feita algumas confirmações, o "-y" significa "yes", ou seja, "sim", para a instalação não ficar tendo interrupções esperando uma confirmação do usuário, deixe como padrão sim, ela será executada do começo ao fim sem parar;
• postgresql: para instalar o postgresql;
• postgresql-client: para instalar o cliente do Postgresql (psql), através do psql é possível executar querys e ver o resultado das mesmas;
• postgresql-contrib: contém comandos e funções que contribuirão para manipulação de dados;  

Veja a imagem abaixo:

8º) Execute o cliente do Postgresql, o psql, com o usuário postgres.
Será acessado o banco de dados padrão "postgres" (tanto o usuário quanto o banco tem o mesmo nome "postgres").

sudo -u postgres psql postgres

Veja a imagem abaixo:

9º) Veja o psql em execução, na imagem abaixo:

10º) Configure a senha do usuário postgres, utilize o comando:

\password postgres

Veja a imagem:

11º) Digite a senha e a confirme.

12º) Instale a extensão adminpack

CREATE EXTENSION adminpack;

Veja a imagem abaixo:

13º) Será exibida que a mensagem confirmando que a extensão foi criada.

14º) O postgresql está instalado e psql já está executando.

15º) Para sair do psql, utilize o comando:

\q

Veja a imagem abaixo:

16º) Se você gostaria de saber mais sobre:

• Configuração das conexões com o banco; 
• Configuração do firewall;
• Acessar o postgresql da sua máquina local, utilizando interface gráfica, como por exemplo, o PgAdmin

Leia o artigo:

Google Cloud VM Debian - Configuração para conexão remota no Postgresql

Google Cloud - Criar Máquina Virtual (VM) - Debian 9

1º) Clique na seta destacada em vermelho para criar um novo projeto. 

2º) Será aberta uma nova tela, na qual é possível pesquisar um projeto existente ou clicar no botão "+", para criar um novo projeto, que é nosso caso.

3º) Neste exemplo, o novo projeto será chamado "projeto-teste-2". Após preencher o nome do seu projeto clique no botão "Criar"

4º) Clique no botão "Criar" para criar uma nova Instância da VM.

5º) Será aberto um formulário de criação da VM,  a seguir serão preenchidos os campos básicos para o funcionamento da VM:

6º) Preencha o nome do campo.

7º) Preencha o nome da zona.

Para saber mais sobre as localidades que serão hospedados seus dados, veja:

https://cloud.google.com/compute/docs/regions-zones/regions-zones?hl=pt_BR&_ga=2.227979677.-1618039993.1498426005#available

8º) Preencha o tipo máquina. O tipo de máquina é a configuração da sua máquina, ou seja processador, memória e disco (hd). O preço varia de acordo com a máquina escolhida. Neste exemplo, escolhemos uma das mais simples a "SMALL", mas há várias configurações de acordo com a sua necessidade.

O tipo e o tamanho de hd será escolhido no 9º passo.

Repare que o valor estimado mensalmente, é exibido do lado direito. Estimado, porque o valor é cobrado por hora em que a instância, está "ativa",  caso você não use está máquina continuamente durante o mês, é possível desativá-la, e quando precisar reative, novamente, e pague somente as horas utilizadas durante o mês.

Atualmente, o google está com uma promoção que oferece R$ 300,00 de crédito para novos usuários do CLOUD. Esses créditos expiram após um ano. Caso o crédito acabe antes de um ano, será necessário, efetuar o pagamento para continuar utilizando o serviço, caso tenha interesse.

Particularmente, estou utilizando o serviço a praticamente 3 meses, e estou gostando bastante.

9º) Agora vamos escolher a imagem do sistema operacional, neste exemplo, vamos escolher o DEBIAN, mas há centenas de imagens disponíveis no Cloud, entre elas as distribuições Linux: Ubuntu, CentOs, Red Hat.
Também está disponível imagens para o sistema operacional Windows.

A seguir escolha o tipo do disco "convencional" ou SSD (este disco tem maior velocidade de acesso).

Preencha o tamanho do disco, por padrão vem configurado com 10 GB, mas você poderá aumentar a capacidade.

10º) Se você quer criar, um servidor web, você pode liberar:

• o tráfego HTTP, na porta 80;
• o tráfego HTTPS, para conexões seguras, na porta 443; 

11º) Clique no link "Redes" para configurar um IP estático (fixo).

12º) Preencha o campo "Nome", a seguir clique no botão "Reservar". Quando a instância estiver criada, será exibido um ip para a nova instância, neste exemplo foi o 35.185.28.193

13º) Clique no botão "Criar" para a criação da instância.

14º) Aguarde a criação da instância.

15º) Quando a instância estiver criada, aparecerá um ícone verde, indicando que a instância está pronta. Você já pode utilizar sua máquina virtual (VM) Debian 9.

16º) Há algumas maneira para se conectar a VM via SSH. A mais simples é e escolher a opção "Abrir na janela do navegador". Será aberta uma nova janela no navegador, onde você visulizará sua nova VM Debian.

Também é possível se conectar no terminal do linux, usando o comando SSH ou no windows através do PUTTY (cliente SSH,  para utilizá-lo é necessário fazer o download e instalar, é gratuito).

Para saber mais sobre conexão SSH no Google Cloud, veja:

https://cloud.google.com/compute/docs/instances/connecting-to-instance#standardssh

17º) Veja a seguir, a sua nova Máquina Virtual (VM), exibida no navegador.

Se você já está com sua estância criada mas precisar aumentar o tamanho do disco  e / ou a quantidade de memória é muito simples, basta apenas acessar uma estância clicar em editar e alterar os valores de disco e / ou memória e depois salvar as alterações

Para se autenticar no Debian 9, veja o artigo:

Google Cloud VM - Senha de root do Debian

Google Cloud VM Debian - Configuração para conexão remota no Postgresql

Neste post, descrevemos como se conectar remotamente no Postgresql, utilizando o Google Cloud VM Debian.


1º)  Acessar a instância da VM através do menu: Compute Engine => Instâncias da VM

2º) Escolha a instância em que está instalado o Debian e se conecte via SSH. Veja a imagem abaixo:

3º) Será aberto o terminal, em seguida, faça a autenticação no debian, como root. Para se autenticar utilizar, utilize o comando:

su

Veja a imagem abaixo:

Autenticação  no Debian 

4º) Se autenticar no cliente psql, neste caso foi feita a autenticação com o usuário "postgres" e o banco de dados "postgres", utilize o comando:

sudo -u   nome_usuario    psql nome_banco

sudo -u   postgres              psql postgres

Veja a imagem abaixo:

5º) Localize o diretório onde se encontra o arquivo "pg_hba.conf". Utilize o comando:

SHOW hba_file

Veja a imagem:

6º) Localize o diretório onde se encontra o arquivo "postgresql.conf". Utilize o comando:

SHOW config_file

Veja a imagem:

7º) Saia do cliente psql. Veja o comando:

\q

8º) Após localizar o diretório do arquivo "pg_hba.conf", vamos editá-lo. Podemos utilizar um editor de texto. Exemplo: "nano" ou "vi".
Neste, exemplo será utilizado o "nano". Utilize o comando abaixo:

nano /etc/postgresql/9.6/main/pg_hba.conf

Veja a imagem abaixo:

9º) No arquivo "pg_hba.conf", localize "127.0.0.1/32", ou seja, o ip 127.0.0.1 e mascara 32.

Após localizar o IP, nós vamos substituí-lo de acordo com o tipo de conexão desejada:

• Para aceitar conexão de qualquer ip:  substitua o ip  127.0.0.1/32 pelo ip 0.0.0.0/0. Para fechar o editor "Nano", pressione as teclas "CTRL+X". Em seguida, será exibida uma pergunta confirmando se você deseja salvar, pressione a tecla "y" para salvar ou a tecla "n" para desistir das alterações.  Veja a imagem a seguir:

• Para restringir a conexão a uma determinada faixa de ips: substitua o ip 127.0.0.1 pelo ip desejado, neste exemplo foi utilizado o ip 192.168.64.2/24, poderia ser um outro ip.  Para sair do editor "Nano", pressione as teclas "CTRL+X". Em seguida, será exibida uma pergunta, confirmando se você deseja salvar, pressione a tecla "y" ou a tecla "n" para desistir das alterações. Veja a imagem a seguir:

10º) Após localizar o diretório do arquivo "postgresql.conf", vamos editá-lo.

nano /etc/postgresql/9.6/main/postgresql.conf

Veja a imagem abaixo:

11º) No  arquivo "postgresql.conf", localize o item listen_adressess. Veja a imagem abaixo:

12º)  Remova o comentário do item, listen_adressess, representados pelo "#". Em seguida, substitua, 'localhost', por '*'. Para sair do editor "Nano", pressione as teclas "CTRL+X". Em seguida, será exibida uma pergunta, confirmando se você deseja salvar, pressione a tecla "y" ou a tecla "n" para desistir das alterações. Veja a imagem a seguir:

13º) Escolher a quantidade de conexões, no padrão de instalação, o máximo são 100 conexões. O número de conexões é armazenado no parâmetro "max_connections".

Neste exemplo, vamos alterar o número de conexões 100 para 120.

14º) Alterar o parâmetro, "shared_buffers", que determina quanta memória é dedicada para o Postgresql para o caching de dados. Por padrão, está configurado com 128 MB.

Se o seu sistema tem 1GB ou mais de RAM, o parametro "shared_buffer" deve estar configurado para utilizar 25% da memoria total. Neste exemplo, o sistema possui 2 GB, então vamos utilizar 512 MB para o parâmetro, shared buffer.

Se o seu sistema possui menos de 1GB de RAM, você deve utilizar aproximadamente 15% da sua memória RAM.

15º) Reinicie o serviço do postgresql

sudo service postgresql restart

16º)  No Google Cloud VM, vamos clicar no item de menu "Regras de firewall". Veja a imagem abaixo:

17º)  Em seguida clique no link "Criar Regra de Firewall", no topo da pagina.

18º)  Será aberto o formulário, através do qual poderemos liberar a porta padrão 5432 do Postgresql, ou uma outra porta, conforme sua necessidade.

19º) Deverão ser preenchidos os seguintes campos:

• Campo nome:

• Preencha a tag e um IP:

• Preencha o protocolo e a porta: repare que devemos separar o protocolo e a porta com ":"

protocolo:porta

• Clique no botão salvar

20º) Agora vamos acessar a instância da VM através do menu:

Compute Engine => Instâncias da VM

21º) Clique na instância "DEBIAN". Veja a imagem abaixo:

22º) Clique em "Editar"instância. Veja a imagem abaixo:

23º) No campo "Tags de rede",  adicionar a tag "pg", esta tag foi incluída na regra do firewall (15º passo). Ver imagem abaixo:

24º) Clique no botão "Salvar". Ver imagem abaixo:

25º) Clique no link  no formato de "seta". para voltar a visualizar página inicial. Ver imagem abaixo:

26º) Observe na página inicial, o ip externo da sua estância, neste caso é o ip 35.202.119.38 que será utilizado para fazer a conexão remota.

27º) Agora, após a finalização da configuração da VM, será feita a conexão remota. Vamos utilizar o PgAdmin para efetuar a conexão.
Primeiramente, clique com o botão direito em "Server", selecione o menu "Create", em seguida, selecione o submenu "Server".

28º) Será exibido um formulário. Vamos preencher alguns campos da aba "General" e da aba "Connection" com as configurações do servidor:

• Aba General: vamos escolher um nome para a conexão remota, neste exemplo eu escolhi "REM", mas poderia ser qualquer outro nome.

• Aba Connection: preencha o ip, a porta, o  nome do banco, o usuário e a senha, em seguida clique no botão "Salvar". Veja a imagem abaixo:

29º) A conexão remota foi efetuada com sucesso. Veja imagem abaixo: