PostgreSQL: Prática Abrangente Básica do PostgreSQL

Última atualização: 2026-08-26

Após terminar as primeiras 6 lições de fundamentos, é hora de unir tudo em um projeto completo — este artigo guia você na construção de um banco de dados de livraria online do zero.

1. O Que Você Vai Aprender

O fluxo completo da análise de requisitos ao design de tabelas
Criar um banco de dados e alternar conexões
Projetar múltiplas tabelas relacionadas com restrições
Inserções em lote e importação de dados com UPSERT
Consultas simples para verificar a correção dos dados
Modificar estruturas de tabelas e limpeza

2. A História Real de uma Desenvolvedora Solo

(1) A Dor: Construindo o Banco de Dados Sozinha

Alice é uma desenvolvedora solo que precisa configurar o banco de dados para um projeto de livraria online. Seus requisitos são:

5 tabelas principais: users, books, categories, orders, reviews
O email do usuário deve ser único; a senha não pode estar vazia
O preço do livro deve ser > 0; ISBN deve ser único
Pedidos vinculam usuários e livros
Avaliações devem ter uma nota (1–5)
Ela precisa importar um lote de dados iniciais; alguns livros podem já existir (exigindo UPSERT)

(2) O Fluxo Completo de Construção do Banco de Dados

Alice completa a inicialização do banco de dados do zero com estes passos:

SQL

-- Passo 1: Criar banco de dados
CREATE DATABASE bookstore;

-- Passo 2: Conectar ao novo banco de dados
\c bookstore

-- Passos 3-7: Criar tabelas com tipos e restrições adequadas
-- (SQL completo abaixo na Seção 6)

(3) O Resultado

Experiência completa de design de banco de dados (dos requisitos ao SQL)
5 tabelas + restrições + cascatas de chave estrangeira = design de nível de produção
Importação em lote com UPSERT = uma habilidade do mundo real
Concluído em 30 minutos = a capacidade de entregar sozinha

3. Análise de Requisitos

(1) Diagrama ER da Livraria Online

