PostgreSQL: Guia de uso

há 4 dias 11

Saudações. Nesse artigo e tutorial vou mostrar como usar o PostgreSQL (PG), explorando os principais recursos com exemplos.

O PG é um SGDB (sistema de gerenciamento de banco de dados).

Pré-requisitos (constam em outros artigos aqui do blog):

Instalação do Linux (Debian);
Obter terminal (shell) como usuário root;
Instalação previa do PostgreSQL:
- https://blog.patrickbrandao.com/postgres-guia-de-instalacao/

Vou considerar que você, leitor, já esteja dominando o acesso ao servidor Postgres via terminal.

1 – Comandos rápidos

Principais comandos no terminal PSQL:

PostgreSQL Shell (psql)

\l -- Listar bancos de dados \c nome_db -- Conectar em um banco de dados \c nome_db usuario -- Conectar em um DB com usuário específico \conninfo -- Informacoes da conexao atual \d* -- Lista todos os objetos \dt -- Listar tabelas do banco conectado \dt+ -- Listar tabelas com detalhes \d nome_da_tabela -- Descreve a estrutura da tabela \d+ nome_da_tabela -- Descreve a estrutura da tabela detalhada \d nome_index_vw -- Descreve índice ou view \dn -- Lista todos os esquemas \du -- Lista usuários/roles \q -- Sair da conexão PGSQL \? -- Ajuda geral dos comandos psql \h -- Lista de todos os comandos \h SELECT -- Ajuda para usar o comando SELECT \h CREATE TABLE -- Ajuda para criar tabela \h comando_aqui -- Ajuda para o comando desejado \l -- Listar bancos de dados \l+ -- Listar bancos de dados com detalhes \dt -- Lista tabelas do schema atual \dt+ -- Lista tabelas com detalhes \dt schema.* -- Lista tabelas de um schema específico \dt *.nome_tabela -- Lista a tabela específica em todos os schemas \dp -- Lista privilégios de tabelas \dp+ -- Lista privilégios de tabelas com detalhes \dn -- Lista esquemas \dn+ -- Lista esquemas com detalhes \di -- Lista índices \di+ -- Lista índices com detalhes \df -- Lista funções \df+ -- Lista funções com detalhes \df nome_funcao -- Lista função específica \dy -- Lista triggers de eventos \dv -- Lista views \dv+ -- Lista views com detalhes \dm -- Apenas materialized views \ds -- Lista sequências \ds -- Lista sequências com detalhes \db -- Lista tablespaces \db+ -- Lista tablespaces com detalhes \dE -- Lista encodings disponíveis \dC -- Lista collations \dD -- Lista domains \dc -- Lista conversões \do -- Lista operadores \do+ -- Lista operadores com detalhes \da -- Lista funções de agregação \dT -- Lista tipos de dados \dT+ -- Lista tipos de dados com detalhes \e -- Abre editor para último comando \e script.sql -- Abre arquivo informado no editor \ef nome_funcao -- Abre o editor para editar a função informada \s -- Mostra histórico de comandos \s output.txt -- Salva histórico no arquivo informado \w output.sql -- Salva o último comando no arquivo informado \x -- Liga/desliga modo expandido (alterna) \x on -- Liga modo expandido (padrão, chato) \x off -- Desliga modo expandido (bom!) \a -- Alternar exibição entre alinhado/não alinhado \t -- Alternar exibição de cabeçalhos das consultas \f -- Mostrar separador atual (ascii de exibição) \f '|' -- Definir caracter pipe "|" como separador \f '\t' -- Define um TAB como separador \H -- Liga/desliga modo de saída HTML (alterna) \T 'Titulo X' -- Define um título para as próximas consultas \T -- Remove título das consultas \C 'Titulo Y' -- Define um título só para a próxima consulta \pset border 2 -- Define bordas das consultas \pset format wrapped -- Formato com quebra de linha das consultas \pset null '(NULL)' -- Define como exibir colunas com valor NULL \pset footer off -- Remove rodapé das tabelas \timing -- Liga/desliga cronômetro de consultas \timing on -- Liga cronômetro de consultas \timing on -- Desliga cronômetro de consultas \! -- Abre shell para o sistema onde o PG está rodando \! COMMAND -- Executa um comando específico no shell do sistema \cd -- Alterna para o diretório HOME no sistema \cd FOLDER_PATH -- Alterna para um diretório específico no sistema \setenv VN VLE -- Define uma variável de ambiente VN com valor VLE BEGIN -- Iniciar transação \begin -- Iniciar transação (apelido de BEGIN) COMMIT -- Finaliza a transação e executa \commit -- Finaliza a transação e executa (apelido de COMMIG) ROLLBACK -- Desfaz transação \rollback -- Desfaz transação (equivale a ROLLBACK) \i arquivo_sql -- Executa todos os comandos do arquivo (/pasta/…) \o arquivo_saida -- Redireciona a saída da tela para o arquivo SELECT 1\g out.txt -- Executa a SELECT e salva no arquivo out.txt -- Importe o arquivo file.csv para a tabela escolhida \copy tabela FROM 'file.csv' CSV HEADER; -- Exportar a tabela escolhida para um arquivo CSV \copy tabela TO 'file.csv' CSV HEADER; SELECT count(*) FROM tabela \watch 5 -- Executa a SELECT a cada 5 segundos \watch -- Para a repetição -- Executa e mostra em formato expandido SELECT * FROM tabela \gx -- Exibir em formato de tabela cruzada SELECT c1, c2 FROM tabela \crosstabview

2 – Desambiguação no PostgreSQL

Ao instalar o PostgreSQL (seja no HOST ou em container), a palavra “postgre” aparece muitas vezes em objetos diferentes. Isso confunde o usuário principiante.

No sistema Linux (HOST ou Container) existe o usuário “root” que tem poder sobre o sistema operacional. O instalador do PG cria o usuário “postgres” [1] no sistema. Esse usuário de sistema deve ser o dono dos diretórios e arquivos de sistema do PG (/var/lib/postgresql).

Com o PostgreSQL instalado e rodando, existe dentro dele o usuário “postgres” [2], que é um usuário do serviço de SGDB (conectar no banco, executar SQL, …). Nesse ponto pode existir um usuário “root” criado dentro do SGDB (como é o caso do MySQL/MariaDB) que nada tem a ver com o usuário do sistema.

Ao executar o comando psql para obter um shell no PG sem informar o usuário dentro do PG, o psql usa por padrão o mesmo usuário que você está usando no sistema, se esse usuário também existir dentro do PG.

O sistema PG possui um banco de dados principal onde tudo é registrado (usuários, bancos de dados, objetos em geral) que se chama “postgres” [3].

Afirmação final: logado no linux com o usuário postgres[1] , executei o comando psql e me conectei no serviço PostgreSQL com o usuário padrão postgres[2] e selecionei o banco de dados postgres[3].

O termo conectar também é ambíguo. Você precisa se conectar[1] ao PG (via TCP/IP ou Socket Unix) e autenticar para obter uma conexão funcional. Dentro dessa conexão você precisa se conectar[2] a um banco de dados (selecionar DB) para ter acesso aos seus objetos (schema, tables, etc…);

Esclarecido isso, vamos explorar os objetos dentro do PostgreSQL.

3 – TableSpace (espaço de armazenamento)

TableSpace (TS) são os locais onde os dados são realmente gravados no servidor (sistema de arquivos). Sempre que você cria qualquer objeto dentro do PG isso resulta na adição ou alteração de um arquivo persistente em disco. Use com cuidado.

TableSpaces:

pg_default e pg_global (para cluster)
Quando o caminho é omitido, o diretório usado será:
- /var/lib/postgresql/data/

Consultando TS:

PostgreSQL Shell (psql)

-- Listar tablespaces (atalho): \db -- List of tablespaces -- Name | Owner | Location -- ------------+----------+---------- -- pg_default | postgres | -- pg_global | postgres | -- (2 rows) -- Listar tablespaces (consultando estrutura interna): SELECT spcname AS "_NAME", pg_catalog.pg_get_userbyid(spcowner) AS "_OWNER", pg_catalog.pg_tablespace_location(oid) AS "_PATH" FROM pg_catalog.pg_tablespace ORDER BY spcname; -- Listar tablespaces com detalhes de espaço consumido e permissões: SELECT t.spcname, t.spcowner, u.usename AS owner_name, pg_tablespace_location(t.oid) AS location, pg_size_pretty(pg_tablespace_size(t.spcname)) AS size, array_to_string(t.spcacl, ', ') AS permissions FROM pg_catalog.pg_tablespace t LEFT JOIN pg_user u ON t.spcowner = u.usesysid;

Ao criar um TS personalizado, cuidado para não fazer isso em um container e escrever fora das pastas mapeadas em volumes.

Bash

# Criar diretório para o tablespace: mkdir -p /nvme; mkdir -p /nvme/ts_exemplo01; mkdir -p /nvme/ts_exemplo02; # Garantir propriedade do diretório ao usuário 'postgres' do sistema: chown -R postgres:postgres /nvme; # Conferir: ls -lah /nvme; ls -lah /nvme/ts_exemplo01; ls -lah /nvme/ts_exemplo02;

Registrando TS no PG:

PostgreSQL Shell (psql)

