Instalação e Fundamentos

Instalação

Docker

# Iniciar PostgreSQL 16
docker run -d \
    --name postgres \
    -e POSTGRES_PASSWORD=senha123 \
    -e POSTGRES_DB=meubanco \
    -p 5432:5432 \
    -v pgdata:/var/lib/postgresql/data \
    postgres:16

# Conectar
docker exec -it postgres psql -U postgres

apt (Debian/Ubuntu)

# Adicionar repositório oficial
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update

# Instalar PostgreSQL 16
sudo apt install postgresql-16 postgresql-client-16 -y

# Iniciar serviço
sudo systemctl start postgresql
sudo systemctl enable postgresql

psql — Cliente Interativo

# Conectar ao banco
psql -U postgres -h localhost
psql -U postgres -d meubanco

# Comandos \ (meta-comandos)
\l                    # listar databases
\dt                   # listar tabelas
\d tabela             # descrever tabela
\du                   # listar roles
\dn                   # listar schemas
\di                   # listar indexes
\df                   # listar funções
\dv                   # listar views
\dx                   # listar extensões
\c banco              # conectar a banco
\h                    # help de comandos SQL
\?                    # help de meta-comandos
\q                    # sair

# Executar arquivo SQL
\i /caminho/script.sql

# Timing
\timing on

Databases e Tablespaces

-- Criar database
CREATE DATABASE meubanco;
CREATE DATABASE meubanco OWNER app_user ENCODING 'UTF8' LC_COLLATE 'pt_BR.UTF-8';

-- Listar databases
SELECT datname, datistemplate, datallowconn FROM pg_database;

-- Tablespace
CREATE TABLESPACE fastspace LOCATION '/ssd/postgres/data';
CREATE DATABASE analitico TABLESPACE fastspace;

-- Schema
CREATE SCHEMA vendas;
CREATE TABLE vendas.clientes (id INT, nome TEXT);
SET search_path TO vendas, public;

Data Types

-- Numéricos
SMALLINT          -- 2 bytes, -32768 a 32767
INTEGER (INT)     -- 4 bytes, -2^31 a 2^31-1
BIGINT            -- 8 bytes
NUMERIC(10,2)     -- exato, 10 dígitos, 2 decimais
REAL              -- 4 bytes, precisão simples
DOUBLE PRECISION  -- 8 bytes, precisão dupla
SMALLSERIAL       -- auto-incremento 2 bytes
SERIAL            -- auto-incremento 4 bytes
BIGSERIAL         -- auto-incremento 8 bytes

-- Texto
CHAR(N)           -- fixo, preenchido com espaço
VARCHAR(N)        -- variável com limite
TEXT              -- variável sem limite

-- Booleano
BOOLEAN           -- true / false / NULL

-- JSON
JSON              -- validado, mantém formato original
JSONB             -- binário, indexável, operadores @>, ?, ||

-- Array
INT[]             -- array de inteiros
TEXT[][]          -- array multidimensional
TEXT[] ARRAY[4]   -- array com tamanho fixo

-- ENUM
CREATE TYPE status_pedido AS ENUM ('pendente', 'pago', 'cancelado');

-- UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE usuarios (id UUID DEFAULT gen_random_uuid(), nome TEXT);

-- Interval
INTERVAL          -- '1 day', '2 hours', '3 months'
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

-- tsvector / tsquery (full-text search)
tsvector           -- 'gato':1 'cachorro':2
tsquery            -- 'gato' & 'cachorro'

-- Geométricos
POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE

-- Range
INT4RANGE, INT8RANGE, NUMRANGE, TSRANGE, TSTZRANGE, DATERANGE

CREATE TABLE — Constraints

CREATE TABLE clientes (
    id          INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nome        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE,
    cpf         CHAR(11) UNIQUE NOT NULL,
    data_nasc   DATE,
    altura      NUMERIC(3,2) CHECK (altura > 0 AND altura < 3),
    ativo       BOOLEAN DEFAULT true,
    criado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE categorias (
    id   SERIAL PRIMARY KEY,
    nome TEXT NOT NULL
);

CREATE TABLE produtos (
    id          SERIAL PRIMARY KEY,
    nome        VARCHAR(200) NOT NULL,
    preco       NUMERIC(10,2) NOT NULL CHECK (preco >= 0),
    categoria_id INTEGER NOT NULL REFERENCES categorias(id),
    estoque     INTEGER DEFAULT 0 CHECK (estoque >= 0),
    descricao   TEXT,
    metadata    JSONB DEFAULT '{}',
    tags        TEXT[] DEFAULT '{}',
    criado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_produtos_categoria ON produtos(categoria_id);
CREATE INDEX idx_produtos_metadata ON produtos USING GIN(metadata);

SERIAL vs IDENTITY

-- SERIAL (legado, cria sequência automaticamente)
CREATE TABLE t1 (id SERIAL PRIMARY KEY, nome TEXT);

-- IDENTITY (SQL Standard, PostgreSQL 10+)
CREATE TABLE t2 (id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, nome TEXT);
-- ALWAYS: não aceita inserção manual
INSERT INTO t2 (id, nome) VALUES (999, 'teste'); -- ERRO

CREATE TABLE t3 (id INTEGER GENERATED BY DEFAULT AS IDENTITY, nome TEXT);
-- BY DEFAULT: aceita inserção manual, ajusta sequência
INSERT INTO t3 (id, nome) VALUES (100, 'manual'); -- OK

Generated Columns

CREATE TABLE pedidos (
    id          SERIAL PRIMARY KEY,
    quantidade  INTEGER NOT NULL,
    preco_unit  NUMERIC(10,2) NOT NULL,
    subtotal    NUMERIC(10,2) GENERATED ALWAYS AS (quantidade * preco_unit) STORED,
    criado_em   TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO pedidos (quantidade, preco_unit) VALUES (3, 29.90);
SELECT subtotal FROM pedidos; -- 89.70

Lab: Schema de E-commerce

-- 01-schema-ecommerce.sql
CREATE DATABASE ecommerce;

\c ecommerce

-- Extensões
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ENUMs
CREATE TYPE status_pedido AS ENUM ('pendente', 'confirmado', 'enviado', 'entregue', 'cancelado');
CREATE TYPE tipo_endereco AS ENUM ('cobranca', 'entrega');

-- Tabelas
CREATE TABLE clientes (
    id          UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    nome        VARCHAR(150) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    cpf         CHAR(11) UNIQUE NOT NULL,
    telefone    VARCHAR(20),
    data_cadastro TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE enderecos (
    id            UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    cliente_id    UUID NOT NULL REFERENCES clientes(id) ON DELETE CASCADE,
    tipo          tipo_endereco NOT NULL DEFAULT 'entrega',
    logradouro    VARCHAR(255) NOT NULL,
    numero        VARCHAR(10),
    complemento   VARCHAR(100),
    bairro        VARCHAR(100),
    cidade        VARCHAR(100) NOT NULL,
    estado        CHAR(2) NOT NULL,
    cep           CHAR(8) NOT NULL,
    principal     BOOLEAN DEFAULT false
);

CREATE TABLE categorias (
    id          SERIAL PRIMARY KEY,
    nome        VARCHAR(100) NOT NULL,
    slug        VARCHAR(100) UNIQUE NOT NULL,
    descricao   TEXT,
    ativo       BOOLEAN DEFAULT true
);

CREATE TABLE produtos (
    id              UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    categoria_id    INTEGER REFERENCES categorias(id),
    nome            VARCHAR(200) NOT NULL,
    slug            VARCHAR(200) UNIQUE NOT NULL,
    descricao       TEXT,
    preco           NUMERIC(10,2) NOT NULL CHECK (preco >= 0),
    estoque         INTEGER NOT NULL DEFAULT 0 CHECK (estoque >= 0),
    ativo           BOOLEAN DEFAULT true,
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE pedidos (
    id              UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    cliente_id      UUID NOT NULL REFERENCES clientes(id),
    status          status_pedido NOT NULL DEFAULT 'pendente',
    total           NUMERIC(10,2) NOT NULL DEFAULT 0,
    criado_em       TIMESTAMPTZ DEFAULT NOW(),
    atualizado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE itens_pedido (
    id          UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    pedido_id   UUID NOT NULL REFERENCES pedidos(id) ON DELETE CASCADE,
    produto_id  UUID NOT NULL REFERENCES produtos(id),
    quantidade  INTEGER NOT NULL CHECK (quantidade > 0),
    preco_unit  NUMERIC(10,2) NOT NULL,
    subtotal    NUMERIC(10,2) GENERATED ALWAYS AS (quantidade * preco_unit) STORED
);

-- Índices
CREATE INDEX idx_pedidos_cliente ON pedidos(cliente_id);
CREATE INDEX idx_pedidos_status ON pedidos(status);
CREATE INDEX idx_itens_pedido ON itens_pedido(pedido_id);
CREATE INDEX idx_produtos_categoria ON produtos(categoria_id);
CREATE INDEX idx_produtos_slug ON produtos(slug);

-- Ver schema
\dt
\d clientes
\d pedidos

PostgreSQL oferece tipos ricos (JSONB, UUID, array, range), constraints poderosas (CHECK, UNIQUE, FK) e colunas generated. Identity é superior a SERIAL. Use JSONB ao invés de JSON para performance.