erDiagram
    USERS ||--o{ ORDERS : faz
    BOOKS ||--o{ ORDER_ITEMS : contém
    CATEGORIES ||--o{ BOOKS : possui
    USERS ||--o{ REVIEWS : escreve
    BOOKS ||--o{ REVIEWS : recebe
    ORDERS ||--|| ORDER_ITEMS : inclui

    USERS {
        int id PK
        varchar email UK
        varchar name
        varchar password_hash
        timestamptz created_at
    }
    CATEGORIES {
        int id PK
        varchar name UK
        text description
    }
    BOOKS {
        int id PK
        varchar isbn UK
        varchar title
        decimal price
        int stock
        int category_id FK
        jsonb metadata
    }
    ORDERS {
        int id PK
        int user_id FK
        varchar status
        decimal total
        timestamptz created_at
    }
    ORDER_ITEMS {
        int id PK
        int order_id FK
        int book_id FK
        int quantity
        decimal unit_price
    }
    REVIEWS {
        int id PK
        int user_id FK
        int book_id FK
        smallint rating
        text content
        timestamptz created_at
    }

(2) Lista de Tabelas e Restrições

Tabela	Colunas	Restrições principais
users	5	email UNIQUE NOT NULL, password_hash NOT NULL
categories	3	name UNIQUE
books	7	isbn UNIQUE, price > 0 CHECK, stock >= 0 CHECK, category_id FK
orders	5	user_id FK CASCADE, status CHECK, total >= 0 CHECK
order_items	5	order_id FK CASCADE, book_id FK RESTRICT, quantity > 0 CHECK
reviews	6	user_id FK SET NULL, book_id FK CASCADE, rating 1-5 CHECK

4. Construa Passo a Passo

(1) Criar o Banco de Dados e Conectar

▶ Exemplo: Criando o Banco de Dados da Livraria

SQL

-- Cria o banco de dados da livraria
CREATE DATABASE bookstore
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

-- Conecta ao novo banco de dados
\c bookstore

Output:

TEXT 📖 Somente leitura

CREATE TABLE

(2) Criar a Tabela de Categorias

▶ Exemplo: A Tabela categories

SQL

-- Tabela de categorias: gêneros de livros
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

-- Insere categorias iniciais
INSERT INTO categories (name, description) VALUES
    ('Programming', 'Livros sobre desenvolvimento de software e linguagens de programação'),
    ('Database', 'Design de banco de dados, SQL e gerenciamento de dados'),
    ('Data Science', 'Aprendizado de máquina, estatística e análise de dados'),
    ('DevOps', 'CI/CD, computação em nuvem e infraestrutura'),
    ('Web Development', 'Tecnologias web frontend e backend');

Output:

TEXT 📖 Somente leitura

INSERT 0 1

(3) Criar a Tabela de Usuários

▶ Exemplo: A Tabela users

SQL

-- Tabela de usuários: clientes da livraria
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insere usuários de exemplo
INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$hash_alice_1234567890abcdefghijklmnopqrs'),
    ('bob@example.com', 'Bob', '$2a$12$hash_bob_1234567890abcdefghijklmnopqrstuvwx'),
    ('charlie@example.com', 'Charlie', '$2a$12$hash_charlie_1234567890abcdefghijklmno')
RETURNING id, email, name;

Output:

TEXT 📖 Somente leitura

INSERT 0 1

(4) Criar a Tabela de Livros

▶ Exemplo: A Tabela books

SQL

-- Tabela de livros: o produto principal
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    title VARCHAR(300) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insere livros de exemplo com metadados
INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 39.99, 120, 1,
     '{"author": "Brett Slatkin", "pages": 352, "edition": 2}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 44.99, 80, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781491910368', 'Python Data Science Handbook', 49.99, 60, 3,
     '{"author": "Jake VanderPlas", "pages": 548}'::jsonb),
    ('9781098118283', 'Kubernetes Up and Running', 54.99, 45, 4,
     '{"author": "Brendan Burns", "pages": 300, "edition": 3}'::jsonb),
    ('9781718500417', 'CSS in Depth', 42.99, 90, 5,
     '{"author": "Keith Grant", "pages": 432}'::jsonb)
RETURNING id, title, price;

Output:

TEXT 📖 Somente leitura

INSERT 0 1

(5) Criar as Tabelas de Pedidos e Itens de Pedido

▶ Exemplo: As Tabelas orders + order_items

SQL

-- Tabela de pedidos
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Tabela de itens de pedido
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0)
);

-- Insere um pedido de exemplo
INSERT INTO orders (user_id, status, total_amount) VALUES
    (1, 'paid', 84.98)
RETURNING id;

-- Insere itens de pedido (assume id do pedido = 1)
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
    (1, 1, 1, 39.99),
    (1, 2, 1, 44.99);

Output:

TEXT 📖 Somente leitura

INSERT 0 1

(6) Criar a Tabela de Avaliações

▶ Exemplo: A Tabela reviews

SQL

-- Tabela de avaliações: avaliações de usuários para livros
CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insere avaliações de exemplo
INSERT INTO reviews (user_id, book_id, rating, title, content) VALUES
    (1, 1, 5, 'Excellent Python Tips',
     'Este livro cobre muitas dicas práticas de Python que uso diariamente no meu trabalho.'),
    (2, 2, 4, 'Good PostgreSQL Introduction',
     'Ótimo para iniciantes, mas poderia ter mais tópicos avançados como particionamento.'),
    (3, 3, 5, 'Must-have for Data Scientists',
     'Cobertura abrangente de NumPy, Pandas, Matplotlib e Scikit-learn.')
RETURNING id, rating, title;

Output:

TEXT 📖 Somente leitura

INSERT 0 1

5. Importação em Lote com UPSERT

▶ Exemplo: Importando Livros em Lote (alguns já existem)

SQL

-- Importa novo lote de livros: alguns já existem (por isbn), outros são novos
INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 35.99, 150, 1,
     '{"author": "Brett Slatkin", "pages": 352, "edition": 2}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 49.99, 100, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781119557265', 'SQL for Data Analysis', 34.99, 200, 2,
     '{"author": "Ulka Rodgers", "pages": 288}'::jsonb),
    ('9781484254555', 'PostgreSQL High Performance', 59.99, 30, 2,
     '{"author": "Ibragimov", "pages": 400}'::jsonb)
ON CONFLICT (isbn)
DO UPDATE SET
    price = EXCLUDED.price,
    stock = books.stock + EXCLUDED.stock
RETURNING id, title,
    CASE WHEN xmax = 0 THEN 'NEW' ELSE 'UPDATED' END AS operation;

Output:

TEXT 📖 Somente leitura

 id |            title             | operation
----+------------------------------+-----------
  1 | Effective Python             | UPDATED
  2 | Learning PostgreSQL          | UPDATED
  6 | SQL for Data Analysis        | NEW
  7 | PostgreSQL High Performance  | NEW

6. Consultas de Verificação

▶ Exemplo: Verificação de Integridade dos Dados

SQL

-- 1. Conta registros em cada tabela
SELECT 'users' AS table_name, COUNT(*) FROM users
UNION ALL SELECT 'categories', COUNT(*) FROM categories
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews;

-- 2. Verifica relacionamentos de chave estrangeira
SELECT o.id AS order_id, u.name AS customer,
       b.title, oi.quantity, oi.unit_price
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN books b ON oi.book_id = b.id;

-- 3. Verifica se as restrições funcionam (deve falhar)
-- INSERT INTO books (isbn, title, price, stock) VALUES ('test', 'Test', -10, 5);
-- ERROR: a restrição check "books_price_check" foi violada

-- INSERT INTO reviews (user_id, book_id, rating) VALUES (1, 1, 6);
-- ERROR: a restrição check "reviews_rating_check" foi violada

-- 4. Verifica resultados do UPSERT
SELECT isbn, title, price, stock
FROM books
WHERE isbn IN ('9780134685991', '9781119557265')
ORDER BY isbn;

Output:

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

7. Modificar a Estrutura da Tabela

▶ Exemplo: Melhorias Iterativas com ALTER TABLE

SQL

-- Adiciona uma coluna published_year a books
ALTER TABLE books ADD COLUMN published_year INTEGER;

-- Adiciona uma flag is_featured a books
ALTER TABLE books ADD COLUMN is_featured BOOLEAN DEFAULT false;

-- Atualiza published_year a partir do JSONB metadata
UPDATE books
SET published_year = (metadata->>'year')::INTEGER
WHERE metadata ? 'year';

-- Adiciona uma coluna phone a users
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Verifica alterações
\d books
\d users

Output:

TEXT 📖 Somente leitura

-- Comando SQL executado com sucesso

8. Exemplo Completo: Script de Inicialização com Um Clique

SQL

-- ============================================
-- Exemplo completo: Script de inicialização do banco de dados da livraria
-- Execute a partir do psql conectado ao banco de dados postgres
-- ============================================

-- 1. Cria banco de dados
CREATE DATABASE bookstore
    WITH ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.utf8'
    LC_CTYPE = 'en_US.utf8';

-- 2. Conecta ao novo banco de dados
\c bookstore

-- 3. Cria todas as tabelas na ordem de dependência
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    title VARCHAR(300) NOT NULL,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    metadata JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(12, 2) DEFAULT 0 CHECK (total_amount >= 0),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0)
);

CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
    book_id INTEGER NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Insere dados de exemplo
INSERT INTO categories (name, description) VALUES
    ('Programming', 'Desenvolvimento de software e linguagens de programação'),
    ('Database', 'Design de banco de dados, SQL e gerenciamento de dados'),
    ('Data Science', 'Aprendizado de máquina e análise de dados');

INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$hash_alice'),
    ('bob@example.com', 'Bob', '$2a$12$hash_bob');

INSERT INTO books (isbn, title, price, stock, category_id, metadata) VALUES
    ('9780134685991', 'Effective Python', 39.99, 120, 1,
     '{"author": "Brett Slatkin", "pages": 352}'::jsonb),
    ('9780596007270', 'Learning PostgreSQL', 44.99, 80, 2,
     '{"author": "Regina Obe", "pages": 500}'::jsonb),
    ('9781491910368', 'Python Data Science Handbook', 49.99, 60, 3,
     '{"author": "Jake VanderPlas", "pages": 548}'::jsonb);

INSERT INTO orders (user_id, total_amount) VALUES (1, 84.98);
INSERT INTO order_items (order_id, book_id, quantity, unit_price) VALUES
    (1, 1, 1, 39.99), (1, 2, 1, 44.99);

INSERT INTO reviews (user_id, book_id, rating, title, content) VALUES
    (1, 1, 5, 'Excellent', 'Dicas práticas de Python.'),
    (2, 2, 4, 'Good intro', 'Ótimo para iniciantes.');

-- 5. Verifica
SELECT 'categories' AS t, COUNT(*) FROM categories
UNION ALL SELECT 'users', COUNT(*) FROM users
UNION ALL SELECT 'books', COUNT(*) FROM books
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'reviews', COUNT(*) FROM reviews;