-- Exemplo de como criar diretorio de dentro do PSQL: \! mkdir "/nvme" \! mkdir "/nvme/ts_exemplo01" \! mkdir "/nvme/ts_exemplo02" -- normalmente o exemplo acima falha por falta de permissao, o -- psql roda como usuario postgres e criar diretorios requer poder de root -- Criar um tablespace chamado "ts_exemplo01" na pasta "/nvme/ts_exemplo01": CREATE TABLESPACE ts_exemplo01 LOCATION '/nvme/ts_exemplo01'; -- Criar um tablespace chamado "ts_exemplo02" na pasta "/nvme/ts_exemplo02": -- - especificar o usuário dono do TS para dar exclusividade. CREATE TABLESPACE ts_exemplo02 OWNER postgres LOCATION '/nvme/ts_exemplo02'; -- Listar tablespaces (atalho): \db -- List of tablespaces -- Name | Owner | Location -- --------------+----------+-------------------- -- pg_default | postgres | -- pg_global | postgres | -- ts_exemplo01 | postgres | /nvme/ts_exemplo01 -- ts_exemplo02 | postgres | /nvme/ts_exemplo02 -- (4 rows) -- Exibir ajuda para destruir tablespace: \h DROP TABLESPACE; -- sintaxe: DROP TABLESPACE [ IF EXISTS ] nome_do_ts; -- Destruindo tablespace ts_exemplo01: DROP TABLESPACE IF EXISTS ts_exemplo01; -- Destruindo tablespace ts_exemplo02: DROP TABLESPACE IF EXISTS ts_exemplo02; -- Criar ts_exemplo01 novamente: CREATE TABLESPACE ts_exemplo01 LOCATION '/nvme/ts_exemplo01'; -- Renomear tablespace: ALTER TABLESPACE ts_exemplo01 RENAME TO ts_exemplo1; -- Destruir novamente: DROP TABLESPACE IF EXISTS ts_exemplo1;

3 – Database (Banco de dados)

No PG, o banco de dados é o volume principal que armazena todos os objetos para uma finalidade comum (um sistema de ERP por exemplo). Esses objetos são:

TableSpaces: são as unidades de armazenamento onde os objetos do banco de dados serão armazenados. Precisam existir antes da criação do objeto;
- Exemplo: você pode criar um schema de tabelas em um TS de armazenamento de alta velocidade (NVME) e outro schema de tabelas em um TS de armazenamento com grande volume mas baixa velocidade (SATA, NFS);
Templates: são bancos de dados prontos com todos os objetos criados previamente que servem de modelo para clonagem;
- Exemplo: um banco de dados template de um sistema SaaS que é clonado para cada novo usuário. O novo banco herda a cópia de todos os objetos do banco template;
Schemas (esquemas): são containers de tabelas e objetos comuns à essas tabelas. São como “departamentos” dentro do banco de dados. Permite que objetos tenham nomes iguais mas em esquemas diferentes.
- Exemplo: a tabela “logs” pode existir no esquema padrão public e também pode existir uma tabela “logs” no esquema “infra“.
  - Schema padrão: public
  - Referência de tabela: “logs” aponta para “public.logs“;
Tables (tabelas): a unidade primordial de armazenamento semelhante a planilhas. Possui linhas (registros) e colunas. É onde os dados realmente são armazenados;
Sequency (sequências): são contadores que podem ser usados para gerar números únicos sequenciais para uma tabela ou para várias tabelas ao mesmo tempo. É o objeto que provê o efeito de auto-increment em colunas tipo SERIAL. Ajuda a evitar colisões de identificadores únicos;
Index (índices): cópia de uma coluna (índice único) ou várias colunas (índice composto) mantido em ordem para ajudar a localizar o dado real em uma tabela. Auxilia na localização rápida de registros;
Partitions (partições): são regras que permitem dividir a tabela em pedaços espaciais organizados para facilitar o acesso a grandes volumes de dados.
- Exemplo: criar uma partição para cada dia em uma tabela de logs que recebe bilhões de registros por dia ajuda a localizar um dia específico que estará em um arquivo especifico, assim não é necessário percorrer trilhões de registros na tabela ou em índices para localizar com rapidez um período desejado;
Types e domains (tipos e domínios): tabelas possuem colunas com tipos específicos padrões mas você pode criar seus tipos personalizados, domínios (DOMAIN) permite validar sintaticamente os valores de uma tabela e impedir dados mal formatados ou inválidos sejam inseridos;
Views (visão): visão virtual de uma ou mais tabelas que criam uma tabela virtual de apresentação de dados. Sempre que uma view é consultada os comandos SQL da VIEW copiam os dados das tabelas reais;
Materialized View (visão materializada): versão persistente de uma view, armazena uma cópia dos dados da view em uma tabela persistente. Para que os dados sejam gerados é preciso materializar a view para popular a tabela da MV, em seguida todas as SELECTs encima da MV retornam os dados materializados sem consultar as tabelas reais. Boa para relatórios pesados que não são gerados constantemente e views que não precisam de dados em tempo real;
Event Trigger (gatilhos): são procedimentos semelhante a scripts que rodam antes ou depois do evento ao qual foi associado;
- Exemplo: antes de efetivar um UPDATE, copia os dados do registro atual para uma tabela com cópias de sombra dos registros;
- Exemplo: ao inserir um novo registro, auto-preencher colunas omitidas com valores gerados por funções;
Functions (funções): são códigos que podem ser acionados em comandos SQL para transformar e verificar dados (ex.: função de verificar formato de CPF, função de verificar número primo);
Procedures (procedimentos): são blocos de códigos independentes que executam comandos SQL, manipulam variáveis, usam funções, etc… são como “softwares dentro do banco de dados”;
Extensions (extensões, plugins): são módulos adicionais no núcleo do Postgres. Permite adicionar novos objetos (funções, tipos, etc…);
Rules (regras): são parecidas com triggers, são disparados para conferir se o procedimento se enquadra nas políticas do objeto;
Constraints (restrições): regras aplicadas às colunas ou tabelas que garantem a integridade e consistência dos dados, funcionam como policiais que impedem a inserção, atualização ou exclusão de dados que violem as regras estabelecidas;
- Exemplo: impedir que um par de valores em duas colunas se repitam na tabela;

Parece muita coisa, mas dominando esses objetos você construirá um banco de dados vivo que reduz o código do programa principal e provê velocidade ao fornecer e manipular dados.

PostgreSQL Shell (psql)

-- Ajuda para criar banco de dados: \h CREATE DATABASE; -- Ajuda para destruir um banco de dados: \h DROP DATABASE; -- Listar banco de dados: \l -- Exemplo mais explícito (recomendado): CREATE DATABASE automacoes WITH OWNER = postgres ENCODING = 'UTF8'; \l -- Conectar (entrar) em um banco: \c automacoes; -- Informacoes da sua conexao atual: \conninfo -- Sair do banco de dados (mudar para o banco padrao 'postgres') \c postgres -- Exemplo simples: CREATE DATABASE db_exemplo01; \l -- Renomear: ALTER DATABASE db_exemplo01 RENAME TO db_exemplo1; -- Destruir completamente o banco automacoes: DROP DATABASE automacoes; -- Destruir completamente o banco db_exemplo1: -- - Apenas se existir, forcar destruicao completa DROP DATABASE IF EXISTS db_exemplo1 WITH ( FORCE ); \l -- Criar banco de dados (DB) em tablespace (TS) dedicado \! mkdir -p '/nvme/ts_automacoes'; -- Criar tablespace: CREATE TABLESPACE ts_automacoes LOCATION '/nvme/ts_automacoes'; -- Criar database dentro do tablespace: CREATE DATABASE db_automacao1001 WITH OWNER = postgres TABLESPACE = 'ts_automacoes' ALLOW_CONNECTIONS = TRUE CONNECTION LIMIT 256 ENCODING = 'UTF8'; -- Listar bancos de dados: \l -- Destruir banco de dados db_automacao1001: DROP DATABASE IF EXISTS db_automacao1001; -- Desruir tablespace ts_automacoes: DROP TABLESPACE ts_automacoes;

4 – Schema (esquemas)

Schemas são “bancos de dados dentro do banco de dados”. Servem para ajudar a criar espaços nomeados (namespace) para ajudar bancos muito grandes.

Exemplo:

Um sistema de ERP pode ter um schema “financeiro” contendo todas as tabelas, indices, extensões e views que não são visíveis para quem trabalha no schema “help_desk“, podem existir centenas de schemas isolados, mas todos dentro do mesmo banco de dados;

Todo banco de dados no PostgreSQL é criado com um schema padrão: public

PostgreSQL Shell (psql)

-- Inventário de todos os schemas: SELECT schema_name, schema_owner FROM information_schema.schemata; -- Criar banco de dados: CREATE DATABASE db_exemplo03 WITH OWNER = postgres ENCODING = 'UTF8'; \l -- Conectar (entrar) no banco de dados: \c db_exemplo03; -- You are now connected to database "db_exemplo03" as user "postgres". -- Listar schemas: \dn -- List of schemas -- Name | Owner -- --------+------------------- -- public | pg_database_owner -- (1 row) -- Criar schema de desenvolvimento e testes: CREATE SCHEMA IF NOT EXISTS devtests; -- Criar schema para um usuario -- (schema privado do usuario, precisa criar o usuario primeiro): -- Criar usuario: CREATE USER user001 WITH PASSWORD 'tulipasql'; CREATE SCHEMA IF NOT EXISTS sch_user001 AUTHORIZATION user001; -- Listar esquemas: \dn+ -- Destruir schema: DROP SCHEMA IF EXISTS sch_user001; -- Destruir usuario: DROP USER IF EXISTS user001;

Search Path

O recurso “Search Path” – SP (caminho de busca) determina em quais esquemas uma tabela será procurada se a SQL não informar o caminho completo da tabela.

O SP padrão é: $user, public

Exemplo:

Ao se conectar com o usuário postgres e executar “SELECT * FROM logs;”, o SP traduzirá para “postgres.logs” (baseado em $user) e “public.logs“;
- Se existir um schema chamado “postgres” e dentro dele uma tabela chamada “logs”, a busca retornará “postgres.logs“, a SQL será executada como:
  - “SELECT * FROM postgres.logs;”
- Se existir um schema chamado “public” e dentro dele uma tabela chamada “logs”, a busca retornará “public.logs“, a SQL será executada como:
  - “SELECT * FROM public.logs;”
