Utilização do psql

Introdução

O psql é a ferramenta de linha de comando padrão para interagir com bancos de dados PostgreSQL, permitindo que você execute consultas SQL, configure o servidor, administre usuários e bancos de dados, importe e exporte dados, e muito mais. Neste artigo, você vai aprender sobre os principais utilitários, parâmetros e comandos, como também verá diversos exemplos de diferentes situações de uso.

Roteiro:

• Conexão
• Variáveis internas do psql
• Comandos básicos
• Arquivo psqlrc
• Arquivo psql_history
• Importar e exportar dados

Conexão

Para abrir o executável do psql, podemos rodar apenas “psql” (supondo que o binário já esteja na variável de ambiente PATH) ou então passar o caminho completo do seu binário.
Assim que entramos, é possível ver algumas informações sobre o PostgreSQL, como a versão do psql e do servidor, que pode ser diferente a depender da instalação:

• Com cliente (psql) e servidor na mesma versão:

$ psql
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

• Com cliente (psql) e servidor em versões diferentes, sendo a versão do psql a 15.7 e do servidor a 16.2:

$ /usr/lib/postgresql/15/bin/psql
psql (15.7 (Debian 15.7-1.pgdg120+1), server 16.2 (Debian 16.2-1.pgdg120+2))
WARNING: psql major version 15, server major version 16.
         Some psql features might not work.
Type "help" for help.

postgres=#

Ao acessar o psql, é possível passar alguns parâmetros, uns com informações da conexão, outros com ações para serem realizadas imediatamente após a conexão. Entretanto, mesmo com essa possibilidade, o psql é inteligente o suficiente para inferir alguns parâmetros de acesso automaticamente ou usar valores padrão, quando possível, e também variáveis de ambiente se estiverem configuradas.

Alguns dos principais parâmetros de conexão são:

• -h ou --host: host (endereço) onde o servidor PostgreSQL está sendo executado (o padrão é localhost);
• -p ou --port: número da porta em que o servidor PostgreSQL está recebendo as conexões (o padrão é 5432);
• -U ou --username: nome do usuário usado para autenticar a conexão com o servidor (o padrão é o nome do usuário atual do sistema operacional).

E algumas das principais opções:

• -d ou –-dbname: nome do banco de dados (o padrão é o nome do usuário passado);
• -c ou --command: permite que você forneça uma consulta SQL ou um comando diretamente na linha de comando para ser executado imediatamente após a conexão ser estabelecida;
• -v ou --variable: define uma variável de ambiente para a sessão do psql, que pode ser útil para personalizar o comportamento do psql durante uma sessão (veremos mais sobre isso adiante);
• -l ou --list: lista todos os bancos de dados;
• -E ou --echo-hidden: usado para exibir as consultas feitas pelos comandos internos do psql.

Considerando os parâmetros citados, alguns exemplos de acesso ao psql via Linux são:

• Acesso com parâmetros -h de endereço do servidor, -p de porta, -d de nome do banco de dados e -U de nome do usuário:

$ psql -h localhost -p 5432 -d banco_exemplo -U usuario_exemplo
Password for user usuario_exemplo:
psql (16.2 (Debian 16.2-1.pgdg120+2))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

• Acesso com parâmetros -h de endereço do servidor (dessa vez utilizando um IP), porta como default (sem passar o parâmetro, portanto, acessando diretamente a 5432), parâmetro de usuário e banco:

$ psql -h 64.23.248.23 -U usuario_ex -d banco_exemplo
Password for user usuario_ex:
psql (16.2 (Debian 16.2-1.pgdg120+2))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

banco_exemplo=>

• Acesso sem parâmetro, que permite que o psql interprete o usuário e o nome do banco de dados automaticamente, considerando o mesmo nome do usuário atual do Linux (nesse caso, usuário “postgres”, que acessou o usuário de mesmo nome no servidor e também entrou no banco chamado “postgres”):

$ psql
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

postgres=#

• Acesso com parâmetros de endereço do servidor e apenas do usuário, ocultando o nome do banco de dados e realizando a ação citada acima: caso seja omitido o nome do banco, o psql busca por algum banco que tenha o mesmo nome do usuário:

$ psql -h 64.23.248.23 -U usuario_ex
Password for user usuario_ex:
psql (16.2 (Debian 16.2-1.pgdg120+2))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

usuario_ex=>

• Acesso ao psql com parâmetro -c para utilização de um comando depois de acessá-lo (o acesso é feito, o comando rodado e depois você é desconectado do psql, então ele entra apenas para rodar o comando fornecido), no nosso caso, um SELECT dos dados de uma tabela:

$ psql -c "SELECT * FROM minha_tabela;"
 id |      nome
----+----------------
  1 | Ana Clara
  2 | João Silva
  3 | Maria Souza
(3 rows)

$

• Acesso com parâmetro -v, definindo uma variável chamada “meu_nome”, com o valor “João”, e depois utilizando dessa variável para uma consulta na tabela “minha_tabela”:

$ psql -v meu_nome="'João'"
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

postgres=# SELECT * FROM minha_tabela WHERE nome = :meu_nome;
 id | nome
----+------
  4 | João
(1 row)

• Acesso com parâmetro -l listando todos os bancos existentes no servidor (os bancos postgres, template0 e template1 são criados automaticamente na instalação):

$ psql -l
                            List of databases
Name           |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
---------------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 banco_exemplo | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 postgres      | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 template0     | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
               |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1     | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres                 

Os parâmetros para acessar o psql não são fornecidos aleatoriamente, existe uma ordem a ser seguida para que a conexão seja realizada com sucesso, pedindo as opções antes dos parâmetros de conexão. Essa ordem e outras informações úteis podem ser consultadas ao chamar o psql junto com o utilitário --help no shell:

$ psql --help
psql is the PostgreSQL interactive terminal.

Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

Um exemplo de acesso pelo Windows seria assim:

> & "C:\Program Files\PostgreSQL\16\bin\psql.exe" -U usuario_ex -d banco_ex
Senha para o usuário usuario_ex:

psql (16.2)
Digite "help" para obter ajuda.

banco_ex=#

Arquivo .psqlrc

O arquivo .psqlrc é um arquivo de configuração que possibilita personalizar o comportamento padrão do psql ao iniciá-lo, permitindo a execução automática de uma variedade de comandos SQL e configurações especiais sempre que você acessa o psql. Isso acontece porque o psql procura automaticamente por esse arquivo, geralmente localizado em %APPDATA%\postgresql no Windows e no diretório home do usuário no Linux (~/.psqlrc).

Entre os comandos e as configurações que podem ser definidos no arquivo psqlrc, temos:

• Definição de variáveis, as quais serão usadas durante sua sessão do psql;
• Atalhos de comando: definição de atalhos de comando personalizados. Isso pode ser útil para criar abreviações para consultas SQL longas ou frequentemente usadas;
• Configurações de exibição: personalização da aparência do psql, como alterar o formato de exibição das tabelas ou definir cores para destacar diferentes tipos de saída;
• Comandos de inicialização: execução de comandos SQL específicos sempre que o psql é iniciado, ou seja, você pode definir um comando para exibir o tempo de execução das consultas SQL realizadas naquela sessão, com \timing.

Veja um exemplo de configuração útil que recomendamos para inserir no arquivo:

\pset null '(null)'
\set PROMPT1 '%m: %n@%/%R%#%x '
\set PROMPT2 '%m: %n@%/%R%#%x '
\timing
\x auto

Explicando cada exemplo:

• \pset null '(null)': define como o valor NULL será exibido no psql, sendo o valor padrão uma string vazia, alterado aqui para a string ‘(null)’, o que facilita a visualização dos dados;

• \set PROMPT1 ‘%m: %n@%/%R%#%x ’ e \set PROMPT2 ‘%m: %n@%/%R%#%x ’:  define o prompt que será exibido no psql, sendo que a string ‘%m’ representa o endereço do servidor, a string ‘%n’ representa o nome do usuário na sessão, o @ serve apenas para separar uma informação da outra, seguido pela string ‘$/’ que mostra o nome da base de dados atual, depois temos a ‘/%R’ que mostra a informação sobre nossa conexão, sendo o valor padrão ‘=‘ e o valor que indica desconexão ‘!‘, a string ‘#’ mostra se estamos conectados com um superusuário ao exibir ‘#’ e ‘>’ no padrão, e a string ‘%x’ mostra o status de uma transação ‒ sendo ela vazia quando a transação acontece normalmente, ‘*’ para transações bloqueadas, ‘!’ para transações com falha ou ainda ‘?’ para transações com estado indeterminado. Você pode conferir diversas opções de personalização nesta página.

• \set PROMPT2: diz respeito ao prompt que temos quando estamos finalizando algum comando SQL em outra linha, antes de terminá-lo com ‘;’ + Enter. Para manter a mesma visualização, é só repetir as mesmas configurações.

Agora, veja um exemplo sem a configuração:

postgres=#

E outro com a configuração:

[local]: postgres@postgres=# 

• \timing: ativa o cronômetro do psql, que mostrará o tempo decorrido para cada comando executado;
• \x auto: ativa o modo de display expandido automático, para melhor exibir os resultados de consultas no terminal. Aqui, o psql tenta escolher automaticamente o modo que melhor se adapta ao tamanho do terminal, alternando entre modo expandido e não expandido.

Linux

Para criarmos o arquivo .psqlrc em sistemas Linux, podemos usar o seguinte comando:

nano ~/.psql

Após criado e aberto o arquivo, basta inserir nele suas configurações desejadas, conforme exemplo acima.

Windows

O arquivo deve ser criado na pasta de dados de aplicações do usuário, sendo essa uma pasta oculta, que tem como uma forma de acesso o atalho “Windows + R” > digite “%APPDATA%” > clique no botão Enter. A diferença aqui é o nome do arquivo, de acordo com a documentação oficial, ele deve ser chamado de psqlrc.conf, no caminho C:\Users\<usuario>\AppData\Roaming\postgresql (se não houver a pasta “postgresql” ao abrir AppData via atalho, basta criá-la).
O conteúdo é o mesmo do exemplo anterior.

Arquivo psql_history

O arquivo psql_history é um arquivo de texto que armazena o histórico de comandos que você executou no psql. Esse arquivo pode ser útil para:

• Rever comandos: acessar comandos que você executou anteriormente, o que pode ser útil para depuração, repetição de comandos ou consulta de histórico;
• Compartilhar comandos: compartilhar o histórico de comandos com outros usuários ou para fins de documentação;
• Automatizar tarefas: usar scripts que reproduzem comandos do histórico para automatizar tarefas no psql.

Em sistemas Linux, ele também está localizado no diretório home do usuário (~/.psql_history).

Variáveis internas do psql

No psql do PostgreSQL, além das variáveis definidas pelo usuário, como :meu_nome exemplificada anteriormente, existem também variáveis internas predefinidas durante a sessão, que podem ser úteis em diferentes cenários, por exemplo, para gerar relatórios e executar scripts. Para conferir todas as opções, basta acessar a página do site oficial aqui.
Algumas das principais são:

• :DBNAME: retorna o nome do banco de dados atualmente conectado;
• :USER: retorna o nome do usuário atualmente conectado ao banco de dados;
• :PORT: retorna o número da porta usada para a conexão com o servidor PostgreSQL;
• :HOST: retorna o nome do host onde o servidor do banco de dados PostgreSQL está sendo executado.

Para consultar o valor de alguma dessas variáveis, podemos usar o utilitário \echo:

\echo :'DBNAME'
\echo :'USER'
\echo :'PORT'
\echo :'HOST'

'postgres'
'postgres'
'5432'
'192.168.0.10'

Comandos básicos

O psql oferece uma variedade de comandos para interagir com o banco de dados PostgreSQL, que podem ser consultados no site oficial. Aqui estão alguns dos comandos básicos (todos utilizam o console interativo, então, para sair, é só apertar a letra q):

• \?: exibe a lista de comandos disponíveis do psql, com explicação do que cada um faz, permitindo navegar por essa lista;
• \h: exibe a lista de comandos SQL;
• \h <instrução SQL>: usado para obter ajuda sobre uma instrução SQL específica. Deve-se digitar \h seguido pelo nome de uma instrução SQL (por exemplo, \h SELECT), e o psql exibe informações detalhadas sobre a sintaxe e o uso da instrução SQL especificada;
• Comandos \d (é possível acrescentar “S” e/ou “+” em cada um deles, sendo “S” para mostrar objetos internos do sistema e “+” para revelar informações adicionais):
  • \d: lista todos os objetos do schema atual;
  • \d <nome do objeto>: mostra detalhes da estrutura do objeto especificado, podendo ser tabelas, views, índices, funções e sequências;
  • \dt: lista as tabelas no schema atual;
  • \df: lista todas as funções do schema atual; 
  • \di:  lista todos os índices existentes no schema atual;
  • \dx: lista todas as extensões instaladas no schema atual.
  • \dn: lista os schemas do banco;
  • \du: lista todos as roles do banco;
• \l: lista os bancos de dados existentes;
• \set: define uma variável de sessão com nome e valor específicos;
• \unset: remove uma variável de sessão previamente definida com \set;
• \i <nome do arquivo>: executa comandos SQL a partir de um arquivo, ou seja, importa arquivos no psql;
• \o <nome do arquivo>: redireciona a saída de comandos para um arquivo de texto, ou seja, exporta arquivos no psql;
• \o: termina o redirecionamento para o arquivo aberto anteriormente;
• \t: ativa ou desativa a exibição do cabeçalho com os nomes das colunas e o rodapé com a contagem de linhas do resultado;
• \x: ativa ou desativa a formatação de tabelas expandida, o que facilita a leitura e a visualização de tabelas com muitas colunas.

E aqui estão exemplos de uso desses comandos:

• \l:

# \l
                                                     List of databases
     Name      |  Owner   | Encoding | Locale Provider | Collate |  Ctype  | ICU Locale | ICU Rules |   Access privileges
---------------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
 banco_exemplo | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
  postgres     | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           |
 template0     | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
               |          |          |                 |         |         |            |           | postgres=CTc/postgres
 template1     | postgres | UTF8     | libc            | C.UTF-8 | C.UTF-8 |            |           | =c/postgres          +
               |          |          |                 |         |         |     
 (7 rows)

• \d:

# \d
                     List of relations
   Schema   |         Name         |   Type   |    Owner
------------+----------------------+----------+-------------
 meu_schema | Exemplo              | table    | meu_usuario
 meu_schema | Exemplo_id_seq       | sequence | meu_usuario
 meu_schema | Outro_exemplo        | table    | meu_usuario
 meu_schema | Outro_exemplo_id_seq | sequence | meu_usuario
 meu_schema | exemplo2             | table    | meu_usuario
 meu_schema | exemplo2_id_seq      | sequence | meu_usuario
 meu_schema | outro_exemplo        | table    | meu_usuario
 meu_schema | outro_exemplo_id_seq | sequence | meu_usuario
(8 rows)

• \dt:

postgres=# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | alunos        | table | postgres
 public | cidades       | table | postgres
 public | coordenadores | table | postgres
 public | minha_tabela  | table | postgres
 public | nomes         | table | postgres
 public | pessoas       | table | postgres
 public | professores   | table | postgres
(7 rows)

• \dt, considerando schemas diferentes do mesmo banco de dados (schemas meu_schema e public). Veja que, para pegar todas as informações do schema e não apenas uma tabela específica, podemos utilizar o caracter *:

postgres=# \dt meu_schema.*
              List of relations
   Schema   |   Name   | Type  |    Owner
------------+----------+-------+-------------
 meu_schema | exemplo2 | table | meu_usuario
(1 row)

postgres=# \dt public.*
           List of relations
 Schema |  Name   | Type  |    Owner
--------+---------+-------+-------------
 public | pessoas | table | meu_usuario
(1 row)

• \d <nome do objeto>:

postgres=# \d alunos;
                                    Table "public.alunos"
 Column |   Type            | Collation | Nullable |  Default
--------+------------------------+-----------+----------+---------------------------------
 id     | integer           |           | not null | nextval('alunos_id_seq'::regclass)
 nome   | char varying(255) |           | not null |
Indexes:
    "alunos_pkey" PRIMARY KEY, btree (id)

• \i <nome do arquivo>:

Vamos começar criando um arquivo com um script para ser executado no psql:

$ nano meu_script.sql

INSERT INTO alunos (nome) VALUES ('João'), ('Caio'), ('Felipe');

SELECT * FROM alunos;

UPDATE alunos SET nome = 'Filipe' WHERE id = 6;

SELECT * FROM alunos;

Agora, vamos mostrar como rodar esse script com o \i:

$ psql
psql (16.2 (Debian 16.2-1.pgdg120+2))
Type "help" for help.

postgres=# SELECT * FROM alunos;
 id |  nome
----+---------
  1 | Ludmila
  2 | Maria
  3 | Ana

postgres=# \i meu_script.sql
INSERT 0 3
 id |  nome
----+---------
  1 | Ludmila
  2 | Maria
  3 | Ana
  4 | João
  5 | Caio
  6 | Felipe
(6 rows)

UPDATE 1
 id |  nome
----+---------
  1 | Ludmila
  2 | Maria
  3 | Ana
  4 | João
  5 | Caio
  6 | Filipe
(6 rows)

No exemplo, é possível ver uma consulta de todos os dados da tabela alunos antes e depois do script rodar, adicionando três novos nomes e atualizando um deles (de “Felipe” para “Filipe”).

• \o (deve ser usado antes do início do conteúdo que se deseja direcionar para o arquivo e, depois, para informar ao psql quando parar de enviar a saída) e \t (para um conteúdo mais limpo no segundo arquivo gerado):

postgres=# \o alunos_nomes.txt
postgres=# SELECT * FROM alunos;
postgres=# \o
postgres=# \o alunos_nomes_t.txt
postgres=# \t
Tuples only is on.
postgres=# SELECT * FROM alunos;
postgres=# \o
postgres=# \q

$ ls
alunos_nomes.txt     alunos_nomes_t.txt      meu_script.sql     ...

$ cat alunos_nomes.txt
 id |  nome
----+---------
  1 | Ludmila
  2 | Maria
  3 | Ana
  4 | João
  5 | Caio
  6 | Filipe
(6 rows)

$ cat alunos_nomes_t.txt
  1 | Ludmila
  2 | Maria
  3 | Ana
  4 | João
  5 | Caio
  6 | Filipe

No exemplo, é possível ver o arquivo resultante da saída, chamado alunos_nomes.txt, com os títulos da coluna e informando o total de linhas e o alunos_nomes_t.txt, que foi rodado com o \t antes de finalizar a saída, trazendo um resultado mais limpo.

• \x (exemplos da saída de uma consulta na tabela pg_class, o primeiro com o \x desativado, e o segundo com ele ativado):

postgres=# SELECT * FROM pg_class;

  oid  |                    relname                     | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | relallvisible | reltoastrelid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasrules | relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity | relispopulated | relreplident | relispartition | relrewrite | relfrozenxid | relminmxid |                          relacl                          |       reloptions        | relpartbound
-------+------------------------------------------------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+----------------------------------------------------------+-------------------------+--------------
 16399 | minha_tabela_id_seq                            |         2200 |       0 |         0 |       10 |     0 |       16399 |             0 |        1 |         1 |             0 |             0 | f           | f           | p              | S       |        3 |         0 | f           | f              | f              | f              | f                   | t              | n            | f              |          0 |            0 |          0 |           
...   

postgres=# \x
postgres=# SELECT * FROM pg_class;

-[ RECORD 1 ]-------+---------------------------------------------------------
oid                 | 16399
relname             | minha_tabela_id_seq
relnamespace        | 2200
reltype             | 0
reloftype           | 0
relowner            | 10
relam               | 0
relfilenode         | 16399
reltablespace       | 0
relpages            | 1
reltuples           | 1
relallvisible       | 0
reltoastrelid       | 0
relhasindex         | f
relisshared         | f
relpersistence      | p
relkind             | S
relnatts            | 3
relchecks           | 0
relhasrules         | f
relhastriggers      | f
relhassubclass      | f
relrowsecurity      | f
relforcerowsecurity | f
relispopulated      | t
relreplident        | n
relispartition      | f
relrewrite          | 0
relfrozenxid        | 0
relminmxid          | 0
relacl              |
reloptions          |
relpartbound        |
...

Conclusão

O psql é uma ferramenta com muitas opções e facilidades. Aqui, vimos apenas uma amostra de todo o seu potencial. Ele é também uma ferramenta nativa, que vem distribuída junto com os pacotes originais do PostgreSQL. Isso significa que ele é mantido e atualizado com o mesmo rigor e cuidado que o servidor, o que o torna extremamente confiável e robusto. 
No entanto, usar o psql para escrever longas e complexas consultas pode ser desagradável, mas você pode utilizar o seu editor de textos favoritos para escrever seu código SQL e executá-lo no psql com a opção \i, porém, muitas vezes, parece mais cômodo utilizar uma ferramenta gráfica para isso. Sem preconceitos, se você pesquisar, vai descobrir que 10 entre 10 DBAs Postgres preferem utilizar o psql no dia a dia. Entre as qualidades que esses profissionais apontam na hora de usar o psql, podemos mencionar:

• Confiabilidade: o psql é desenvolvido e atualizado pela mesma equipe que desenvolve o PostgreSQL;
• Sempre atualizado: toda nova funcionalidade do PostgreSQL implica atualizações correspondentes no psql para lidar com elas;
• Robustez: como toda ferramenta em modo texto, a chance de ela quebrar quando rodar scripts SQL longos e pesados é muito baixa se comparada com qualquer outra ferramenta gráfica;
• Está sempre disponível em qualquer ambiente: muitas vezes, você não tem acesso à porta 5432 ou não pode se conectar remotamente a um banco de dados de produção com uma ferramenta gráfica a partir do seu desktop. No entanto, todo servidor PostgreSQL tem por padrão um psql instalado junto com ele;
• Facilidade de automatizar processos: o psql possui uma série de ferramentas para quem quer automatizar processos, criar jobs e outras tarefas do dia a dia de um DBA;
• Simplicidade e eficiência: depois que você vence a curva de aprendizado inicial do psql, você deixa de utilizar o mouse e começa a ser bastante produtivo nas suas tarefas do dia a dia.

Então, mesmo que você não seja um entusiasta de ferramentas em modo texto, é fundamental aprender a se virar minimamente com o psql, assim como com um editor de texto (como o nano ou o vi) no modo texto. Em situações de emergência ou quando precisar executar tarefas complexas, o psql será um aliado de primeira linha.