Output:

TEXT 📖 Somente leitura

     t      | count
------------+-------
 categories |     3
 users      |     2
 books      |     3
 orders     |     1
 reviews    |     2

❓ Perguntas Frequentes

P: A ordem em que as tabelas são criadas importa? R: Sim, importa! Porque a tabela que uma chave estrangeira referencia deve já existir. A ordem correta é: crie primeiro as tabelas referenciadas (categories, users), depois as tabelas que as referenciam (books, orders, reviews) e, por fim, a tabela de ligação (order_items).

P: Como escolher entre ON DELETE CASCADE e RESTRICT? R: Use CASCADE quando as linhas filhas devem ser excluídas junto com a linha pai (ex.: excluir um usuário também exclui seus pedidos). Use RESTRICT quando uma linha pai não deve ser excluída se existirem linhas filhas (ex.: um livro referenciado por itens de pedido não pode ser excluído). Regra geral: use CASCADE onde o negócio permite exclusões em cascata e RESTRICT onde não permite.

P: O que xmax = 0 significa em um UPSERT? R: xmax é uma coluna de sistema do PostgreSQL que registra o ID da transação de exclusão/atualização naquela linha. Um INSERT não define xmax (valor é 0); UPDATE/DELETE o definem. Portanto, xmax = 0 significa que a linha foi recém-inserida, enquanto xmax != 0 significa que foi atualizada. Este é um truque interno do PostgreSQL.

P: Qual a diferença entre uma tabela temporária (TEMP TABLE) e uma tabela normal? R: Uma tabela temporária é visível apenas na sessão atual e é removida automaticamente quando a sessão termina. É boa para armazenar dados intermediários de importação ou resultados de computação temporária. Vantagens: não polui o namespace público e não requer limpeza manual.

P: Qual é o tipo do campo year dentro do JSONB metadata? R: Números dentro do JSONB são numeric por padrão. Para extrair, use ->>'year' (retorna TEXT) e depois converta com ::INTEGER. Se você consultar por ano com frequência, adicione uma coluna GENERATED ou um índice de expressão.

P: Este banco de dados de livraria pode ser usado em produção? R: Estruturalmente sim, mas ainda faltam vários elementos essenciais para produção: 1) ajuste de índices (lições posteriores); 2) campos de auditoria (updated_at); 3) exclusões lógicas (deleted_at); 4) privilégios de usuário do banco de dados (conexões não-root); 5) uma estratégia de backup. As lições posteriores adicionarão esses elementos gradualmente.

📖 Resumo

O fluxo de design de banco de dados: análise de requisitos → diagrama ER → ordem de criação de tabelas → design de restrições → importação de dados → verificação
A ordem de criação de tabelas segue as dependências de chave estrangeira: tabelas referenciadas primeiro, tabelas referenciadoras depois
Restrições protegem a qualidade dos dados: UNIQUE (unicidade) / CHECK (validação condicional) / FK (integridade referencial) / NOT NULL (não vazio)
Importação em lote com UPSERT: ON CONFLICT DO UPDATE lida com dados duplicados
A cláusula RETURNING verifica os resultados da operação
ALTER TABLE melhora iterativamente a estrutura da tabela (adiciona colunas, altera restrições)
Consultas abrangentes verificam a integridade dos dados: JOIN para combinar tabelas + COUNT para estatísticas

📝 Exercícios

Básico (★): Seguindo os passos desta lição, crie o banco de dados da livraria e todas as 6 tabelas do zero, insira os dados de exemplo e execute as consultas de verificação para garantir que a contagem de linhas de cada tabela corresponda aos exemplos.
Intermediário (★★): Execute um UPSERT no banco de dados da livraria: importe 4 registros de livros (2 dos quais têm ISBNs existentes). Para livros existentes, atualize o preço e aumente o estoque; livros novos insira normalmente. Use RETURNING para mostrar se cada registro é NEW ou UPDATED.
Desafio (★★★): Adicione uma tabela de ligação muitos-para-muitos book_authors ao banco de dados da livraria (um livro pode ter vários autores e um autor pode escrever vários livros), além de uma tabela authors. Projete a estrutura da tabela (com chaves estrangeiras e restrições), insira 3 autores e os dados de ligação, depois escreva uma consulta JOIN que mostre todos os nomes de autores para cada livro.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós