Tutorial de PostgreSQL com exemplos para executar
Um tutorial gratuito de PostgreSQL com consultas que rodam na página: tipos de dados, CREATE TABLE, upserts, JSONB, datas, CTEs, funções de janela, índices e VACUUM. Edite a consulta e rode de novo.
Começar o cursoPrimeiros passos
- PostgreSQL vs MySQLPostgreSQL e MySQL são bancos de dados relacionais gratuitos. O PostgreSQL tem SQL mais completo, JSONB e DDL transacional; o MySQL é mais simples de operar onde já é o padrão.
- PostgreSQL vs SQL ServerO PostgreSQL é gratuito e open source; o SQL Server é o banco comercial da Microsoft, com T-SQL e ferramentas centradas no Windows. Veja como diferem o SQL, o JSON, os locks e as licenças.
- PostgreSQL vs MongoDBO PostgreSQL guarda linhas em tabelas com schema fixo e pode guardar documentos JSON em colunas jsonb; o MongoDB guarda documentos de schema flexível em coleções e os distribui entre servidores.
- Listar bancos e tabelasNo psql, \l lista os bancos de dados, \c troca para um deles e \dt lista as tabelas. Em SQL, consulte pg_database, information_schema.tables e information_schema.columns.
Tabelas e tipos
- Tipos de dadosOs tipos de dados do PostgreSQL que você vai usar em quase toda coluna: inteiros, numeric, text, boolean, timestamptz, uuid, jsonb, arrays e enums, e quando escolher cada um.
- CREATE TABLEO CREATE TABLE define as colunas, os tipos e as restrições de uma tabela. Inclui chaves identity, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS e LIKE.
- ALTER TABLEO ALTER TABLE altera uma tabela que já existe: adicionar, renomear e remover colunas, mudar o tipo de uma coluna com USING, definir valor padrão e NOT NULL e adicionar restrições.
- Chaves primáriasComo definir uma chave primária no PostgreSQL e como gerá-la: colunas identity, o antigo SERIAL e UUIDs com gen_random_uuid(). Também chaves compostas e como resetar uma sequence.
- Chaves estrangeirasUma chave estrangeira faz uma coluna aceitar só valores que existem em outra tabela. Inclui REFERENCES, ON DELETE CASCADE, SET NULL e RESTRICT, como adicionar a chave a uma tabela existente e como indexá-la.
- Tipos enumUm tipo enum é uma lista fixa e ordenada de valores permitidos, criada com CREATE TYPE ... AS ENUM. Como adicionar e renomear valores, listar, ordenar e quando uma restrição CHECK é a melhor escolha.
- Timestampstimestamptz guarda um instante e o mostra no fuso horário da sessão; timestamp guarda um horário de relógio sem fuso. Nenhum dos dois guarda o fuso que você escreveu. Use timestamptz para quase tudo.
Consultas
- JoinsUm join combina linhas de duas tabelas por uma condição. INNER JOIN mantém as correspondências, LEFT JOIN todas as linhas da primeira tabela, FULL JOIN as das duas, CROSS JOIN combina tudo e LATERAL roda uma subconsulta por linha.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort fica com a primeira linha de cada grupo, como o último pedido de cada cliente. Só existe no PostgreSQL.
- COALESCE, NULLIF e CASECOALESCE(a, b, ...) retorna o primeiro argumento que não é NULL, NULLIF(a, b) transforma um valor em NULL e CASE WHEN escolhe um valor por condição.
- LIKE, ILIKE e regexLIKE compara texto com um padrão usando % e _, ILIKE faz o mesmo ignorando maiúsculas, e os operadores ~ aplicam expressões regulares POSIX.
- CAST e ::CAST(value AS type) e value::type convertem um valor para outro tipo no PostgreSQL. As duas fazem a mesma conversão; :: é a sintaxe curta do PostgreSQL.
Alterar dados
- Upsert (ON CONFLICT)Upsert no PostgreSQL com INSERT ... ON CONFLICT: DO UPDATE com EXCLUDED, DO NOTHING, updates condicionais, contadores e como diferenciar linhas inseridas de atualizadas.
- RETURNINGRETURNING faz INSERT, UPDATE, DELETE e MERGE devolverem as linhas que alteraram: ids gerados, valores novos, linhas apagadas e qual ação do MERGE foi executada.
- TransaçõesComo funcionam as transações no PostgreSQL: BEGIN, COMMIT e ROLLBACK, savepoints, o que um erro faz com uma transação aberta e os níveis de isolamento.
JSON e arrays
- JSONBGuarde e consulte JSON no PostgreSQL com jsonb: os operadores -> e ->>, contenção com @>, jsonb_set e outras atualizações, transformar arrays em linhas, índices GIN e json vs jsonb.
- ArraysColunas do tipo array no PostgreSQL: como declarar, índices a partir de 1, como verificar se um array contém um valor com ANY, @> e &&, unnest e as funções de array que você precisa.
- string_agg e array_aggstring_agg junta os valores de um grupo num único texto e array_agg os reúne num array. Ordenação, DISTINCT, FILTER, tratamento de NULL e os erros mais comuns.
Strings e datas
- Funções de textoAs funções de texto do PostgreSQL que você usa todo dia: substring, left e right, || e concat, split_part, position, replace, regexp_replace, trim, lpad e format.
- Funções de dataTrabalhe com datas no PostgreSQL: now() e current_date, somar e subtrair intervalos, date_trunc para agrupar, extract e age, e formatação com to_char.
- generate_seriesO generate_series retorna um conjunto de linhas de um valor inicial até um final: números, datas e timestamps. Use para preencher lacunas em relatórios, gerar dados de teste e montar calendários.
Consultas avançadas
- CTE (WITH)Uma expressão de tabela comum dá nome a uma subconsulta numa cláusula WITH para que a consulta principal a leia como uma tabela. WITH RECURSIVE permite que ela se refira a si mesma para percorrer árvores e grafos.
- Funções de janelaFunções de janela calculam um valor para cada linha a partir de um grupo de linhas relacionadas, sem juntá-las: rankings, totais acumulados, médias móveis e comparações com a linha anterior.
Desempenho e manutenção
- ÍndicesO CREATE INDEX cria por padrão um índice B-tree para o PostgreSQL achar linhas sem ler a tabela inteira. Outros tipos (GIN, GiST, BRIN, hash) cobrem JSONB, arrays, busca de texto completo, intervalos e tabelas muito grandes.
- EXPLAIN ANALYZEO EXPLAIN mostra o plano que o PostgreSQL escolhe para uma consulta, e o EXPLAIN ANALYZE executa a consulta e acrescenta as linhas e os tempos reais de cada etapa. Aprenda a ler a árvore do plano e os nós mais comuns.
- Views e views materializadasUma view é uma consulta salva que roda toda vez que você a lê. Uma view materializada guarda o resultado da consulta e só muda quando você roda REFRESH MATERIALIZED VIEW.
- VACUUMO VACUUM remove as versões mortas de linhas que UPDATE e DELETE deixam para trás, para que o espaço possa ser reutilizado. O autovacuum roda isso por você; esta página mostra quando, como ajustar e quando o VACUUM FULL compensa o lock.
Funções e triggers
- Funções e proceduresO CREATE FUNCTION define uma função que você chama dentro das consultas, escrita em SQL ou PL/pgSQL. O CREATE PROCEDURE define uma rotina que você roda com CALL e que pode fazer commit no meio do caminho.
- TriggersUm trigger executa uma função automaticamente quando linhas são inseridas, atualizadas ou apagadas. No PostgreSQL você escreve uma função de trigger que retorna trigger e a liga a uma tabela com CREATE TRIGGER.