Terminal Interativo (psql) - manipulação de dados

PostgreSQL
Manipulando o Banco de Dados
Aula 03
Aula 03
Acessando um banco de dados
Apó
Após o banco de dados ter sido criado, pode ser acessado pela:
1) Execuç
Execução do programa de terminal interativo do PostgreSQL
chamado psql, que permite a entrada, ediç
edição e execuç
execução de
comandos SQL interativos.
2) Utilizaç
Utilização de uma ferramenta cliente grá
gráfica existente como o
PgAdmin,
PgAdmin, ou de um pacote de automaç
automação de escritó
escritórios com
suporte a ODBC para criar e manusear bancos de dados.
3) Criaç
Criação de aplicaç
aplicações personalizadas, usando uma das vá
várias
ligaç
ligações com linguagens disponí
disponíveis, como por exemplo o PHP.
Antes de usarmos o terminal interativo PSQL, vamos ter que criar uma
nova base de dados, utilizando o comando createdb no sistema
operacional, ou usar o comando create database de dentro do psql.
createdb –U postgres –h 127.0.0.1 nomebancodedados
1
Aula 03
Utilizando o PSQL
O psql é um cliente no modo terminal do PostgreSQL. Permite digitar
comandos interativamente, submetêsubmetê-los para o PostgreSQL e ver os
resultados. Como alternativa, a entrada pode vir de um arquivo. Alé
Além
disso, possui vá
vários metameta-comandos e diversas funcionalidades
semelhantes às da shell para facilitar a criaç
criação de scripts e automatizar
um grande nú
número de tarefas.
Aula 03
Terminal Interativo – PSQL - Opç
Opções
-a ou --echo
--echo--all
Exibe todas as linhas na tela ao serem lidas. É mais útil para o processamento de
scripts que no modo interativo. Equivale a definir a variá
variável ECHO como all.
all.
-A ou --no
--no--align
Muda para o modo de saí
saída não alinhado (De outra forma, o modo de saí
saída
padrão é o alinhado).
-c comando ou --command
--command comando
Especifica que o psql deve executar a cadeia de caracteres comando
comando e, em seguida,
terminar. Útil em scripts. O comando deve ser uma cadeia de caracteres que possa ser
integralmente analisada pelo servidor (ou seja, não conté
contém funcionalidades especí
específicas do
psql), ou ser um único comando de contrabarra.
contrabarra.
Portanto, não podem ser misturados comandos SQL com metameta-comandos do psql. Para
misturar, a cadeia de caracteres pode ser enviada para o psql conforme
conforme mostrado a seguir:
echo "\x \\ select * from foo;"
foo;" | psql. Se a cadeia de caracteres do comando conté
contém vá
vários
comandos SQL, estes são processados em uma única transaç
transação, a menos que existam
comandos BEGIN/COMMIT explí
explícitos incluí
incluídos na cadeia de caracteres para divididividi-la em
várias transaç
transações. Este comportamento é diferente do comportamento ocorrido quando a
mesma cadeia de caracteres é recebida pela entrada padrão do psql.
2
Aula 03
Terminal Interativo – PSQL - Opç
Opções
-d nome_bd ou --dbname
--dbname nome_bd
Especifica o nome do banco de dados a se conectar. Equivale a especificar
especificar nome_bd como o
primeiro argumento nãonão-opç
opção na linha de comando.
-e ou --echo
--echo--queries
Mostra todos os comandos enviados para o servidor. Equivale a definir
definir a variá
variável ECHO
como queries.
queries.
-E ou --echo
--echo--hidden
Exibe o comando verdadeiro gerado por \d e por outros comandos de contrabarra.
contrabarra. Pode ser
usado caso se deseje incluir uma funcionalidade semelhante nos pr
próprios programas.
Equivale a definir a variá
variável ECHO_HIDDEN a partir do psql.
-f nome_do_arquivo ou --file
--file nome_do_arquivo
Usa o arquivo nome_do_arquivo como origem dos comandos, em vez de ler os comandos
interativamente. Apó
Após o arquivo ser processado, o psql termina. Sob muitos aspectos
equivale ao comando interno \i. Se o nome_do_arquivo for - (hí
(hífen) a entrada padrão é lida.
O uso desta opç
opção é quase imperceptivelmente diferente de escrever psql <
nome_do_arquivo.
nome_do_arquivo. De uma maneira geral, as duas formas fazem o esperado, mas o uso
uso de
-f ativa algumas funcionalidades úteis, como mensagens de erro com o nú
número da linha. Ao
se usar esta opç
opção existe, també
também, uma pequenachance de reduzir o trabalho extra de
inicializaç
inicialização.
ão. Por outro lado, a utilizaç
utilização do redirecionamento da entrada (em teoria)
garante produzir exatamente a mesma saí
saída produzida quando tudo é digitado
manualmente.
Aula 03
Terminal Interativo – PSQL - Opç
Opções
-F separador ou --field
--field--separator separador
Usa separador como o separador de campos. Equivale a \pset fieldsep ou ao comando \f.
-h hospedeiro ou --host
--host hospedeiro
Especifica o nome de hospedeiro da má
máquina onde o servidor está
está executando. Se o nome
iniciar por uma barra (/) é usado como o diretó
diretório do soquete do domí
domínio Unix.
-H ou --html
--html
Ativa a saí
saída tabular HTML. Equivale a \pset format html ou ao comando \H.
-l ou --list
--list
Mostra todos os bancos de dados disponí
disponíveis e depois termina. Outras opç
opções que não
sejam de conexão são ignoradas. Semelhante ao comando interno \list.
list.
-o nome_do_arquivo ou --output
--output nome_do_arquivo
Envia a saí
saída de todos os comandos para o arquivo nome_do_arquivo.
nome_do_arquivo. Equivale ao
comando \o.
-p porta ou --port porta
Especifica a porta TCP, ou a extensão de arquivo do soquete do domínio Unix local, onde o
servidor está ouvindo as conexões. Por padrão o valor da variável de ambiente PGPORT ou,
se não estiver definida, a porta especificada durante a compilação, geralmente 5432.
3
Aula 03
Terminal Interativo – PSQL - Opç
Opções
-P atribuiç
atribuição ou --pset
--pset atribuiç
atribuição
Permite especificar opç
opções de exibiç
exibição no estilo \pset pela linha de comando. Observe que
aqui o nome e o valor devem estar separados pelo sinal de igual, em vez de espaç
espaço.
Portanto, para definir o formato de saí
saída como LaTeX deve ser escrito -P format=
format=latex.
latex.
-q ou --quiet
--quiet
Especifica que o psql deve trabalhar em silêncio. Por padrão, são
são exibidas mensagens de
boasboas-vindas e vá
várias outras mensagens informativas. Se esta opç
opção for usada, nada disso
acontece. É útil em conjunto com a opç
opção -c. Dentro do psql é possí
possível, també
também, definir a
variá
variável QUIET para produzir o mesmo efeito.
-R separador ou --record
--record--separator separador
Usa separador como o separador de registros. Equivale ao comando \pset recordsep.
recordsep.
-s ou --single
--single--step
Executa no modo passopasso-único, significando que será
será solicitada uma confirmaç
confirmação antes de
cada comando ser enviado para o servidor, com a opç
opção de cancelar a execuç
execução. Usado
para depurar scripts.
-S ou --single
--single--line
Executa no modo linhalinha-única, onde o caractere de novanova-linha termina o comando SQL,
como o pontoponto-e-vírgula faz.
Aula 03
Terminal Interativo – PSQL - Opç
Opções
-t ou --tuples
--tuples--only
Desativa a exibiç
exibição dos nomes das colunas, do rodapé
rodapé com o nú
número de linhas do
resultado, etc. É inteiramente equivalente ao metameta-comando \t.
-T opç
opções_de_tabela ou --table
--table--attr opç
opções_de_tabela
Permite especificar opç
opções a serem colocadas dentro da marca table do HTML. Veja \pset
para obter detalhes.
-u
Faz o psql solicitar o nome do usuá
usuário e a senha antes de conectar ao banco de dados. Esta
opç
opção está
está obsoleta, porque é conceitualmente incorreto (Solicitar um nome de usuá
usuário não
padrão e solicitar uma senha porque o servidor requer são realmente
realmente duas coisas
diferentes). EncorajaEncoraja-se o uso das opç
opções -U e -W em seu lugar.
-U nome_do_usuá
nome_do_usuário ou --username
--username nome_do_usuá
nome_do_usuário
Conecta ao banco de dados como o usuá
usuário nome_do_usuá
nome_do_usuário em vez do padrão (É
(É
necessá
necessário ter permissão para fazêfazê-lo, é claro).
-v atribuiç
atribuição ou --set
--set atribuiç
atribuição ou --variable
--variable atribuiç
atribuição
Realiza atribuiç
atribuição de variá
variável, como o comando interno \set. Observe que é necessá
necessário
separar o nome e o valor, se houver, por um sinal de igual na linha
linha de comando. Para
remover a definiç
definição de uma variá
variável omiteomite-se o sinal de igual. Para definir uma variá
variável sem
um valor, usausa-se o sinal de igual mas omiteomite-se o valor.
4
Aula 03
Terminal Interativo – PSQL - Opç
Opções
-V ou --version
--version
Mostra a versão do psql.
-W ou --password
--password
Requer que o psql solicite a senha antes de conectar ao banco de dados. Esta continuará
continuará
definida por toda a sessão, mesmo que a conexão ao banco de dados
dados seja mudada com o
metameta-comando \connect.
connect. Na versão corrente, o psql automaticamente solicita a senha
sempre que o servidor requer autenticaç
autenticação por senha. Como atualmente isto se baseia em
um hack,
hack, o reconhecimento automá
automático pode falhar misteriosamente e, por isso, existe esta
opç
opção para forç
forçar a solicitaç
solicitação. Se a solicitaç
solicitação da senha não for feita, e o servidor requer
autenticaç
autenticação por senha, a tentativa de conexão não será
será bembem-sucedida.
-x ou --expanded
--expanded
Ariva o modo formataç
formatação de tabela estendido Equivale ao comando \x.
-X ou --no
--no--psqlrc
Não lê o arquivo de inicializaç
inicialização ~/.psqlrc
~/.psqlrc..
-? ou --help
--help
Mostra a ajuda sobre os argumentos de linha de comando do psql.
Status de saí
saída
Aula 03
Terminal Interativo – PSQL - Opç
Opções
O psql retorna: 0 se terminar normalmente; 1 se ocorrer erro fatal
fatal pró
próprio (falta de
memó
memória, arquivo não encontrado); 2 se a conexão com o servidor teve problema e a
sessão não é interativa; 3 se ocorrer erro no script e a variá
variável ON_ERROR_STOP estiver
definida.
O psql é uma aplicaç
aplicação cliente do PostgreSQL comum. Para conectar a um banco de dados
dados
é necessá
necessário saber o nome do banco de dados, o nome do hospedeiro, o nú
número da porta
do servidor e o nome do usuá
usuário a ser usado para conectar. O psql pode ser informado
sobre estes parâmetros por meio das opç
opções de linha de comando -d, -h, -p e -U,
respectivamente. Se for encontrado um argumento que não pertenç
pertença a nenhuma opç
opção,
este será
será interpretado como o nome do banco de dados (ou o nome do usuá
usuário, se o nome
do banco de dados já
já tiver sido fornecido). Nem todas estas opç
opções são necessá
necessárias, os
padrões se aplicam. Se for omitido o nome do hospedeiro, então o psql se conecta atravé
através
do soquete do domí
domínio Unix ao servidor no hospedeiro local. O nú
número padrão para a porta
é determinado na compilaç
compilação. Uma vez que o servidor de banco de dados usa o mesmo
padrão, não é necessá
necessário especificar a porta na maioria dos casos. O nome de usuá
usuário
padrão é o nome do usuá
usuário do Unix, como també
também é o nome do banco de dados padrão.
Observe que não é possí
possível se conectar a qualquer banco de dados com qualquer nome de
usuá
usuário. O administrador de banco de dados deve informar as permissões
permissões de acesso
concedidas. Para evitar a digitaç
digitação, podem ser definidas as variá
variáveis de ambiente
PGDATABASE, PGHOST, PGPORT e PGUSER com os valores apropriados.
5
Aula 03
Terminal Interativo – PSQL - Opç
Opções
Se a conexão não puder ser estabelecida por algum motivo (por exemplo,
exemplo, privilé
privilégios
insuficientes, o servidor não está
está executando no hospedeiro, etc.), o psql retorna uma
mensagem de erro e termina.
Entrando com comandos SQL
No modo normal de operaç
operação, o psql exibe um prompt com o nome do banco de dados ao
qual está
está conectado, seguido pela cadeia de caracteres =>. Por exemplo:
$ psql testedb
Welcome to psql 7.4.5, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
testedb=>
testedb=>
No prompt o usuá
usuário pode digitar comandos SQL. Normalmente, as linhas de entrada são
enviadas para o servidor quando o caractere pontoponto-e-vírgula, que termina o comando, é
encontrado. Um caractere de fimfim-dede-linha não termina o comando. Portanto, os comandos
podem ser distribuí
distribuídos por vá
várias linhas para clareza. Se o comando for enviado e
executado sem erro, o resultado do comando será
será exibido na tela.
Aula 03
Terminal Interativo – PSQL - Opç
Opções
MetaMeta-comandos
Qualquer texto digitado no psql começ
começando por uma contrabarra (\) (não entre apó
apóstrofos,
') é um metameta-comando do psql processado pelo pró
próprio psql. Estes comandos tornam o
psql interessante para administraç
administração e para scripts. Os metameta-comandos são geralmente
chamados de comandos de barra ou de contrabarra.
contrabarra.
O formato de um comando psql é a contrabarra,
contrabarra, seguida imediatamente por um verbo
comando, e depois pelos argumentos. Os argumentos são separados do verbo comando, e
entre si, por qualquer nú
número de caracteres de espaç
espaço.
Para incluir caracteres de espaç
espaço no argumento devedeve-se colocá
colocá-los entre apó
apóstrofos ('). Para
incluir um apó
apóstrofo neste tipo de argumento, devedeve-se precedêprecedê-lo por uma contrabarra.
contrabarra.
Qualquer texto entre apó
apóstrofos está
está sujeito às substituiç
substituições no estilo C para \n (nova(novainha),
inha), \t (tabulaç
(tabulação), \dígitos, \0dí
0dígitos e \0xdí
0xdígitos (o caractere com o có
código decimal,
octal ou hexadecimal especificado).
Se um argumento (não entre apó
apóstrofos) começ
começar por doisdois-pontos (:), será
será considerado
como sendo uma variá
variável e o valor desta variá
variável será
será usado como o argumento.
6
Aula 03
Terminal Interativo – PSQL – Meta dados
Os seguintes metameta-comandos estão definidos:
\a
Se o formato atual de saí
saída da tabela for não alinhado, muda para alinhado. Se não for não
não
alinhado, define como não alinhado. Este comando é mantido para compatibilidade com as
versões anteriores. Veja \pset para uma soluç
solução geral.
\cd [diretó
[diretório]
Muda o diretó
diretório de trabalho corrente para diretó
diretório. Sem argumento, muda para o diretó
diretório
home do usuá
usuário corrente. Dica: Para ver o diretó
diretório de trabalho corrente deve ser
usado \!pwd.
pwd.
\C [ tí
título ]
Define o tí
título de qualquer tabela mostrada como resultado de uma consulta, ou remove a
definiç
definição deste tí
título. Este comando equivale a \pset titletí
titletítulo (O nome deste comando
deriva de "caption
"caption"" (tí
(título), porque anteriormente só
só era usado para definir tí
título em tabela
no HTML).
Aula 03
Terminal Interativo – PSQL – Meta dados
\connect (ou \c) [ nome_bd [ nome_do_usuá
nome_do_usuário ] ]
Estabelece a conexão com um banco de dados novo e/ou sob um usuá
usuário novo. A conexão
anterior é fechada. Se nome_bd for - (hí
(hífen), então é utilizado o nome do banco de dados
corrente. Se o nome_do_usuá
nome_do_usuário for omitido, então o nome do usuá
usuário corrente é utilizado.
Como regra especial, \connect sem nenhum argumento conecta ao banco de dados padrão
com o usuá
usuário padrão (da mesma forma que aconteceria se o psql fosse executado
executado sem
argumentos). Se a tentativa de conexão não for bembem-sucedida (nome de usuá
usuário errado,
acesso negado, etc.), a conexão anterior será
será mantida se, e somente se, o psql estiver no
modo interativo. Ao executar um script não interativo, o processamento
processamento será
será imediatamente
interrompido com erro. Esta distinç
distinção foi escolhida por ser mais conveniente para o usuá
usuário
que digita menos, e como um mecanismo de seguranç
segurança impedindo os scripts atuarem
acidentalmente no banco de dados errado.
\copy tabela [ ( lista_de_colunas ) ] { from | to } nome_do_arquivo | stdin |
stdout [ with ] [ oids ] [ delimiter [as] 'caractere' ] [ null [as] 'cadeia_de_caracteres
'cadeia_de_caracteres'' ]
Executa uma có
cópia pelo cliente. Esta é uma operaç
operação que executa o comando SQL COPY,
mas em vez do servidor ler ou escrever no arquivo especificado, o psql lê ou escreve no
arquivo e roteia os dados entre o servidor e o sistema de arquivos local. Isto significa
significa que a
acessibilidade ao arquivo e os privilé
privilégios são do usuá
usuário local, e não do servidor, e que não
há necessidade dos privilé
privilégios do superusuá
superusuário.
rio. A sintaxe deste comando é semelhante à
sintaxe do comando SQL COPY. Observe que, por isso, regras especiais
especiais de aná
análise se
aplicam ao comando \copy.
copy. Em particular, as regras de substituiç
substituição de variá
variável e de
escapes de contrabarra (\) não se aplicam.
7
Aula 03
Terminal Interativo – PSQL – Meta dados
\copyright
Mostra os termos da distribuiç
distribuição e dos direitos autorais do PostgreSQL.
\d [ padrão ]
Para cada relaç
relação (tabela, visão, índice ou seqü
seqüência) correspondendo ao padrão, mostra
todas as colunas, seus tipos e atributos especiais como NOT NULL ou o valor padrão, se
houver. Os índices, restriç
restrições, regras e gatilhos associados també
também são mostrados, assim
como a definiç
definição da visão se a relaç
relação for uma visão (A "Correspondência com padrão" é
definida abaixo). A forma \d+ do comando é idêntica, mas todos os comentá
comentários associados
às colunas da tabela també
também são mostrados.
Nota: Se \d for utilizado sem o argumento padrão, tornatorna-se equivalente a \dtvs,
dtvs, mostrando
a lista de todas as tabelas, visões e seqü
seqüências. Isto é puramente uma medida de
conveniência.
\da [ padrão ]
Mostra todas as funç
funções de agregaç
agregação disponí
disponíveis, junto com o tipo de dado com que
operam. Se padrão for especificado, somente as agregaç
agregações cujos nomes correspondem ao
padrão são mostradas.
\dc [ padrão ]
Mostra todas as conversões entre codificaç
codificações de conjunto de caracteres disponí
disponíveis. Se
padrão for especificado, somente as conversões cujos nomes correspondem
correspondem ao padrão são
mostradas.
Aula 03
Terminal Interativo – PSQL – Meta dados
\dC
Mostra todas as conversões de tipo disponí
disponíveis.
\dd [ padrão ]
Mostra a descriç
descrição dos objetos cujos nomes correspondem ao padrão, ou todos os objetos
objetos
visí
visíveis se nenhum argumento for especificado. Nos dois casos somente
somente os objetos que
possuem uma descriç
descrição são mostrados ("Objeto" compreende agregaç
agregações, funç
funções,
operadores, tipos, relaç
relações [tabelas, visões, índices, seqü
seqüências e objetos grandes], regras e
gatilhos). Por exemplo:
exemplo:
=> \dd version
Object descriptions
Schema | Name | Object |
Description
------------+
------------+---------+
---------+----------+
----------+--------------------------pg_catalog | version | function | PostgreSQL version string
(1 linha)
As descriç
descrições dos objetos podem ser criadas pelo comando SQL COMMENT ON.
\dD [ padrão ]
Mostra todos os domí
domínios disponí
disponíveis. Se padrão for especificado, somente os domí
domínios
cujos nomes correspondem ao padrão são mostrados.
8
Aula 03
Terminal Interativo – PSQL – Meta dados
\df [ padrão ]
Mostra as funç
funções disponí
disponíveis, junto com o tipo de dado de seus argumentos e do valor
retornado. Se padrão for especificado, somente as funç
funções cujos nomes correspondem ao
padrão são mostradas. Se a forma \df+
df+ for usada, informaç
informações adicionais sobre cada
funç
função, incluindo a linguagem e a descriç
descrição, são mostradas.
Nota: Para diminuir a confusão, \df não mostra as funç
funções com tipo de dado I/O. Isto é
implementado ignorando as funç
funções que recebem ou retornam o tipo cstring.
cstring.
\distvS [ padrão ]
Este não é na verdade o nome do comando: As letras i, s, t, v, S correspondem
correspondem a índice,
seqü
seqüência, tabela, visão e tabela do sistema, respectivamente. Pode ser especificada
qualquer uma ou todas as letras, em qualquer ordem, para obter a relaç
relação de todos os
objetos correspondentes. A letra S restringe a listagem aos objetos
objetos do sistema; sem o S,
somente os objetos que não são do sistema são mostrados. Se o caractere
caractere "+" for anexado
ao nome do comando, cada objeto é mostrado junto com sua descriç
descrição associada, se
houver. Se o padrão for especificado, somente os objetos cujos nomes
nomes correspondem ao
padrão são mostrados.
\dl
Este é um outro nome para \lo_list,
lo_list, que mostra a lista dos objetos grandes.
Aula 03
Terminal Interativo – PSQL – Meta dados
\dn [ padrão ]
Mostra todos os esquemas (espaç
(espaços de nomes) disponí
disponíveis. Se padrão (uma expressão
regular) for especificado, somente os esquemas cujos nomes correspondem
correspondem ao padrão são
mostrados.
\do [ padrão ]
Mostra os operadores disponí
disponíveis junto com o tipo de seus operandos e do valor retornado.
Se padrão for especificado, somente os operadores cujos nomes correspondem
correspondem ao padrão
são mostrados.
\dp [ padrão ]
Produz uma lista contendo todas as tabelas disponí
disponíveis junto com seus privilé
privilégios de acesso
associados. Se padrão for especificado, somente as tabelas cujos nomes correspondem ao
padrão são mostradas. Os comandos GRANT e REVOKE são utilizados para definir privilé
privilégios
de acesso. Veja o comando GRANT para obter mais informaç
informações.
\dT [ padrão ]
Mostra todos os tipos de dado, ou somente aqueles que correspondem
correspondem ao padrão. A forma
\dT+
dT+ do comando mostra informaç
informações adicionais.
\du [ padrão ]
Mostra todos os usuá
usuários do banco de dados, ou somente aqueles que correspondem ao
padrão.
9
Aula 03
Terminal Interativo – PSQL – Meta dados
\edit (ou \e) [ nome_do_arquivo ]
Se o nome_do_arquivo for especificado, o arquivo é editado; apó
após o fim da execuç
execução do
editor, o conteú
conteúdo do arquivo é copiado de volta para o buffer de comando. Se nenhum
argumento for fornecido, o buffer de comando corrente é copiado para um arquivo
temporá
temporário, que é editado de forma idêntica. O novo buffer de comando é então analisado
novamente de acordo com as regras normais do psql, onde o todo buffer
buffer é tratado como
sendo uma única linha (Portanto, não podem ser gerados scripts dessa forma. Use o
comando \i para fazer isso). Isto significa també
também que, se o comando terminar por (ou
contiver) um pontoponto-e-vírgula, será
será executado imediatamente. Se não contiver, apenas
permanecerá
permanecerá aguardando no buffer de comando.
Dica: O psql procura nas variá
variáveis de ambiente PSQL_EDITOR, EDITOR e VISUAL (nesta
ordem) o editor a ser usado. Se nenhuma delas estiver definida, então /bin
/vi é executado.
/bin/vi
\echo texto [ ... ]
Envia os argumentos para a saí
saída padrão, separados por um espaç
espaço e seguido por um
caractere de novanova-linha, o que pode ser útil para intercalar informaç
informações na saí
saída dos scripts.
Por exemplo:
=> \echo `date`
Tue Oct 26 21:40:57 CEST 1999
Se o primeiro argumento for -n (não entre apó
apóstrofos) o caractere de novanova-linha final não é
enviado. Dica: Se for usado o comando \o para redirecionar a saí
saída do comando, talvez se
deseje utilizar \qecho em vez deste comando.
Aula 03
Terminal Interativo – PSQL – Meta dados
\encoding [ codificaç
codificação ]
Define a codificaç
codificação do conjunto de caracteres do cliente. Sem argumento, este comando
comando
mostra a codificaç
codificação corrente.
\f [ cadeia_de_caracteres ]
Define o separador de campos para a saí
saída de comando não alinhada. O padrão é a barra
vertical (|). Veja també
também em \pset uma forma gené
genérica de definir opç
opções de saí
saída.
\g [ { nome_do_arquivo | |comando
|comando } ]
Envia o buffer de entrada de comando corrente para o servidor e, opcionalmente, salva a
saí
saída em nome_do_arquivo,
nome_do_arquivo, ou envia a saí
saída para outro interpretador de comandos do Unix
para executar o comando. Um \g puro e simples é virtualmente equivalente ao pontoponto-evírgula. Um \g com argumento é uma alternativa "de uma só
só vez" para o comando \o.
\help (ou \h) [ comando ]
Fornece ajuda de sintaxe para o comando SQL especificado. Se o comando
comando não for
especificado, então o psql mostra todos os comandos para os quais
quais a ajuda de sintaxe está
está
disponí
disponível. Se comando for um asterisco ("*"), então é mostrada a ajuda de sintaxe para
todos os comandos SQL.
Nota: Para simplificar a digitaç
digitação, os comandos compostos por vá
várias palavras não
necessitam estar entre apó
apóstrofos. Portanto, pode ser digitado \help alter table.
table.
10
Aula 03
Terminal Interativo – PSQL – Meta dados
\H
Ativa o formato HTML de saí
saída da consulta. Se o formato HTML já
já estiver habilitado,
retorna ao formato de texto alinhado padrão. Este comando existe por compatibilidade e
conveniência, mas veja em \pset a definiç
definição de outras opç
opções de saí
saída.
\i nome_do_arquivo
Lê a entrada no arquivo nome_do_arquivo,
nome_do_arquivo, e executa como se tivesse sido digitada pelo
teclado.
Nota: Se for desejado ver as linhas na tela ao serem lidas, deve ser definida a variá
variável
ECHO como all.
all.
\l (ou \list)
list)
Mostra o nome, dono e codificaç
codificação do conjunto de caracteres de todos oa bancos de dados
do servidor. Deve ser adicionado + ao nome do comando para ver, també
também, todas as
descriç
descrições dos bancos de dados.
\lo_export loid nome_do_arquivo
Lê no banco de dados o objeto grande com OID igual a loid,
loid, e escreve em
nome_do_arquivo.
nome_do_arquivo. Observe que isto é quase imperceptivelmente diferente da funç
função do
servidor lo_export,
lo_export, que atua com a permissão do usuá
usuário como o qual o servidor de banco
de dados processa, e no sistema de arquivos do servidor.
Dica: Use \lo_list para descobrir os OIDs dos objetos grandes.
Aula 03
Terminal Interativo – PSQL – Meta dados
\lo_import nome_do_arquivo [ comment ]
Armazena o arquivo em um objeto grande do PostgreSQL. Opcionalmente,
Opcionalmente, associa o
comentá
comentário fornecido junto com o objeto. Exemplo:
foo=>
foo=> \lo_import '/home
'/home//peter/
peter/pictures/
pictures/photo.
photo.xcf'
xcf' 'uma fotografia minha'
lo_import 152801
A resposta indica que o objeto grande recebeu o identificador de objeto 152801, que deve
ser lembrado para acessar o objeto novamente. Por esta razão, recomenda
recomenda--se associar
sempre um comentá
comentário inteligí
inteligível a cada objeto. Estes podem ser vistos utilizando o
comando \lo_list.
lo_list. Observe que este comando é quase imperceptivelmente diferente da
funç
função lo_import do servidor, porque atua como o usuá
usuário local no sistema de arquivos
local, em vez do usuá
usuário do servidor no sistema de arquivos do servidor.
\lo_list
Mostra a lista de todos os objetos grandes do PostgreSQL armazenados
armazenados neste instante no
banco de dados, junto com os comentá
comentários fornecidos para os mesmos.
\lo_unlink loid
Remove do banco de dados o objeto grande com o OID igual a loid.
loid.
Dica: Use \lo_list para descobrir os OIDs dos objetos grandes.
11
Aula 03
Terminal Interativo – PSQL – Meta dados
\o [ {nome_do_arquivo
}]
{nome_do_arquivo | |comando
|comando}
Salva os resultados dos pró
próximos comandos no arquivo nome_do_arquivo,
nome_do_arquivo, ou envia os
pró
próximos resultados para um outro interpretador de comandos do Unix para executar o
comando. Se nenhum argumento for especificado, a saí
saída do comando será
será redefinida para
a saí
saída padrão. Os "resultados dos comandos" incluem todas as tabelas,
tabelas, respostas dos
comandos e notificaç
notificações obtidas do servidor de banco de dados, assim como a saí
saída dos
vários comandos de contrabarra que consultam o banco de dados (como o \d), mas não as
mensagens de erro.
Dica: Para intercalar saí
saída de texto entre os resultados dos comandos deve ser utilizado
\qecho.
qecho.
\p
Envia o buffer de comando corrente para a saí
saída padrão.
\pset parâmetro [ value ]
Este comando define opç
opções que afetam a saí
saída das tabelas de resultado das
consultas. O parâmetro indica qual opç
opção será
será definida. A semântica do valor
depende do parâmetro. As opç
opções ajustá
ajustáveis de exibiç
exibição são: format,
format, border,
border, etc...
- Veja nos pró
próximos slides
Aula 03
Terminal Interativo – PSQL – Meta dados
format
Define o formato de saí
saída como unaligned (não alinhado), aligned (alinhado), html ou latex.
latex.
Formas abreviadas únicas são permitidas (O que equivale a dizer que uma letra basta).
basta). O
modo "Unaligned
"Unaligned"" escreve todos as colunas em uma linha, separadas pelo separador
separador de
campos ativo no momento. PretendePretende-se com isso poder criar uma saí
saída que sirva de
entrada para outro programa (separada por tabulaç
tabulação, por ví
vírgula, etc.). O modo "Aligned
"Aligned““
é a saí
saída de texto padrão, inteligí
inteligível e agradavelmente formatada. Os modos "HTML" e
"LaTeX"
LaTeX" produzem tabelas feitas para serem incluí
incluídas em documentos usando a linguagem
de marcaç
marcação correspondente. Não são documentos completos! (Isto não é tão problemá
problemático
no HTML, mas no LaTeX deve haver um invó
invólucro completo do documento).
border
O segundo argumento deve ser um nú
número. Em geral, quanto maior o nú
número mais bordas
e linhas a tabela terá
terá, mas isto depende do formato. No modo HTML será
será traduzido
diretamente para o atributo border=...,
border=..., nos outros modos somente os valores 0 (sem
borda), 1 (linhas divisó
divisórias internas) e 2 (moldura da tabela) fazem sentido.
expanded (ou x)
Alterna entre os formatos regular e expandido. Quando o formato expandido está
está ativo,
todas as saí
saídas possuem duas colunas, com o nome da coluna à esquerda e o dado à
direita. Este modo é útil quando os dados não cabem na tela no modo normal "horizontal".
"horizontal".
O modo expandido é suportado por todos os quatro modos de saí
saída.
12
Aula 03
Terminal Interativo – PSQL – Meta dados
null
O segundo argumento é a cadeia de caracteres a ser mostrada sempre que o campo for
nulo. O padrão é não mostrar nada, que pode ser facilmente confundido com, por exemplo,
exemplo,
uma cadeia de caracteres vazia. Portanto, pode ser preferido escrever
escrever \pset null '(nulo)'.
fieldsep
Especifica o separador de campos a ser utilizado no modo de saí
saída não alinhado. Desta
forma pode ser criada, por exemplo, uma saí
saída separada por tabulaç
tabulação, por ví
vírgula ou por
um outro caractere, conforme o programa preferir. Para definir o caractere de tabulaç
tabulação
como separador de campo deve ser usado \pset fieldsep '\t'. O separador de campos padrão
é o '|' (a barra vertical).
footer
Alterna a exibiç
exibição do rodapé
rodapé padrão (x linhas).
recordsep
Especifica o separador de registro (linha) a ser usado no modo de
de saí
saída não alinhado. O
padrão é o caractere de novanova-linha.
Aula 03
Terminal Interativo – PSQL – Meta dados
tuples_only (ou t)
Alterna entre mostrar somente as tuplas e mostrar tudo. O modo mostrar tudo pode
mostrar informaç
informações adicionais como os cabeç
cabeçalhos das colunas, tí
títulos e vá
vários rodapé
rodapés.
No modo tuplastuplas-apenas somente os dados da tabela são mostrados.
title [ texto ]
Define o tí
título para as pró
próximas tabelas mostradas. Pode ser usado para colocar textos
descritivos na saí
saída. Se nenhum argumento for fornecido, o tí
título é removido.
tableattr (ou T) [ texto ]
Permite especificar qualquer atributo a ser colocado dentro da marca
marca table do HTML. Estes
atributos podem ser, por exemplo, cellpadding ou bgcolor.
bgcolor. Observe que, provavelmente,
não será
será desejado especificar border aqui, porque isto já
já é tratado pelo \pset border.
border.
pager
Controla o uso do paginador para comandos e para a saí
saída da ajuda do psql. Se a variá
variável
de ambiente PAGER estiver definida, a saí
saída é enviada para o programa especificado, senão
o padrão dependente da plataforma é utilizado (como o more). Quando o paginador está
está
inativo, a paginaç
paginação não é feita. Quando o paginador está
está ativo, a paginaç
paginação é feita
somente quando for apropriado, ou seja, a saí
saída é para um terminal e não cabe na tela. O
(psql não realiza um trabalho perfeito ao avaliar quando o paginador
paginador deve ser utilizado).
\pset pager ativa e desativa o paginador. O paginador també
também pode ser definido como
always,
always, o que faz o paginador ser sempre utilizado.
13
Aula 03
Terminal Interativo – PSQL – Meta dados
Existem vá
vários comandos abreviados para o \pset.
pset. Veja \a, \C, \H, \t, \T e \x.
Nota: É errado chamar o \pset sem argumentos. No futuro, esta chamada deverá
deverá mostrar o
status corrente de todas as opç
opções de exibiç
exibição.
\q
Sair da aplicaç
aplicação psql.
\qecho texto [ ... ]
Este comando é idêntico a \echo,
echo, exceto que toda a saí
saída é enviada para o canal de saí
saída
de comando, definido por \o.
\r
Restaura (limpa) o buffer de comando.
\s [ nome_do_arquivo ]
Mostra ou salva o histó
histórico da linha de comando em nome_do_arquivo.
nome_do_arquivo. Se
nome_do_arquivo for omitido, o histó
histórico é enviado para a saí
saída padrão. Esta opç
opção
somente estará
estará disponí
disponível se o psql estiver configurado para usar a biblioteca de histó
histórico
GNU.
Nota: Na versão corrente não é mais necessá
necessário salvar o histó
histórico dos comandos, porque
isso é feito automaticamente quando o programa termina. O histó
histórico també
também é carregado,
automaticamente, toda vez que o psql inicia.
Aula 03
Terminal Interativo – PSQL – Meta dados
\set [ nome [ value [ ... ]]]
Define a variá
variável interna nome com o valor ou, se mais de um valor for fornecido,
fornecido, com a
concatenaç
concatenação de todos os valores. Se o segundo argumento não for fornecido,
fornecido, a variá
variável é
definida sem valor. Para remover a definiç
definição da variá
variável deve ser usado o comando \unset.
unset.
Nomes de variá
variáveis vá
válidos podem conter letras, dí
dígitos e sublinhados (_). Veja a seç
seção
Variá
Variáveis abaixo para obter detalhes. Embora possa ser definida qualquer
qualquer variá
variável como
qualquer coisa desejada, o psql trata vá
várias variá
variáveis como sendo especiais. Elas estão
documentadas na seç
seção sobre variá
variáveis.
Nota: Este comando é totalmente distinto do comando SQL SET.
\t
Alterna a exibiç
exibição do cabeç
cabeçalho contendo o nome das colunas e do rodapé
rodapé contendo o
número de linhas. Este comando equivale a \pset tuples_only,
tuples_only, sendo fornecido por
conveniência.
\T opç
opções_de_tabela
Permite especificar atributos a serem colocados na marca table no modo de saí
saída tabular
HTML. Este comando equivale a \pset tableattr opç
opções_de_tabela.
ões_de_tabela.
\timing
Alterna a exibiç
exibição de quanto tempo cada comando SQL leva, em milissegundos.
milissegundos.
14
Aula 03
Terminal Interativo – PSQL – Meta dados
\w {nome_do_arquivo | |command
}
|command}
Escreve o buffer de comando corrente no arquivo nome_do_arquivo,
nome_do_arquivo, ou envia para o
comando Unix comando atravé
através de um pipe.
pipe.
\x
Alterna o modo de formataç
formatação de tabela estendido. Sendo assim, equivale a \pset
expanded.
expanded.
\z [ padrão ]
Produz uma lista contendo todas as tabelas disponí
disponíveis, junto com seus privilé
privilégios de
acesso. Se padrão for especificado, somente as tabelas cujos nomes
nomes correspondem ao
padrão são listadas. Os comandos GRANT e REVOKE são utilizados para
para definir os privilé
privilégios
de acesso. Veja o comando GRANT para obter mais informaç
informações. Este comando é um outro
nome para o comando \dp ("display privileges").
privileges").
\! [ comando ]
Abre um outro interpretador de comandos do Unix, ou executa o comando
comando Unix comando.
Os argumentos não são mais interpretados, sendo enviados para o interpretador de
comandos como estão.
\?
Mostra informaç
informação de ajuda para os comandos de contrabarra ("\
("\").
Aula 03
Utilizando o PSQL
A última linha exibida pelo psql é o prompt,
prompt, indicando que o psql está
está aguardando você,
e que você pode digitar comandos SQL dentro do espaç
espaço de trabalho mantido pelo psql.
Tente usar estes comando:
template1=> SELECT version();
version();
version
---------------------------------------------------------------PostgreSQL 8.0.0beta3 on i686i686-pcpc-mingwn32, compiled by GCC 3.2.3
(1 row)
row)
template1=> SELECT current_date;
current_date;
date
-----------20042004-1010-21
(1 row)
row)
template1=> SELECT 2 + 2;
?column?
column?
---------4
(1 row)
row)
15
Aula 03
Manipulando Base de Dados – PSQL
Manipulando a base de dados passo a passo.
1) Criar uma tabela chamada filmes
template1=> create table filmes (
nome varchar(40),
varchar(40),
ano smallint,
smallint,
diretor varchar(30));
varchar(30));
CREATE
template1=>
No exemplo foi criada uma tabela de nome filmes com os campos:
nome, tipo varchar com tamanho má
máximo de 40.
ano, tipo smallint.
smallint.
diretor, tipo varchar com tamanho má
máximo de 30 caracteres.
Aula 03
Manipulando Base de Dados – PSQL
2) Inserir dados dentro desta tabela
template1=> insert into filmes values
template1template1-> ('Morango e Chocolate',1993,'Tomas Gutierrez');
INSERT 18433 1
template1=> insert into filmes values
template1template1-> (‘
(‘Guerra nas Estrelas I',1980,‘
I',1980,‘Spilberg');
Spilberg');
INSERT 18434 1
template1=> insert into filmes values
template1template1-> (‘
(‘Guerra nas Estrelas II',1980,‘
II',1980,‘Spilberg');
Spilberg');
INSERT 18435 1
A sintaxe do comando é:
INSERT INTO table VALUES (val1,val2,...,valN
);
(val1,val2,...,valN);
Note que as strings devem ser delimitadas por aspas simples.
16
Aula 03
Manipulando Base de Dados – PSQL
Para acessar os dados da tabela precisamos fazer uma query.
query.
template1=> select * from filmes;
nome
| ano |diretor
----------------------------------+
----------------------------------+------+
------+--------------------Morango e Chocolate
|1993|Tomas Gutierrez
Guerra nas Estrelas I
|1990|Spilberg
|1990|Spilberg
Guerra nas Estrelas II
|1990|Spilberg
|1990|Spilberg
(3 rows)
rows)
Usamos o comando Select para fazer as consultas.
SELECT campo1,campo2,...,campoN
campo1,campo2,...,campoN FROM table;
table;
Veja outros exemplo:
template1=> select nome,ano from filmes;
nome
| ano
----------------------------------+
----------------------------------+---Morango e Chocolate
|1993
Guerra nas Estrelas I
|1990
Guerra nas Estrelas II
|1990
(3 rows)
rows)
Aula 03
Manipulando Base de Dados – PSQL
template1=> select * from filmes where diretor=‘
diretor=‘Spilberg';
Spilberg';
nome
| ano |diretor
----------------------------------+
----------------------------------+-----+
-----+----------Guerra nas Estrelas I
|1990|Spilberg
|1990|Spilberg
Guerra nas Estrelas II
|1990|Spilberg
|1990|Spilberg
(2 rows)
rows)
No segundo exemplo vemos a clá
cláusula where que serve para selecionar os registros que
queremos ver. A sintaxe é
WHERE campo1 operador valor1,..., campoN operador valorN
Outra claú
claúsula interessante é a ORDER BY que permite mostrar uma tabela ordenada por
um campo. Veja o exemplo:
template1=> select * from filmes order by nome;
nome
| ano |diretor
-----------------------------------+
-----------------------------------+-----+
-----+--------------------Guerra nas Estrelas I
|1990|Spilberg
|1990|Spilberg
Guerra nas Estrelas II
|1990|Spilberg
|1990|Spilberg
Morango e Chocolate
|1993|Tomas Guitierrez Alea
(3 rows)
rows)
17
Aula 03
Manipulando Base de Dados – PSQL
Cometemos um erro nos dados que entramos, ``Guerra nas Estrelas II'' não foi feito no
mesmo ano que o primeiro. Para corrigir isso vamos executar o seguinte
seguinte comando
template1=> update filmes set ano=1991
where nome='Guerra nas Estrelas II';
UPDATE 1
A sintaxe do UPDATE é
UPDATE table name SET campo1=valor1,..., campoN=
campoN=valorN
A claú
claúsula WHERE do UPDATE é igual à do SELECT. Ela é muito importante pois diz para o
postgres quais registros devem ser modificados, se você não colocacoloca-la ele modifica todos os
registros. Veja abaixo como a tabela ficou depois do UPDATE:
template1=> select * from filmes;
nome
| ano |diretor
---------------------------------+
---------------------------------+------+
------+--------------------Morango e Chocolate
|1993 |Tomas Gutierrez
Guerra nas Estrelas I
|1990 |Spilberg
|Spilberg
Guerra nas Estrelas II
|1991 |Spilberg
|Spilberg
(3 rows)
rows)
Aula 03
Manipulando Base de Dados – PSQL
Muito parecido com o comando UPDATE é o comando DELETE. Nele també
também claú
claúsula
WHERE é muito importante, se você esqueceesquece-la vai apagar todo o seu banco de dados, por
isso digitedigite-o com cuidado.
template1=> delete from filmes where diretor='Tomas Gutierrez';
DELETE 1
A sintaxe do DELETE é muito simples, novamente a clausula WHERE é igual ao do SELECT.
Note que eu não a coloquei na sintaxe do comando. É porque ela não é obrigató
obrigatória, apesar
de raramente o DELETE ser usado em ela.
DELETE FROM table name
Esses foram os comandos bá
básicos da linguagem SQL. Agora vamos ver alguns tó
tópicos mais
avanç
avançados. O primeiro deles serão os índices. Eles são muito importantes pois tornam as
querys com clausú
clausúla WHERE e ORDER BY muito mais rá
rápidas. Você pode indexar qualquer
campo de uma tabela, só
só que quanto mais campos você indexar mais lenta ficam as
operaç
operações INSERT e UPDATE. Vamos então indexar o campo nome.
template1=> create unique index filmes_nome on filmes (nome);
CREATE
18
Aula 03
Manipulando Base de Dados – PSQL
A sintaxe de CREATE INDEX é
CREATE INDEX index name ON table name (campo)
No nosso exemplo usamos a claú
claúsula opcional unique que faz com que o postgres não
aceite informaç
informações repetidas no campo indexado. Ou seja na nossa tabela não pode
pode haver
dois filmes com o mesmo nome. Claro que isso não faria sentido no
no campo ano.
Vamos supor que você comprou todos os filmes do Spilberg e quer cadastracadastra-los no seu
banco de dados. Em cada um dos registros você vai ter digitar o nome do diretor, se mais
e uma pessoa for digitar os registros uma pode digitar ‘‘Steven
‘‘Steven Spilberg’’
Spilberg’’,, outro digita
somente ‘‘Spilberg
‘‘Spilberg’’’’,, da para imaginar o problema que você teria para fazer uma query que
mostrasse todos os filmes do Spilberg.
Spilberg. Uma soluç
solução para esse problema é você criar duas
tabelas, uma para os filmes e outra para os diretores, e relacionar
relacionar as duas por um campo,
de preferência numé
numérico. Apague a tabela que você criou com comando:
template1=> drop table filmes;
DROP
E crie uma nova tabela filmes
template1=> create table filmes (nome varchar(40),
varchar(40), ano int, diretor int);
CREATE
Aula 03
Manipulando Base de Dados – PSQL
Note que o campo diretor agora é um numero inteiro. Como vamos ter uma tabela só
só para
diretores, podemos por informaç
informações ligadas ao diretor nessa tabela, como por exemplo, a
nacionalidade dele. Veja que podemos por quantas informaç
informações quisermos na tabela de
diretores que ela não vai se repetir em cada registro da tabela filmes, assim economizamos
espaç
espaço em disco e evitamos informaç
informações redundantes.
template1=> create table diretores (id int, nome char(30),
char(30),
nacionalidade char(10),
char(10), unique(id));
unique(id));
NOTICE: CREATE TABLE/UNIQUE will create implicit index
diretores_id_key for table diretores
CREATE
Note que a sequencia unique(id)
unique(id) já
já indexa o campo id da tabela diretores. Esse campo id é
muito importante, pois é com ele que nó
nós vamos relacionar o campo diretores na tabela
filmes.
Antes de inserir os filmes vamos inserir os diretores
template1=> insert into diretores values (0,'Steven
(0,'Steven Spilberg','Americano');
Spilberg','Americano');
INSERT 18527 1
template1=> insert into diretores values (1,'Ewa
Polones');
');
(1,'Ewa Karpoff','
Karpoff','Polones
INSERT 18528 1
19
Aula 03
Manipulando Base de Dados – PSQL
Agora insira os filmes
template1=> insert into filmes values ('A volta dos que nao foram',1990,1);
INSERT 18529 1
template1=> insert into filmes values ('A volta dos que nao foram II',1991,1);
INSERT 18530 1
template1=> insert into filmes values ('De volta pro futuro',1985,0);
INSERT 18531 1
Veja que no campo diretor nó
nós colocamos o nú
número id dele que aparece na tabela de
diretores.
Tente agora mostrar os dados da tabela filmes
template1=> select * from filmes;
A volta dos que nao foram
A volta dos que nao foram II
De volta pro futuro
|1990|
|1991|
|1985|
1
1
0
Usando duas tabelas relacionadas nó
nós eliminamos o problema de dados redundantes, mas
criamos outro. Agora em vez de aparecer o nome dos diretores aparece
aparece um monte de
números, algo difí
difícil para seres humanos lidarem. Mas não precisa se preocupar com um
query um pouco mais complexa podemos resolver esse problema.
Aula 03
Manipulando Base de Dados – PSQL
template1=> select filmes.nome,filmes.ano,diretores.nome from
filmes,diretores where filmes.diretor=diretores.id;
De volta pro futuro
|1985|Steven
|1985|Steven Spilberg
A volta dos que nao foram
|1990|Ewa
|1990|Ewa Karpoff
A volta dos que nao foram II
|1991|Ewa
|1991|Ewa Karpoff
Veja que a clausula from tem duas tabelas, isso é necessá
necessário porque a informaç
informação agora
está
está espalhada por mais de uma tabela. A capacidade de juntar vá
várias tabelas em uma
única query é a capacidade mais importante de linguagem SQL. Veja també
também que a agora
temos dois campos nome, um na tabela filmes e outro na tabela diretores,
diretores, para
diferenciadiferencia-los especificamos o nome da tabela antes do nome do campo, separando
separando os dois
por um ponto. Outra coisa importante é a clausula WHERE que diz para o postgres que o
campo diretor da tabela filmes se relaciona com campo id da tabela
tabela diretores.
20
Término da Aula
Aula 03
21