Apesar de geralmente usarmos a interface gráfica para criar e manipular bancos de dados, podemos utilizar a linha de comando do PSQL para isso.
Se você estiver no Linux, abra o terminal e digite psql --version para ver a versão, e psql --help para ver a ajuda. Para logar no SQL digite o nome de usuário e psql, assim:
sudo -i -u postgres
psql
No caso do Windows, basta abrir o utilitário diretamente, colocar o usuário e senha configurados, que ele abrirá no banco de dados padrão (postgres). Se o path estiver configurado você pode logar digitando no CMD psql -U postgres.
Os comandos são definidos com uma barra na frente, para configurar uma senha para o usuário, digite o comando \password. Para limpar a tela, use Ctrl L (no terminal do Linux). Para vermos os comandos SQL disponíveis, digite \h.
Digite o comando \h create role, esse comando é para ver a ajuda para criar novos usuários. Para vermos os comandos administrativos, digite \?.
Para listar os bancos de dados disponíveis, digite \l. Para ver os privilégios do usuário logado, digite \du.
Para acessar outro banco de dados, basta colocar \c nomedobanco. Estando dentro do banco, para ver as tabelas, digitamos \d. Para ver as tabelas administrativas e visões do postgres, digite \dS.
Para alternar entre o terminal padrão do sistema e o do postgresql, digite \!, mas se der exit, ele não fechará a janela, e voltará ao psql. Podemos também rodar o comando do sistema operacional digitando \! nomedocomando (como pra limpar a tela no Windows, digitando \! cls). Para sair do PSQL definitivamente digite \q.
Vamos supor que existe o usuário eu
no nosso sistema, se a gente logar digitando psql, ele logará com esse usuário padrão do sistema, que não existe no Postgres. Para configurarmos ele, podemos logar com o usuário configurado com o banco (no caso, postgres) para configurarmos o usuário eu
(usuário no linguajar do Postgres é chamado role
).
Para ver os privilégios do usuário logado, digite \du. E para ver as informações digite \conninfo.
Para configurar uma senha para o usuário postgres
(que pode estar sem a senha configurada), digite \password e coloque a nova senha. Após configurado, na próxima vez que logar ele pedirá a senha, mas ele continuará logando direto por ser uma configuração padrão do Postgres que confia
no usuário logado. Para exigir o pedido de senha, digite show hba_file; para ver onde está o arquivo de configuração da senha no sistema. Use um editor de texto comum para editar, mas com privilégio de superusuário, e altere essa linha:
# Database administrative login by Unix domain socket
local all postgres peer
Para alterar, basta mudar o peer para md5 (pode ser scram-sha-256 também). Também existe o trust, que confia em qualquer usuário para logar no Postgres sem senha. Depois reinicie o serviço do Postgres (no caso do Linux, digite no terminal sudo systemctl restart postgresql. Depois disso tente logar novamente digitando psql -U postgres.
Os comandos createuser e dropuser são wrappers para criar usuários no Postgres. Um wrapper é usado dentro do Postgres para simplificar comandos do SQL.
No Linux, logue como o usuário do Postgres no terminal (não no PSQL), em Windows isso não funcionará. Digite o comando createuser -dPs nomedouser. No caso, o d
é o privilégio pra criar bancos de dados, o P
para criar com senha e o s
para criar como superusuário. Para ver se ele foi criado mesmo, digite \du.
Ao tentar logar no Postgres com o novo usuário, ele dará erro por causa da opção peer
. Então vamos voltar no arquivo pg_hba.conf (digitando show hba_file; para vermos onde ele está) e procurar a linha referente aos usuários e o adicione, assim:
# Database administrative login by Unix domain socket
local all postgres md5
local all nomedouser md5
Reinicie o serviço do Postgres e tente logar novamente. Ele pedirá a senha. Mas ainda ele não vai encontrar a mesma por não ter um banco com este nome (pois por padrão ele tem um banco com o nome do usuário). Nesse caso digite psql -U nomedouser postgres para ele entrar com o banco existente.
PS: Dentro do PSQL, o nome que aparece ao lado é o banco atual, não o usuário, para ver o usuário logado, digite \conninfo.
Para excluir um usuário do sistema do Postgres, usamos esse comando fora do PSQL, logado como outro usuário: dropuser nomedousuario, ele pedirá a senha do banco logado (não o que será excluído).
PS: Se ele tiver privilégios ou propriedades em bancos, tabelas e etc. específicos, não conseguiremos excluir os usuários vinculários.
Para criar algo no banco de dados usamos o comando create, que pode ser um banco, tabela, trigger, etc., no caso, digitamos simplesmente isso:
create database teste;
Podemos também criar com opções, dessa forma:
create database teste2
with owner = postgres
encoding = 'utf8'
lc_collate = 'portuguese_brazil.1252'
lc_ctype = 'portuguese_brazil.1252'
tablespace = pg_default
connection limit -1; # O limite é definido aqui, -1 significa ilimitado.
Mas essas opções acima são as opções padrão, então não precisa usar o comando longo, a não ser que você queira alterar alguns deles.
Já existem alguns bancos padrões do Postgres, como os denominados template
, que são modelos para novos bancos e nunca devem ser excluídos.
Primeiramente, vamos criar via PSQL dessa forma, após logar no mesmo:
create database teste;
Para dar privilégios a um usuário, podemos usar o comando grant, como por exemplo esse para um usuário poder fazer tudo no novo banco de dados criado:
grant all on database teste to nomedouser;
Para excluir privilégios do usuário, use o grant revoke.
Para excluir um banco de dados, basta usar apenas drop database teste;.
Nas aulas seguintes, vamos usar o banco biblioteca. Vamos criar ele assim:
create database biblioteca;
Podemos fazer as operações no PGAdmin, que acessamos de forma gráfica. Ao ver no banco que escolhermos, vemos os comandos usados para criá-lo, de forma longa. Indo em Bancos de Dados e clicando com o botão direito, podemos criar um banco de dados de forma gráfica, sem precisar digitar comandos. Indo em SQL, no banco especificado, podemos digitar comandos como por exemplo, o select (tipo select datname from pg_database; para mostrar dados padrão do Postgres).
O PostgreSQL possui uma rica variedade de tipos de dados que podem ser empregados na definição de colunas em tabelas.
Esses tipos englobam os domínios numérico, texto, data e hora, geométrico, booleano, endereçamento de redes, enumeração, e até mesmo tipos definidos pelo usuário, com o uso da declaração create type.
Veremos a seguir alguns dos tipos mais comuns usados em Postgres:
| Tipo | Aplicação |
|---|---|
smallint |
Inteiros de 2 bytes |
int |
Inteiros de 4 bytes |
bigint |
Inteiros de 8 bytes |
numeric(precisao, escala) |
Números de ponto flutuante, onde: Precisão = Nº de dígitos total. Escala = Nº de Casas Decimais |
real |
32 bits, até 6 dígitos decimais de precisão |
double precision |
64 bits, variável, até 15 dígitos de precisão |
serial |
32 bits de tamanho, com sinal, números sequenciais |
big serial |
64 bits de tamanho, com sinal, números sequenciais |
money |
64 bits, com sinal (263 valores). Para valores monetários |
| Tipo | Aplicação |
|---|---|
text |
Varchar de tamanho ilimitado. Tipo preferido para strings |
char(n), character(n) |
Caracteres de tamanho fixo, com padding (preenchimento) e n caracteres |
varchar(n) |
Varchar de tamanho limitado a até n caracteres |
| Tipo | Aplicação |
|---|---|
date |
4 bytes, apenas datas, precisão de 1 dia |
time [without time zone] |
8 bytes, hora sem fuso horário, com precisão de 1 microssegundo |
time [with time zone] |
12 bytes, armazena data e hora com fuso horário, precisão de 1 microssegundo |
timestamp [with time zone] |
8 bytes, armazena data e hora com fuso horário, precisão de 1 microssegundo |
timestamp [without time zone] |
8 bytes, armazena data e hora sem fuso horário, precisão de 1 microssegundo |
interval |
16 bytes, armazena faixas de tempo, com precisão de 1 microssegundo |
| Tipo | Aplicação |
|---|---|
boolean |
Tipo lógico, 8 bits (1 byte) - Valores true (1/yes/on) ou false (0/no/off) |
cidr |
7 ou 19 bytes - Endereços de rede IPv4 ou IPv6, como 192.168.14.0/24 |
inet |
7 ou 19 bytes - Endereços de hosts IPv4 ou IPv6, como 192.168.14.22/32 |
macaddr |
6 bytes (48 bits), como 00:22:33:44:55:b2 ou 0022.3344.55b2 |
| Geometric Types | Armazenar informações relacionadas com figuras geométricas, como linhas, círculos, polígonos, pontos, caminhos, etc. |
| Tipos de Enumeração | Criados pelo usuário, para conjunto de valores estáticos |
tsvector/tsquery |
Tipos para busca completa de texto em documentos |
Para criar tabelas, vá no PgAdmin, vá no banco, em ferramentas e em ferramenta de consulta e digite esses comandos:
create table clientes (
codigo int primary key,
nome varchar(20) not null,
sobrenome varchar(40) not null
);
PS: Dá pra criar de forma gráfica também.
Crie essa tabela também:
create table produtos (
codigo int primary key,
nome varchar(30) not null,
descricao text null,
preco numeric check(preco > 0) not null, -- Verifica se o preço é maior que zero
qtd_estoque smallint default 0
);
Pode ver na parte de tabelas (dentro de esquemas) que ambas as tabelas estão lá.
Agora crie essa tabela para pedidos, no qual terá chaves estrangeiras, as quais serão feitas de duas formas:
create table pedidos (
codigo serial primary key, -- O tipo serial cria um número que é incrementado
cod_cliente int not null references clientes(codigo),
cod_produto int not null,
qtd_vendida smallint not null,
foreign key(cod_produto) references produtos(codigo)
);
Para inserir dados nas tabelas, usamos o comando insert into assim:
insert into clientes (codigo, nome, sobrenome) values (1, 'Fulano', 'da Silva');
PS: Se os dados inseridos for na mesma ordem das colunas no banco, podemos omitir os nomes das colunas.
Insira mais dois cadastros:
insert into clientes (codigo, nome, sobrenome) values (2, 'Beltrano', 'Silveira');
insert into clientes (codigo, nome, sobrenome) values (3, 'Sicrano', 'Teixeira');
Para vermos se os dados foram inseridos, use o select assim:
select * from clientes;
Vamos cadastrar produtos, da mesma forma, mas no caso vamos inserir vários cadastros de uma vez só:
insert into produtos (codigo, nome, descricao, preco, qtd_estoque) values
(1, Álcool Gel', 'Garrafa de álcool em gel de 1 litro', 12.90, 20),
(2, 'Luvas de Látex', 'Caixa de luvas de látex com 100 unidades', 32.50, 25),
(3, 'Pasta de Dentes', 'Tubo de pasta de dentes de 90g', 3.60, 12),
(4, 'Sabonete', 'Sabonete antibacteriano de 90g', 3.50, 5),
(5, 'Enxaguante Bucal', 'Antisséptico bucal de 500 ml', 17.00, 28);
Para ver os dados, use o select da mesma forma:
select * from produtos;
Vamos inserir dados na tabela pedidos:
insert into pedidos (cod_cliente, cod_produto, qtd_vendida) values -- Não se coloca o campo de serial
(1, 2, 3),
(2, 3, 2),
(1, 3, 4);
Dê um select * from pedidos; para ver os dados inseridos.
Para realizar consultas, como foi dito anteriormente, usamos o comando select. Podemos usar o asterisco para selecionar todas as colunas ou escrever as colunas desejadas:
select * from clientes;
Usando o nomes de uma coluna:
select nome from clientes;
Aí só aparecerá somente os dados referentes à coluna especificada.
Para pegar dados de mais de uma coluna, fazemos assim:
select nome, sobrenome from clientes;
Inclusive, nem precisam estar na ordem dos dados na coluna:
select sobrenome, nome from clientes;
Vamos fazer um select em produtos, assim:
select * from produtos;
Pra pegar apenas os nomes dos produtos:
select nome from produtos;
-- Depois faça esse comando:
select nome, descricao from produtos;
-- E esse, fora da ordem:
select nome, preco, descricao from produtos;
PS: Podemos dividir os comandos em várias linhas.
Fazendo o mesmo em pedidos:
select * from pedidos;
Claro que a tabela pedidos só retornará números referentes às chaves estrangeiras. Isso será tratado mais pra frente.
Quando usamos o select sem nenhuma condição, ele retorna todos os registros que estão em determinada tabela. Para filtrarmos apenas determinados registros de uma tabela, usamos a cláusula where.
Uma consulta básica, como sabemos, é assim:
select * from clientes;
O select acima retorna todos os registros da tabela clientes, para pegar um determinado registro da tabela, fazemos assim:
select * from clientes where codigo = 1;
No caso acima, ele só retornará o registro referente ao código específico. Caso ele não seja encontrado, ele não dá erro, mas não mostrará nada.
Vamos ver um exemplo na tabela produtos:
select * from produtos;
Com a cláusula where:
select nome, qtd_estoque from produtos where qtd_estoque < 10;
No caso, usamos um operador relacional, que são os mesmos de várias linguagens. No comando acima mostramos apenas os dados especificados dos produtos que o estoque está abaixo de 10.
Veja outro exemplo:
select nome, preco from produtos where preco >= 15.00;
-- Invertendo:
select nome, preco from produtos where preco < 15.00;
Outro exemplo, na tabela de pedidos:
-- Mostrando tudo:
select * from pedidos;
-- Filtrando:
select cod_produto, qtd_vendida from pedidos where cod_cliente = 1;