Se o usuário “user001” se conectar a busca será schema user001 seguida de public;
Se qualquer usuário executar: “SELECT * FROM public.logs;” o SP não será consultado pois a SQL foi fornecida completa;

Praticando:

PostgreSQL Shell (psql)

-- Consultar variável de SP: SHOW search_path; --- search_path --- ----------------- --- "$user", public --- (1 row) -- Criar banco de dados: CREATE DATABASE db_exemplo04; \c db_exemplo04; -- Criar schemas: CREATE SCHEMA IF NOT EXISTS sch_develop; CREATE SCHEMA IF NOT EXISTS sch_sandbox; -- Criar usuario para testes e desenvolvimento: CREATE USER devops01 WITH PASSWORD 'tulipasql'; -- Alterar usuário dono (proprietário): ALTER SCHEMA sch_develop OWNER TO devops01; -- para desenvolvimento ALTER SCHEMA sch_sandbox OWNER TO postgres; -- para testes -- Alterar SP da sessão atual (não salva): SET search_path TO sch_sandbox,sch_develop,public; -- Alterar SP para nao enxergar o schema public: -- (temporario, somente durante a conexao atual) SET search_path TO sch_sandbox,sch_develop; -- Alterar search_path de um usuário -- (definitivo, salva para próximas sessões do usuário): ALTER USER devops01 SET search_path TO sch_develop,sch_sandbox; -- Alterar search_path do banco de dados -- (definitivo, salva para próximas sessões dos usuários): ALTER DATABASE db_exemplo04 SET search_path TO sch_sandbox; -- Destruir tudo DROP SCHEMA IF EXISTS sch_develop CASCADE; DROP SCHEMA IF EXISTS sch_sandbox CASCADE; DROP USER IF EXISTS devops01; \c postgres; DROP DATABASE db_exemplo04;

3 – Extensions (extensões)

As extensões dão funcionalidades adicionais à um banco de dados (todos os schemas) ou para um schema específico.

Importante: algumas funções (como UUID e criptografia) não irão funcionar se a respectiva extensão não for ativada no banco de dados ou esquema.

Extensões comuns:

pg_stat_statements: Monitora e coleta estatísticas de performance de todas as consultas SQL;
pg_stat_activity: Monitora atividade do banco em tempo real;
uuid-ossp: Gera UUIDs (Universally Unique Identifiers) para chaves primárias;
postgis: Adiciona suporte completo para dados geoespaciais;
pgcrypto: Funções criptográficas para segurança de dados;
hstore: Armazena pares chave-valor em uma única coluna;
pg_trgm: Busca por similaridade usando trigramas;
ltree: Gerencia dados hierárquicos (árvores);
unaccent: Remove acentos e caracteres especiais;
tablefunc: Funções para manipulação de tabelas e crosstab;
pg_partman: Para Big Data ML;
pgvector: Armazenamento e busca de embeddings vetoriais;
pg_embedding: Alternativa/complemento ao pgvector;
pg_vectorize: Automação de pipeline de embeddings;
lantern: Busca vetorial otimizada (alternativa ao pgvector);
madlib: Biblioteca completa de Machine Learning;
timescaledb: Séries temporais para IA;

Exemplos de uso:

PostgreSQL Shell (psql)

-- Criar banco de dados para testes de extensao: CREATE DATABASE testex WITH OWNER = postgres ENCODING = 'UTF8'; -- Entrar (conectar) no banco de dados 'testex' \c testex -- Listar todas as extensões disponíveis no sistema: SELECT name, default_version, comment FROM pg_available_extensions ORDER BY name; -- Listar extensões ativas no banco de dados atual: \dx -- List of installed extensions -- Name | Version | Def. version | Schema | Description -- ---------+---------+-----------------+------------+-------------------- -- plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language -- (1 row) -- Listar extensoes: SELECT extname, extversion, nspname FROM pg_extension e JOIN pg_namespace n ON e.extnamespace = n.oid; -- Carregar uma extensão no banco de dados atual: CREATE EXTENSION IF NOT EXISTS "uuid-ossp" CASCADE; -- Aatualizar se possível: ALTER EXTENSION "uuid-ossp" UPDATE; \dx -- List of installed extensions -- Name | Ver. | Def. version | Schema | Description -- ---------+---------+-----------------+------------+-------------------- -- plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language -- uuid-ossp | 1.1 | 1.1 | public | generate UUIDs -- (1 row) -- Testar funcionamento (gerar UUIDv4 usando funcao da uuid-ossp): SELECT uuid_generate_v4(); -- Criar schemas: CREATE SCHEMA IF NOT EXISTS sch_sandbox; -- Carregar extensao hstore somente no schema sch_sandbox: CREATE EXTENSION IF NOT EXISTS "hstore" WITH SCHEMA sch_sandbox; -- Listagem detalhada de extensoes e esquemas: \dx \dx+ \dx+ hstore -- Testar: SET search_path TO sch_sandbox; SELECT 'nome=>Joao, idade=>30'::sch_sandbox.hstore; -- informando schema explícito SELECT 'nome=>Joao, idade=>30'::hstore; -- usando schema corrente do SP -- Listando extensões e schemas do banco de dados atual: SELECT e.extname, e.extversion, n.nspname FROM pg_extension e JOIN pg_namespace n ON e.extnamespace = n.oid ORDER BY n.nspname, e.extname; -- Retirar extensao do schema sch_sandbox: DROP EXTENSION IF EXISTS "uuid-ossp"; DROP EXTENSION "hstore"; -- Destruir tudo DROP SCHEMA sch_sandbox; \c postgres; DROP DATABASE testex;

4 – Sequencias (Sequence)

Sequências são variáveis internas e persistentes geridas pelo Postgres que armazenam valores para uso em colunas sequenciais.

Normalmente cada tabela tem uma coluna id que é auto-preenchida com um número provido pela sequência.

O tipo padrão das sequencias são BIGINT (mínimo -9.223 trilhões e máximo 9.223 trilhões).

Internamente a sequencia é um objeto (tabela) dentro do schema. Várias tabelas podem fazer uso da mesma sequência.

PostgreSQL Shell (psql)

-- Criar uma sequencia para numero de chamados CREATE SEQUENCE IF NOT EXISTS chamado_unique_id AS INTEGER; -- Consultar valores da sequencia: SELECT * FROM public.chamado_unique_id; -- Criar tabelas que irao usar a sequencia: -- Tabela help_desk com chamados atendidos por telefone/chat CREATE TABLE IF NOT EXISTS help_desk ( id INTEGER NOT NULL DEFAULT nextval('chamado_unique_id') PRIMARY KEY, descricao VARCHAR(100) NOT NULL ); -- Tabela service_desk com chamados atendidos presencialmente dentro da empresa CREATE TABLE IF NOT EXISTS service_desk ( id INTEGER NOT NULL DEFAULT nextval('chamado_unique_id') PRIMARY KEY, descricao VARCHAR(100) NOT NULL ); -- Tabela ordens_servico com chamados atendidos na localizacao do cliente CREATE TABLE IF NOT EXISTS ordens_servico ( id INTEGER NOT NULL DEFAULT nextval('chamado_unique_id') PRIMARY KEY, descricao VARCHAR(100) NOT NULL ); -- Inserir chamados: INSERT INTO help_desk (descricao) VALUES ('Atualizar maquina windows A192'); INSERT INTO service_desk (descricao) VALUES ('Ajudar usuario com impressora'); INSERT INTO ordens_servico (descricao) VALUES ('Trocar monitor da guarita'); SELECT id, descricao FROM help_desk; -- 1 | Atualizar maquina windows A192 SELECT id, descricao FROM service_desk; -- 2 | Ajudar usuario com impressora SELECT id, descricao FROM ordens_servico; -- 3 | Trocar monitor da guarita -- Gerar numero usando a sequencia como originador: -- obs: resulta em incremento da sequencia, mesmo que -- voce nao use o numero gerado no ID de alguma coluna SELECT nextval('public.chamado_unique_id'); -- Reiniciar sequencia (volta ao valor inicial 1): ALTER SEQUENCE chamado_unique_id RESTART; -- Criando novo chamado no help_desk: INSERT INTO help_desk (descricao) VALUES ('Atualizar papel de parede'); -- VAI RETORNAR ERRO: a tabela help_desk ja possui o id=1 em uso. -- ao reiniciar a sequence voce tornou o sistema vulneravel a colisoes. -- Reiniciar sequencia usando um valor especifico (1001): ALTER SEQUENCE chamado_unique_id RESTART WITH 1001; -- Solicitar identificador: SELECT nextval('public.chamado_unique_id'); -- retorna: 1001 (numero que voce definiou no RESTART) -- Destruir tabela: DROP TABLE IF EXISTS help_desk; DROP TABLE IF EXISTS service_desk; DROP TABLE IF EXISTS ordens_servico; -- Destruir sequencia: DROP SEQUENCE IF EXISTS public.chamado_unique_id;

Sequências personalizadas

Você pode criar sequências negativas, decrescente, acelerada.

Tabelas que recebem INSERT (novos registros) intensos podem ser atrasados pela sequência, o recurso CACHE ajuda a sequência a ter os números livres prontos para fornecimento e acelerando a geração de registros.

Observe os exemplos com sintaxe de SEQUENCE completo:

PostgreSQL Shell (psql)

-- Sequencia para senhas de atendimento, decrescente: CREATE SEQUENCE seq_senha_atendimento AS SMALLINT -- Tipo de dados START WITH 100 -- Valor inicial INCREMENT BY -1 -- Incremento MINVALUE 1 -- Valor mínimo MAXVALUE 999 -- Valor máximo CACHE 50 -- Cache de valores CYCLE -- Reinicia ao atingir limite OWNED BY NONE; -- Ownership -- Execute varias vezes: SELECT nextval('public.seq_senha_atendimento'); -- resultados: 100, 99, 98, ... -- MINVALUE para números de pedido (nunca menor que 1000) CREATE SEQUENCE seq_pedidos START WITH 1000 -- Valor inicial, padrão inicial MINVALUE 1000 -- Valor minimo, padrão do reset NO MAXVALUE -- Sem valor maximo NO CYCLE; -- Nao reinicia ao atingir limite -- Normalmente nao permite valores minimos abaixo de 1, para ter -- valores minimos negativos e uma sequencia de decrementos: CREATE SEQUENCE seq_distance_balloon START WITH -1 INCREMENT BY -1 MINVALUE -9223372036854775807 -- Limite negativo do BIGINT MAXVALUE -1 CACHE 10 NO CYCLE; -- Destruir sequencias: DROP SEQUENCE IF EXISTS seq_senha_atendimento; DROP SEQUENCE IF EXISTS seq_pedidos; DROP SEQUENCE IF EXISTS seq_distance_balloon;

Sequências automáticas

Normalmente não se faz a criação manual de sequências! O PG disponibiliza os apelidos de tipos de colunas que internamente criam sequências padrão 1 até o limite do tipo.

Os tipos mágicos são:

SMALLSERIAL: cria uma sequência tipo SMALLINT;
SERIAL: cria uma sequência tipo INTEGER;
BIGSERIAL: cria uma sequência tipo BIGINT;

Exemplo:

PostgreSQL Shell (psql)

-- Sintaxe curta: -- Criar tabela com 'id' automaticamente ligado em sequencia: CREATE TABLE IF NOT EXISTS reclamacoes ( id SERIAL PRIMARY KEY, autor TEXT, descricao TEXT ); -- Sintaxe completa: -- como SERIAL é apelido de (sequencia automatica + integer), a sintaxe -- real da tabela é: -- 1: Criar a sequência CREATE SEQUENCE reclamacoes_id_seq AS INTEGER -- SERIAL usa INTEGER START WITH 1 -- Começa em 1 INCREMENT BY 1 -- Incrementa de 1 em 1 MINVALUE 1 -- Valor mínimo é 1 MAXVALUE 2147483647 -- Máximo do INTEGER CACHE 1 -- Sem cache NO CYCLE; -- Não reinicia -- 2: Criar a tabela CREATE TABLE IF NOT EXISTS reclamacoes ( id INTEGER NOT NULL DEFAULT nextval('reclamacoes_id_seq'), autor TEXT, descricao TEXT, CONSTRAINT reclamacoes_pkey PRIMARY KEY (id) ); -- 3: Associar a sequência à coluna (ownership) -- se a tabela for destruida a sequência é apagada junto ALTER SEQUENCE reclamacoes_id_seq OWNED BY reclamacoes.id; -- Destruindo tudo (tabela e sequencia, independente da forma escolhida acima): DROP TABLE IF EXISTS reclamacoes;

CRÍTICO: sequências requisitadas (nextval) geram um novo número e não descarta o número se ele não for usado na coluna da tabela vinculada. Exemplo:

1 – A sequence foi consultada para gerar um número, retorno 17;
2 – Um registro com id=17 tentou ser inserido e falhou;
3 – A próxima consulta na sequence retorna 18, mesmo que 17 não tenha sido usado;

5 – Tables (tabelas)

As tabelas são onde os dados realmente moram. São primordialmente como planilhas de colunas nomeadas. Cada coluna tem um tipo especifico de dado.

Tipos:

Booleano: para estados on/off, true/false, 1 bit: 0 (desligado) ou 1 (ligado);
Numéricos: com e sem ponto flutuante, otimizados para valores monetários, para ponto de precisão elevado;
Texto curto: usados para armazenamento eficiente de poucos caracteres, ASCII ou multibyte (UTF, LATIN, …);
Texto longo: usados para grandes volumes de texto;
Data/hora e timestamp: armazenar marco temporal, data, hora, segundo, milissegundo, tempo relativo unix (segundos desde 1970), quantidade de tempo e intervalos;
Binário: para dados que não possuem visualização em terminal (arquivos, conteúdo criptografado, audio, video, …);
Geográfico: dados de vetores e formas, compostos por vários pontos relativos;
Personalizado: enumerações, tipos e domínios;
Especiais: providos por extensões (hstore, jsonb);

Listar todos os tipos internos:

PostgreSQL Shell (psql)

-- Listar todos os tipos suportados internamente pelo PostgreSQL: SELECT typname, typlen FROM pg_type;

Exemplo: criar tabela básica e exemplos de manipulação de registros

PostgreSQL Shell (psql)

-- Criar banco de dados para testes de extensao: CREATE DATABASE sisteminha WITH OWNER = postgres ENCODING = 'UTF8'; -- Entrar (conectar) no banco de dados 'sisteminha' \c sisteminha -- Criar tabela de contatos (emails) para marketing: CREATE TABLE IF NOT EXISTS public.emails ( id BIGINT, nome VARCHAR(100), email VARCHAR(100), ativo BOOLEAN ); -- Informações da tabela: \d public.emails \d+ emails -- CRUD = Create (INSERT), Read (SELECT), Update e Delete -- [C] - Inserir registros: INSERT INSERT INTO public.emails (nome, email, ativo) VALUES ('Patrick', 'p@x.com', true); INSERT INTO emails (nome, email, ativo) VALUES ('Jose', 'j@x.com', true); INSERT INTO emails (nome, email) VALUES ('Andre', 'a@x.com'); INSERT INTO emails (nome, email) VALUES ('Bruno', 'b@x.com'); -- [R] - Obter cópia de registros: SELECT * FROM public.emails; SELECT * FROM emails ORDER BY nome; SELECT nome,email FROM emails WHERE ativo = true ORDER BY nome; SELECT email FROM emails WHERE name = 'Patrick'; -- [U] - Atualizar campos (colunas) em alguns registros: UPDATE public.emails SET ativo = false WHERE nome = 'Patrick'; UPDATE emails SET ativo = false WHERE email = 'j@x.com'; -- Conferindo: SELECT * FROM public.emails; -- [D] - Remover registros: DELETE FROM public.emails WHERE nome = 'Patrick'; DELETE FROM public.emails; -- - Conferindo: SELECT * FROM public.emails;

Coluna tipo booleano

O tipo booleano armazena o status de 1 bit: verdadeiro (true) ou falso (false).

PostgreSQL Shell (psql)

-- Criar tabelas para armazenar prefixos e IPs: CREATE TABLE IF NOT EXISTS teste_booleanos ( id SERIAL PRIMARY KEY, campo_bool BOOLEAN, descricao TEXT ); -- Valores aceitos: -- Nativo | Sinonimo -- true = 't', 'on', 'ON', 'On', 'yes', 'YES', '1', 1, 129 -- false = 'f', 'off', 'OFF', 'Off', 'no', 'NO', '0', 0, 0 -- Inserir campo booleano: INSERT INTO teste_booleanos (campo_bool, descricao) VALUES (true, 'teste tipo true'),(false, 'teste tipo false'); -- Inserir possibilidades para o campo booleano: INSERT INTO teste_booleanos (campo_bool, descricao) VALUES ('t', 'teste string t'),('f', 'teste string f'); INSERT INTO teste_booleanos (campo_bool, descricao) VALUES ('on', 'teste string on'),('off', 'teste string off'); INSERT INTO teste_booleanos (campo_bool, descricao) VALUES ('ON', 'teste string ON'),('OFF', 'teste string OFF'); INSERT INTO teste_booleanos (campo_bool, descricao) VALUES ('On', 'teste string On'),('oFF', 'teste string oFF'); INSERT INTO teste_booleanos (campo_bool, descricao) VALUES ('yes', 'teste string yes'),('no', 'teste string no'); INSERT INTO teste_booleanos (campo_bool, descricao) VALUES ('YES', 'teste string YES'),('NO', 'teste string NO'); INSERT INTO teste_booleanos (campo_bool, descricao) VALUES ('1', 'teste string 1'),('0', 'teste string 0'); -- Visualizar: SELECT * FROM teste_booleanos ORDER BY campo_bool; SELECT * FROM teste_booleanos WHERE campo_bool ORDER BY campo_bool; SELECT * FROM teste_booleanos WHERE NOT campo_bool ORDER BY descricao; -- Traduzir valores boleanos: SELECT CASE WHEN campo_bool THEN 'Ta ligado' ELSE 'Ta DESLIGADO' END AS situacao, descricao FROM teste_booleanos;

Coluna tipo inteiro simples

Os tipos inteiro simples (SMALLINT, INTEGER, BIGINT) armazenam sem nenhuma condicional. O tipo BIGINT é o mais usado.

PostgreSQL Shell (psql)

-- Nome | Alias | bytes | bits | minimo | maximo -- SMALLINT | int2 | 2 | 16 | -32.768 | 32.767 -- INTEGER | int4,int | 4 | 32 | -2.147.483.648 | 2.147.483.647 -- BIGINT | int8 | 8 | 64 | -9.223 tri. | 9.223 tri. -- Exemplo de tabela: CREATE TABLE IF NOT EXISTS exemplos_int ( valor_i INTEGER, valor_si SMALLINT, valor_bi BIGINT ); -- Inserir nos limites: INSERT INTO exemplos_int (valor_i, valor_si, valor_bi) VALUES ( -2147483648, -32768, -9223372036854775808), ( 2147483647, 32767, +9223372036854775807); -- Exibir: SELECT * FROM exemplos_int; -- Erro ao inserir, ultrapassou limites: INSERT INTO exemplos_int (valor_i, valor_si, valor_bi) VALUES ( -2147483648-1, -32768-1, -9223372036854775808-1), ( 2147483647+1, 32767+1, +9223372036854775807+1);

CRÍTICO: o PG não possui tipos “unsigned“, ou seja, o INTEGER de 32 bits é signed o que resulta na perda de 1 bit para o sinal, somente 31 bits serão usados para dados, assim o limite é entre 2 bilhões e -2 bilhões, não existe a possibilidade de usar 32 bits unsigned para armazenar até 4 bilhões como no MySQL/MariaDB/Percona.

Coluna tipo inteiro serial (sequência automática)

Os tipos serial (SMALLSERIAL, SERIAL, BIGSERIAL) são semelhantes a tipos inteiros que são automaticamente preenchidos com um programa de sequencia (sequence) vistos no capítulo anterior.

Exemplo com demonstração de BUG:

PostgreSQL Shell (psql)

-- Nome | Alias | bytes | bits | minimo | maximo -- SMALLSERIAL| serial2 | 2 | 16 | 1 | 32.767 -- SERIAL | serial4 | 4 | 32 | 1 | -2.147.483.647 -- BIGSERIAL | serial8 | 8 | 64 | 1 | 9.223 tri. -- Exemplo de tabela: CREATE TABLE IF NOT EXISTS exemplo_serial ( id SERIAL PRIMARY KEY, valor_cents BIGINT ); -- Inserir registro: a coluna 'id' será preenchida com (1) INSERT INTO exemplo_serial (valor_cents) VALUES (120191); -- Exibir: SELECT * FROM exemplo_serial; -- Inserir com id especificado manualmente: INSERT INTO exemplo_serial (id, valor_cents) VALUES (2, 9921); -- Inserir com id automatico novamente: INSERT INTO exemplo_serial (valor_cents) VALUES (1437); -- RETORNA ERRO: a sequencia vai fornecer o proximo numero (2), -- que ja existe na tabela, -- a coluna 'id' é chave primaria (PRIMARY KEY) -- e nao vai permitir a duplicacao -- Inserir com id automatico novamente: INSERT INTO exemplo_serial (valor_cents) VALUES (901); -- Vai funcionar, id (3) pois a falha no INSERT anterior -- nao faz rollback no valor fornecido pela sequencia.

Coluna tipo número personalizado

O PG possui o tipo NUMERIC (apelido de DECIMAL) que permite especificar um número com grandeza astronômica impensável (número de átomos do universo * número de segundos na idade do universo). O limite de dígitos abrange números positivos e negativos.

Sintaxe: NUMERIC(total_de_digitos, digitos_depois_da_virgula)

Exemplo:

NUMERIC(5,2): aceita 123.45 (5 dígitos totais, 2 dígitos de precisão);
NUMERIC(10,0): aceita o número máximo 9.999.999.999 (9 bilhões), somente inteiros;
NUMERIC(147455, 16383): argumentos padrões do tipo:
- Total de 147.455 dígitos (à direita e à esquerda);
- 131072 dígitos para a parte inteira;
- 16383 dígitos após a virgula (à esquerda);

Usando:

PostgreSQL Shell (psql)

-- Tabela para armazenamento de numeros gigantes: CREATE TABLE exemplo_numeric1 ( valor NUMERIC ); -- Padrao: -- Aceita QUALQUER número com até 131.072 dígitos antes da vírgula (inteiro) -- e até 16.383 dígitos depois da vírgula (ponto flutuante) -- Armazena EXATAMENTE o valor informado -- Inserir numeros gigantes: INSERT INTO exemplo_numeric1 VALUES (123.456789); INSERT INTO exemplo_numeric1 VALUES (99999999999999999999.123456789123456789); INSERT INTO exemplo_numeric1 VALUES (0.00000000000001); -- Exibir: SELECT * FROM exemplo_numeric1; -- Destruir: DROP TABLE IF EXISTS exemplo_numeric1;

Exemplo para posição GPS:

PostgreSQL Shell (psql)

-- Tabela para armazenar pontos GPS na superficie (sem altitude): CREATE TABLE gps_locations ( id SERIAL PRIMARY KEY, name VARCHAR(100), lat NUMERIC(10, 8), -- -90.00000000 a 90.00000000 lng NUMERIC(11, 8) -- -180.00000000 a 180.00000000 ); -- Cadastrar pontos GPS: INSERT INTO gps_locations (name, lat, lng) VALUES ('São Paulo', -23.55052000, -46.63330820), ('Rio de Janeiro', -22.90684700, -43.17289600), ('Nova Lima', -19.98582500, -43.84696700); -- Exibir: SELECT * FROM gps_locations; -- Destruir: DROP TABLE IF EXISTS gps_locations;

Coluna tipo ponto flutuante (float)

Suporta números com precisão semelhantes ao tipo NUMERIC mas seguindo as regras de ponto flutuante (mantiza) padronizados pelo IEEE 754 (double precision).

Vantagem: São mais adequados que o tipo NUMERIC por serem mais rápidos em operações computacionais.
Desvantagem: Sofrem de todos os problemas de aproximação de tipos float.

PostgreSQL Shell (psql)

-- Nome | Alias | bytes | bits | minimo | maximo -- REAL | float4 | 4 | 32 | -1.175494351E-38 | 3.402823466E+38 -- DOUBLE PRECISION | float8 | 8 | 64 | -2.22507385E-308 | 1.797693134E+308 -- Tipo especial para valores monetarios: -- Nome | bytes | bits | minimo | maximo -- MONEY | 8 | 64 | -92233720368547758.08 | 92233720368547758.07 -- Exemplo de colunas float: CREATE TABLE IF NOT EXISTS exemplos_float ( id SERIAL PRIMARY KEY, vle_real REAL, vle_double DOUBLE PRECISION, vle_money MONEY, descricao TEXT ); -- Inserir registros -- Note que adicionamos alguns valores como o resultado de uma operacao -- envolvendo ponto flutuante, note o valor informado e o valor final -- na visualizacao INSERT INTO exemplos_float (vle_real, vle_double, vle_money, descricao) VALUES ( 3.141592653589793, 3.141592653589793, 99.14, 'PI - teste de precisão'), ( 1.23456789012345, 1.23456789012345, 1.01, 'Muitos dígitos'), ( 0.1 + 0.2, 0.1 + 0.2, 0.02, 'Soma de decimais'), ( 1.0/3.0, 1.0/3.0, 0.03, 'Divisão que gera dízima'), ( 999999.123456789, 999999.123456789, 0.04, 'Número grande com decimais'), ( 0.000000123456789, 0.000000123456789, 0.05, 'Número muito pequeno'); -- Visualizar: SELECT id, vle_real, vle_double, vle_money, vle_real::TEXT AS vrt, vle_double::TEXT AS vdt, vle_money::TEXT AS vmt, descricao FROM exemplos_float; -- Destruir tabela DROP TABLE IF EXISTS exemplos_float;

Coluna tipo UUID

UUIDs (Universally Unique Identifiers), também conhecidos como GUIDs (Globally Unique Identifiers), são identificadores que usam um número de 128 bits (hexadecimal) para gerar uma grande quantidade de identificadores.

UUIDs são úteis para identificação única de objetos em soluções que não contam com conferência centralizada de sequência. Cada ambiente gera livremente seus identificadores baseados em bytes aleatórios, MAC da interface de rede, amostra temporal (timestamp), hash e outros fatores.

Padrões:

ITU-T X.667 / ISO/IEC 9834-8: especificação para UUIDs padrões ISO;
RFC 4122: A Universally Unique IDentifier (UUID) URN Namespace;
RFC 4121: The Kerberos Version 5 GSS-API Mechanism: Version 2;
RFC 8941: Structured Field Values for HTTP;
RFC 9562: Universally Unique IDentifiers (UUIDs);

Existem vários padrão no preenchimento dos 128 bits que compõem o UUID:

Versão	Tipo	Payload	Padrão oficial
1	Time-based	Tempo + MAC	RFC 4122
2	DCE Security	Tempo + UID/GID	RFC 4122
3	Name-based (MD5)	Hash MD5 de namespace+nome	RFC 4122
4	Random	Aleatório	RFC 4122
5	Name-based (SHA-1)	Hash SHA-1 de namespace+nome	RFC 4122
6	Reordenado (time-ordered)	Tempo + MAC, ordenado	draft uuidrev
7	Unix Time + random	Tempo (Unix) + aleatório	draft uuidrev
8	Custom	Definido pela aplicação	draft uuidrev
59	DCE Security Revised	UID/GID	draft uuidrev

O uso de UUID requer extensão:

uuid-ossp: Tradicional, a extensão mais antiga e amplamente utilizada, funções:
- uuid_generate_v1() – UUID baseado em timestamp e endereço MAC;
- uuid_generate_v1mc() – UUID v1 com endereço MAC multicast aleatório;
- uuid_generate_v3(namespace, text) – UUID v3 baseado em hash MD5;
- uuid_generate_v4() – UUID v4 aleatório (mais usado);
- uuid_generate_v5(namespace, text) – UUID v5 baseado em hash SHA-1;
pgcrypto: Recomendada atualmente, mais moderna e oferece junto funções criptográficas, funções:
- gen_random_uuid() – Gerar UUID com alta performance;

Exemplos usando extensão uuid-ossp:

PostgreSQL Shell (psql)

-- Criar banco de dados para testes CREATE DATABASE db_oosp; -- Entrar no DB: \c db_oosp; -- Ativar a extensão legado: CREATE EXTENSION IF NOT EXISTS "uuid-ossp" CASCADE; -- Listar todas as funções da extensão uuid-ossp: \df uuid_* -- Testar funcoes geradoras: SELECT uuid_generate_v1(), NOW(); -- UUID versao 1 SELECT uuid_generate_v1()::text AS uuid_texto; -- UUID versao 1 (string) SELECT uuid_generate_v1mc(); -- UUID v1 com MAC multicast SELECT uuid_generate_v1mc() AS uuid FROM generate_series(1, 5); -- Gerar varios SELECT uuid_generate_v4(); -- UUID versao 4 -- Namespaces pré-definidos (RFC 4122) -- UUID para namespace DNS SELECT 'dns_namespace'::text, '6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid AS namespace; -- UUID para namespace URL SELECT 'url_namespace'::text, '6ba7b811-9dad-11d1-80b4-00c04fd430c8'::uuid AS namespace; -- UUID para namespace OID SELECT 'oid_namespace'::text, '6ba7b812-9dad-11d1-80b4-00c04fd430c8'::uuid AS namespace; -- UUID para namespace X500 SELECT 'x500_namespace'::text, '6ba7b814-9dad-11d1-80b4-00c04fd430c8'::uuid AS namespace; -- Gerar UUID v3 usando namespace DNS SELECT uuid_generate_v3( '6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, -- namespace DNS 'google.com' -- nome FQDN ) AS uuid_v3_dns; -- Gerar UUID v3 usando namespace URL SELECT uuid_generate_v3( '6ba7b811-9dad-11d1-80b4-00c04fd430c8'::uuid, -- namespace URL 'https://meusite.com.br' -- URL ) AS uuid_v3_url; -- Mesmo namespace + mesmo nome = MESMO UUID (determinístico) SELECT uuid_generate_v3('6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, 'abc') AS u1, uuid_generate_v3('6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, 'abc') AS u2, uuid_generate_v3('6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, 'abc') AS u3; -- Nomes diferentes = UUIDs diferentes SELECT uuid_generate_v3('6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, 'user1') AS l1, uuid_generate_v3('6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, 'user2') AS l2, uuid_generate_v3('6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, 'user3') AS l3; -- Gerar UUID para emails SELECT 'joao@x.com' AS email, uuid_generate_v3( '6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, 'john@x.com') AS uuid_email; SELECT 'maria@x.com' AS email, uuid_generate_v3( '6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, 'maria@x.com') AS uuid_email; -- Gerar 10 UUIDs SELECT uuid_generate_v4() AS uuid_rand_v4 FROM generate_series(1, 10); -- Converter para diferentes formatos SELECT uuid_generate_v4() AS uuid_default, uuid_generate_v4()::text AS uuid_text, replace(uuid_generate_v4()::text, '-', '') AS uuid_plain, upper(uuid_generate_v4()::text) AS uuid_lowercase; -- Gerar partes do UUID (cada pedaco vem de um UUID randomico diferente) SELECT uuid_generate_v4() AS uuid_full, split_part(uuid_generate_v4()::text, '-', 1) AS uuid_p1, split_part(uuid_generate_v4()::text, '-', 2) AS uuid_p2, split_part(uuid_generate_v4()::text, '-', 3) AS uuid_p3; -- UUID v5 usando namespace DNS SELECT uuid_generate_v5( '6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, -- namespace DNS 'x.com.br' ) AS uuid_v5; -- Comparar v3 vs v5 para mesma entrada, v5 usa SHA-1, v3 usa MD5 SELECT 'x.com' AS domain, uuid_generate_v3('6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, 'x.com') AS u3, uuid_generate_v5('6ba7b810-9dad-11d1-80b4-00c04fd430c8'::uuid, 'x.com') AS u5; -- UUIDv5 com URLs -- dominio: github.com SELECT 'https://github.com' AS url, uuid_generate_v5( '6ba7b811-9dad-11d1-80b4-00c04fd430c8'::uuid, 'https://github.com' ) AS uuid_github; -- dominio: gitlab.com SELECT 'https://gitlab.com' AS url, uuid_generate_v5( '6ba7b811-9dad-11d1-80b4-00c04fd430c8'::uuid, 'https://gitlab.com' ) AS uuid_gitlab; -- Testar funcionamento de todas as funções da extensão UUID-OSSP: SELECT uuid_nil(); SELECT uuid_generate_v1(); SELECT uuid_generate_v1mc(); SELECT uuid_generate_v4(); SELECT uuid_generate_v3(uuid_ns_dns(), 'x.com'); SELECT uuid_generate_v3(uuid_ns_url(), 'https://x.com'); SELECT uuid_generate_v3(uuid_ns_oid(), '1.3.6.1.2.1.2.2.1.8'); SELECT uuid_generate_v5(uuid_ns_dns(), 'x.com'); SELECT uuid_generate_v5(uuid_ns_url(), 'https://x.com'); SELECT uuid_generate_v5(uuid_ns_oid(), '1.3.6.1.2.1.2.2.1.8'); -- Tabela usando UUID como chave primaria no lugar do 'id' serial CREATE TABLE IF NOT EXISTS event_log ( uuid UUID PRIMARY KEY DEFAULT uuid_generate_v4(), event_log TEXT, createdAt TIMESTAMP DEFAULT NOW() ); -- Inserir registros, o 'uuid' será gerado randomicamente INSERT INTO event_log (event_log) VALUES ('User support - wrong password'); INSERT INTO event_log (event_log) VALUES ('User support - blocked'); -- Visualizar: SELECT * FROM event_log; -- Tabela usando UUIDv1 no lugar do 'id' serial, o UUIDv1 e' baseado -- no tempo e mantem a ordem dos registros CREATE TABLE IF NOT EXISTS system_log ( uuid UUID PRIMARY KEY DEFAULT uuid_generate_v1(), syslog TEXT, createdAt TIMESTAMP DEFAULT NOW() ); -- Inserir registros, o 'uuid' será gerado sequencialmente baseado no tempo preciso INSERT INTO system_log (syslog) VALUES ('System crashed'); INSERT INTO system_log (syslog) VALUES ('System online'); -- Visualizar: SELECT * FROM event_log; -- Destruir tabelas DROP TABLE IF EXISTS event_log; DROP TABLE IF EXISTS system_log; -- Sair do db \c postgres -- Destruir database DROP DATABASE IF EXISTS db_oosp;

Exemplos usando extensão pgcrypto:

PostgreSQL Shell (psql)

-- Criar banco de dados para testes CREATE DATABASE db_crypto; -- Entrar no DB: \c db_crypto; -- Ativar a extensão moderna: CREATE EXTENSION IF NOT EXISTS "pgcrypto" CASCADE; -- Existe apenas 1 funcao geradora de UUID da pgcrypto: SELECT gen_random_uuid(); -- Gerar UUID v4 SELECT gen_random_uuid() AS uuid FROM generate_series(1, 50); -- Gerar 50 UUIDs -- Tabela usando UUID como chave primaria no lugar do 'id' serial CREATE TABLE IF NOT EXISTS event_log ( uuid UUID PRIMARY KEY DEFAULT gen_random_uuid(), event_log TEXT, createdAt TIMESTAMP DEFAULT NOW() ); -- Inserir registros, o 'uuid' será gerado randomicamente INSERT INTO event_log (event_log) VALUES ('User blocked with wrong password'); INSERT INTO event_log (event_log) VALUES ('User removed'); -- Visualizar: SELECT * FROM event_log; -- Destruir tabela DROP TABLE IF EXISTS event_log; -- Sair do DB \c postgres -- Destruir banco de dados DROP DATABASE IF EXISTS db_crypto;

Coluna tipo texto

Os colunas com tipos de texto armazena strings ASCII ou multi byte (UTF-8, UTF-16, UTF-32, LATIN1, etc…).

Tipos:

bpchar ~ CHARACTER(limite) ou CHAR(limite): tipo de coluna texto de tamanho fixo, ocupam o espaço total (preenchimento com espaços em branco até o limite), limite máximo de 10.485.760 caracteres (10 MB);
CHARACTER VARYING(limite) ou VARCHAR(limite): tipo de coluna texto de tamanho variável, ocupa apenas o espaço do dado armazenado, tamanho máximo suportado de 10.485.760 caracteres (10 MB);
TEXT: tipo de coluna para grandes textos, limite de 1.073.741.824 bytes (1 GB);

PostgreSQL Shell (psql)

-- Criar banco de dados para testes CREATE DATABASE db_text; -- Entrar no DB: \c db_text; -- Criar tabela: CREATE TABLE IF NOT EXISTS tb_texts ( id SERIAL PRIMARY KEY, name VARCHAR(50), -- Limite de 50 caracteres iso_country CHAR(2), -- Exatamente 2 caracteres xcode VARCHAR(10), -- Limite de 10 caracteres description TEXT -- Sem limite definido (~10M) ); -- Inserir: INSERT INTO tb_texts (name, iso_country, xcode) VALUES ('John Look', 'BR', 'ABCDEFGHIJ'); -- Inserir com erro - violacao do tamanho da coluna xcode (limite 10, inserido 11) INSERT INTO tb_texts (name, iso_country, xcode) VALUES ('Maria Smith', 'AR', 'ABCDEFGHIJx'); -- error: ERROR: value too long for type character varying(10) -- Visualizar tabela: SELECT * FROM tb_texts; SELECT name, LENGTH(name) AS nlen, LENGTH(xcode) AS xlen FROM tb_texts; -- Destruir tabela DROP TABLE IF EXISTS tb_texts; -- Sair do db \c postgres -- Destruir db DROP DATABASE IF EXISTS db_text;

Coluna tipo endereço de rede

Endereços de rede são usados para armazenar números (hexadecimal) utilizados em diferentes formatos de endereços das camadas de rede (ethernet e roteamento IP).

Tipos:

CIDIR: armazena prefixo IP (IPv4 ou IPv6). O endereço da rede é acompanhada do tamanho em bits da máscara (tamanho do prefixo);
INET: armazena prefixo IP ou IP unitário (IPv4 ou IPv6);
MACADDR: armazena 6 bytes (12 dígitos hexadecimais) do endereço MAC;
MACADDR8: armazena 8 bytes (16 dígitos hexadecimais) do endereço MAC, bom para armazenar conversão de MAC para representação EUI-64 (64 bits do final do endereço IPv6 SLAAC ou Link-Local);

PostgreSQL Shell (psql)

-- Tipos e tamanhos -- cidr - 7 ou 19 bytes - Endereço de rede IPv4 ou IPv6 -- inet - 7 ou 19 bytes - Endereço de rede ou endereço de HOST IPv4 ou IPv6 -- macaddr - 6 bytes - Endereço MAC (MAC Address) -- macaddr8 - 8 bytes - Endereço MAC no formato EUI-64 -- Criar banco de dados para testes CREATE DATABASE db_ipam; -- Entrar no DB: \c db_ipam; -- Tabela para prefixos CREATE TABLE IF NOT EXISTS tb_cidr ( prefix CIDR ); -- Inserir prefixos: INSERT INTO tb_cidr (prefix) VALUES ('192.168.100/25'),('192.168.100.128/25'); INSERT INTO tb_cidr (prefix) VALUES ('172.16/25'),('192.168'),('198.18'); INSERT INTO tb_cidr (prefix) VALUES ('128.1'),('10.1.2'),('10.1.2.3/32'); INSERT INTO tb_cidr (prefix) VALUES ('10'); INSERT INTO tb_cidr (prefix) VALUES ('2001:4f8:3:ba::/64'); INSERT INTO tb_cidr (prefix) VALUES ('::ffff:1.2.3.0/120'); INSERT INTO tb_cidr (prefix) VALUES ('2001:4f8:3:ba:2e0:81ff:fe22:d1f1/128'); INSERT INTO tb_cidr (prefix) VALUES ('::ffff:1.2.3.0/120'); -- Visualizar: SELECT prefix, abbrev(prefix) AS pfx FROM tb_cidr ORDER BY prefix; -- Tabela para IPs e prefixos CREATE TABLE IF NOT EXISTS tb_inet ( addr INET ); -- Inserir IPs ou prefixos: INSERT INTO tb_inet (addr) VALUES ('2001:4f8:3:ba::'),('::ffff:1.2.3.0'); INSERT INTO tb_inet (addr) VALUES ('192.168.100/25'),('192.168.100.128/25'); INSERT INTO tb_inet (addr) VALUES ('10/8'); INSERT INTO tb_inet (addr) VALUES ('2001:0db8:beba:cafe::d1f1/128'); INSERT INTO tb_inet (addr) VALUES ('2001:4f8:3:ba::/64'); INSERT INTO tb_inet (addr) VALUES ('::ffff:1.2.3.0/120'); -- Tipos prefixos que funcionam no CIDR mas nao funcionam no INET: INSERT INTO tb_inet (addr) VALUES ('172.16/25'); -- ERRO, nao funciona no INET INSERT INTO tb_inet (addr) VALUES ('192.168'); -- ERRO, nao funciona no INET INSERT INTO tb_inet (addr) VALUES ('198.18'); -- ERRO, nao funciona no INET INSERT INTO tb_inet (addr) VALUES ('10'); -- ERRO, nao funciona no INET INSERT INTO tb_inet (addr) VALUES ('10/32'); -- ERRO, nao funciona no INET -- Visualizar: SELECT *, abbrev(addr) AS adr FROM tb_inet ORDER BY addr; -- Tabela para MAC (6 bytes) CREATE TABLE IF NOT EXISTS tb_mac ( mac_addr MACADDR ); -- Inserir MACs: INSERT INTO tb_mac (mac_addr) VALUES ('08:00:2b:01:02:03'); INSERT INTO tb_mac (mac_addr) VALUES ('08-00-3c-0a-0b-0c'); INSERT INTO tb_mac (mac_addr) VALUES ('0800-1a10-2030'); INSERT INTO tb_mac (mac_addr) VALUES ('08004d:050607'); INSERT INTO tb_mac (mac_addr) VALUES ('08005e-08090a'); INSERT INTO tb_mac (mac_addr) VALUES ('08cafefed1d0'); -- Visualizar: SELECT * FROM tb_mac ORDER BY mac_addr; -- Tabela para MAC/EUI64 (8 bytes / 64 bits) CREATE TABLE IF NOT EXISTS tb_mac8 ( eui64 MACADDR8 ); -- Inserir MAC formato EUI64 (8 bytes): INSERT INTO tb_mac8 (eui64) VALUES ('08:00:2b:01:02:03:04:05'); INSERT INTO tb_mac8 (eui64) VALUES ('08-00-3c-02-03-04-05-06'); INSERT INTO tb_mac8 (eui64) VALUES ('0800.2fa1.b2c3.d4e5'); INSERT INTO tb_mac8 (eui64) VALUES ('0800-1af1-e2d3-c4b5'); INSERT INTO tb_mac8 (eui64) VALUES ('08004c:0506070809'); INSERT INTO tb_mac8 (eui64) VALUES ('08005d-060708090a'); INSERT INTO tb_mac8 (eui64) VALUES ('08009ff1:f2f3f4f5'); INSERT INTO tb_mac8 (eui64) VALUES ('08003ce1f2e3f4e5'); -- Inserir no formato MAC (6 bytes): INSERT INTO tb_mac8 (eui64) VALUES ('08:00:2b:01:02:03'); INSERT INTO tb_mac8 (eui64) VALUES ('08-00-3c-0a-0b-0c'); INSERT INTO tb_mac8 (eui64) VALUES ('0800-1a10-2030'); INSERT INTO tb_mac8 (eui64) VALUES ('08004d:050607'); INSERT INTO tb_mac8 (eui64) VALUES ('08005e-08090a'); INSERT INTO tb_mac8 (eui64) VALUES ('08cafefed1d0'); -- Visualizar: SELECT eui64, macaddr8_set7bit(eui64) AS eui7b FROM tb_mac8 ORDER BY eui64; -- Destruir tabela DROP TABLE IF EXISTS tb_cidr; DROP TABLE IF EXISTS tb_inet; DROP TABLE IF EXISTS tb_mac; DROP TABLE IF EXISTS tb_mac8; -- Sair do db \c postgres -- Destruir db DROP DATABASE IF EXISTS db_ipam;

Coluna tipo JSON

O tipo JSON é peculiar: normalmente o JSON final de uma API resume uma ou mais tabelas dentro de uma lista de objetos com propriedades profundas.

Aqui, em vez de criar uma coluna para cada campo do JSON, uma única coluna recebe o documento inteiro e passa a armazenar a árvore de listas, objetos, propriedades e valores.

Tipos:

JSON: Armazena dados JSON como texto exato, preserva espaços em branco, ordem das chaves e chaves duplicadas. É mais lento para consultas devido à necessidade de parsing a cada operação (parsing a cada leitura);
JSONB: Armazena dados JSON em formato binário otimizado (parsing somente na primeira gravação), remove espaços em branco desnecessários, não preserva ordem das chaves (usa ordenação própria), remove chaves duplicadas (mantém a última) – recomendado.

Exemplos:

PostgreSQL Shell (psql)

-- Criar banco de dados para testes CREATE DATABASE db_json; -- Entrar no DB: \c db_json; -- Tabela com colunas JSON CREATE TABLE IF NOT EXISTS docs ( id SERIAL PRIMARY KEY, sjson JSON, -- json string/text bjson JSONB -- json binario ); -- Índice GIN simples (recomendado para JSONB) CREATE INDEX idx_docs_bjson ON docs USING GIN (bjson); -- Inserir: INSERT INTO docs (sjson, bjson) VALUES ( '{"nome": "Joao", "idade": 30, "cidade": "Sao Paulo", "hobbies": ["leitura", "xbox"]}', '{"nome": "Joao", "idade": 30, "cidade": "Sao Paulo", "hobbies": ["leitura", "xbox"]}' ); INSERT INTO docs (sjson, bjson) VALUES ( '{"nome": "Maria", "idade": 25, "endereco": {"rua": "Rua A", "numero": 123}, "ativo": true}', '{"nome": "Maria", "idade": 25, "endereco": {"rua": "Rua A", "numero": 123}, "ativo": true}' ); -- Visualizar: SELECT * FROM docs; -- Selecionar registros puxando colunas do JSON para as colunas do SELECT -- Operador -> (retorna JSON) SELECT bjson -> 'nome' AS nome FROM docs; -- Operador ->> (retorna TEXT) SELECT bjson ->> 'nome' AS nome_texto FROM docs; -- Acesso a arrays (índice baseado em 0) SELECT bjson -> 'hobbies' -> 0 AS primeiro_hobby FROM docs; -- Acesso aninhado SELECT bjson -> 'endereco' ->> 'rua' AS rua FROM docs; -- Operador #> (caminho como array) SELECT bjson #> '{endereco,rua}' AS rua FROM docs; -- Operador #>> (caminho como array, retorna text) SELECT bjson #>> '{endereco,rua}' AS rua_texto FROM docs; SELECT * FROM docs WHERE bjson @> '{"idade": 30}'; -- Operador @> (contém) SELECT * FROM docs WHERE '{"nome": "João"}' <@ bjson; -- Operador <@ (está contido) SELECT * FROM docs WHERE bjson ? 'endereco'; -- Operador ? (existe chave) -- Operador ?| (existe alguma das chaves) SELECT * FROM docs WHERE bjson ?| array['endereco', 'telefone']; -- Operador ?& (existem todas as chaves) SELECT * FROM docs WHERE bjson ?& array['nome', 'idade']; -- jsonb_set: Atualizar/adicionar valores UPDATE docs SET bjson = jsonb_set(bjson, '{telefone}', '"11999999999"') WHERE bjson ->> 'nome' = 'João'; -- jsonb_insert: Inserir em posição específica UPDATE docs SET bjson = jsonb_insert(bjson, '{hobbies,1}', '"ciclismo"') WHERE bjson ? 'hobbies'; -- jsonb_strip_nulls: Remove valores null SELECT jsonb_strip_nulls('{"a": 1, "b": null, "c": 3}'); -- Concatenação || UPDATE docs SET bjson = bjson || '{"updated": true}' WHERE id = 1; -- Remoção de chaves - UPDATE docs SET bjson = bjson - 'updated' WHERE id = 1; -- jsonb_agg: Agregar em array JSON SELECT jsonb_agg(bjson ->> 'nome') AS nomes FROM docs; -- jsonb_object_agg: Criar objeto JSON SELECT jsonb_object_agg(bjson ->> 'nome', bjson ->> 'idade') AS nome_idade FROM docs; -- Exemplo de consulta que usa o índice: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM docs WHERE bjson @> '{"cidade": "São Paulo"}'; -- Destruir tabela DROP TABLE IF EXISTS docs; -- Sair do db \c postgres -- Destruir db DROP DATABASE IF EXISTS db_json;

6 – Índices (index)

Índices são tabelas que auxiliam na localização de registros na tabela principal (clausula WHERE e restrições das colunas). Uma tabela pode ter vários índices, cada um ajudando a localizar um registro baseado em uma ou mais colunas indexadas.

CUIDADO: Índices aceleram a leitura mas reduzem a escrita. Toda escrita (INSERT, UPDATE, DELETE) altera a posição dos registros na tabela e força a recriação ou alteração do índice.

Por outro lado, colunas sem índice fazem com que a busca por registros (WHERE, restrições) precisem percorrer registro por registro, o que é lento, consome muita CPU/RAM e I/O de armazenamento.

A solução é sempre usar índices nas colunas necessárias e basear as buscas focadas nessas colunas.

Índices automáticos

Sempre que uma tabela é criada, alguns índices podem ser criados automaticamente para auxiliar na função da coluna.

Tipo padrão dos índices automáticos: B-tree (árvore binária).

Índices automáticos mais comuns:

PRIMARY KEY (PKEY): uma coluna declarada como chave privada (normalmente “id” ou “uuid”), essa coluna não pode repetir e para localizar se há repetições de valores um índice é criado para auxiliar nessa busca. A chave primária é um índice baseado em restrição de valor único (UNIQUE). Coluna PKEY é imune a nullabilidade, ou seja, conta com restrição que não permite valores nulos (NOT NULL);
UNIQUE: uma coluna declarada como UNIQUE implica que não pode haver valor repetido em toda a tabela para aquela coluna, logo, resulta na criação de índice automático;
EXCLUDE: colunas envolvidas em EXCLUDE não podem se sobrepor dentro da restrição.

Exemplos de índices automáticos:

PostgreSQL Shell (psql)

-- Criar banco de dados para testes CREATE DATABASE db_indices; -- Entrar no DB: \c db_indices; -- ------------------------------------------------------------ PRIMARY KEY -- Tabela tabela simples com chave primária no 'id' sequencial: CREATE TABLE IF NOT EXISTS users ( id BIGSERIAL PRIMARY KEY, username VARCHAR(200) NOT NULL, password VARCHAR(128) NOT NULL, status BOOLEAN NOT NULL DEFAULT TRUE ); -- Chave primária (PKEY) na tabela 'users' para a coluna 'id', logo: -- Índice: users_pkey tipo btree -- Verificando indices criados: SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE tablename = 'users'; -- Índice: users_pkey | CREATE UNIQUE INDEX users_pkey ON users USING btree (id) -- Criando tabela com 'id' sequencial (sem PKEY) CREATE TABLE IF NOT EXISTS policies ( id BIGSERIAL, name VARCHAR(200) NOT NULL, rules text ); -- Criar chave primaria após a criação da tabela: -- índice de chave primaria para a tabela 'policies' na coluna 'id' CREATE UNIQUE INDEX policies_pkey ON public.policies USING btree (id); -- ------------------------------------------------------------ UNIQUE -- Índice automático baseado em coluna de valor único (não pode repetir) -- Tabela com colunas JSON CREATE TABLE IF NOT EXISTS clientes ( id BIGSERIAL PRIMARY KEY, name VARCHAR(200) NOT NULL, cpf VARCHAR(128) NOT NULL UNIQUE ); -- Índices criados: -- - clientes_pkey: indice de chave primaria da coluna 'id' -- - clientes_cpf_key: indice de chave única, CPF não pode repetir -- Para criar o índice de 'cpf' após a criação da tabela: CREATE UNIQUE INDEX clientes_cpf_key ON public.clientes USING btree (cpf); -- ------------------------------------------------------------ EXCLUDE -- Criar a extensão de índices de arvore binária GIST: CREATE EXTENSION IF NOT EXISTS btree_gist CASCADE; -- Criar tabela para reservar quartos para hospedes CREATE TABLE hotel_reservas ( id SERIAL PRIMARY KEY, quarto TEXT, periodo tsrange NOT NULL, responsavel TEXT, EXCLUDE USING GIST (quarto WITH =, periodo WITH &&) ); -- Índices criados: -- - hotel_reservas_pkey: indice de chave primaria da coluna 'id' -- - hotel_reservas_quarto_periodo_excl: indice de exclusão que -- impede que o 'quarto' tenha registros onde o 'periodo' se sobreponham -- Exemplo de criacao do indice EXCLUDE acima: CREATE INDEX hotel_reservas_quarto_periodo_excl ON public.hotel_reservas USING gist (quarto, periodo); -- Inserir algumas reservas INSERT INTO hotel_reservas (quarto, periodo, responsavel) VALUES ('Presidencial', '[2024-01-15 09:00, 2024-01-15 11:00)', 'Paulo'), ('Apt 1001', '[2024-01-15 09:00, 2024-01-15 10:00)', 'Maria'); -- Esta inserção funcionará (quarto diferente) INSERT INTO hotel_reservas (quarto, periodo, responsavel) VALUES ('Presidencial', '[2024-01-15 14:00, 2024-01-15 16:00)', 'Tiago'); -- Esta inserção FALHARÁ (conflito de horário no quarto A) INSERT INTO hotel_reservas (quarto, periodo, responsavel) VALUES ('Presidencial', '[2024-01-15 10:30, 2024-01-15 12:00)', 'Marcos'); -- erro: -- ERROR: conflicting key value violates exclusion constraint -- "hotel_reservas_quarto_periodo_excl" -- DETAIL: Key (quarto, periodo)= -- (Presidencial, ["2024-01-15 10:30:00","2024-01-15 12:00:00")) -- conflicts with existing key (quarto, periodo)= -- (Presidencial, ["2024-01-15 09:00:00","2024-01-15 11:00:00")). -- Destruir tabelas DROP TABLE IF EXISTS users; DROP TABLE IF EXISTS clientes; DROP TABLE IF EXISTS hotel_reservas; -- Sair do db \c postgres -- Destruir db DROP DATABASE IF EXISTS db_indices;

Outros exemplos de índices:

PostgreSQL Shell (psql)

-- Criar banco de dados para testes CREATE DATABASE db_indices; -- Entrar no DB: \c db_indices; -- Criar tabela, índice de chave primária em 'id' CREATE TABLE IF NOT EXISTS reclamacoes ( id SERIAL, nome CHAR(120), reclamacao TEXT, createdAt TIMESTAMP DEFAULT NOW(), PRIMARY KEY (id) ); -- Criar diferentes índices para acelerar a busca pelo 'nome' e pela 'reclamacao' -- Índice padrão para buscas exatas CREATE INDEX IF NOT EXISTS idx_reclamacoes_nome ON reclamacoes (nome); -- Ou com nome mais descritivo CREATE INDEX idx_reclamacoes_nome_btree ON reclamacoes USING btree (nome); -- Para buscas sem distinção de maiúsculas/minúsculas CREATE INDEX idx_reclamacoes_nome_lower ON reclamacoes (lower(nome)); -- Para busca textual completa (recomendado para TEXT) CREATE INDEX idx_reclamacoes_reclamacao_gin ON reclamacoes USING gin (to_tsvector('portuguese', reclamacao)); -- Para buscas com LIKE '%palavra%' CREATE EXTENSION IF NOT EXISTS "pg_trgm"; CREATE INDEX idx_reclamacoes_reclamacao_trgm ON reclamacoes USING gin (reclamacao gin_trgm_ops); -- Apagar índices: DROP INDEX IF EXISTS idx_reclamacoes_nome; DROP INDEX IF EXISTS idx_reclamacoes_nome_btree; DROP INDEX IF EXISTS idx_reclamacoes_nome_lower; DROP INDEX IF EXISTS idx_reclamacoes_reclamacao_gin; DROP INDEX IF EXISTS idx_reclamacoes_reclamacao_trgm; -- Destruir tabelas DROP TABLE IF EXISTS reclamacoes; -- Sair do db \c postgres -- Destruir db DROP DATABASE IF EXISTS db_indices;

7 – Restrições (constraints)

Constraints (restrições) são regras definidas em tabelas para garantir a consistência dos dados, impondo limitações sobre os valores das colunas.

Tipos de restrições:

DEFAULT: Preenche o valor padrão em uma coluna em caso de omissão de um valor nos comandos INSERT e UPDATE. Quando usado sozinho não impede que a coluna seja explicitamente preenchida com valor NULL;
NOT NULL: Impede a coluna de receber valor NULL e impede de não receber valor explicito. Comandos INSERT/UPDATE que não informarem valores válidos para a coluna falharão;
NOT NULL DEFAULT: Impede que o valor da coluna seja NULL mas se o valor for omitido nos comandos INSERT/UPDATE o procedimento padrão declarado em DEFAULT será usado para obter um valor válido;
UNIQUE: Impede que o valor da coluna se repita em outro registro, garantido que cada registro tenha um valor único. Implica na criação de um índice automático para localizar policiar os valores atuais para impedir que o INSERT/UPDATE cause a repetição;
PRIMARY KEY: Restrição de chave primária aplica as restrições
xxx