PostgreSQL: Criando e Alterando Tabelas no PostgreSQL

-- Criar tabela de usuários com unicidade de email e timestamp automático
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()
);

-- Criar tabela de produtos com verificação de preço > 0
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    attributes JSONB DEFAULT '{}'::jsonb
);

-- Criar tabela de pedidos com chaves estrangeiras e verificação de status
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    status VARCHAR(20) DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE [IF NOT EXISTS] nome_tabela (
    nome_coluna tipo_dado [restricao_coluna ...],
    ...
    [, restricao_tabela ...]
);

-- Chave primária composta
CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

-- Chave estrangeira nomeada com cascata
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    content TEXT,
    CONSTRAINT fk_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL
);

-- Chave primária de coluna única (mais comum)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Chave primária composta (para tabelas de junção)
CREATE TABLE product_tags (
    product_id INTEGER REFERENCES products(id),
    tag_id INTEGER REFERENCES tags(id),
    PRIMARY KEY (product_id, tag_id)
);

CREATE TABLE

-- Itens do pedido: excluir pedido -> excluir todos os seus itens (CASCADE)
-- Produto: excluir produto -> definir product_id como NULL (SET NULL)
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER REFERENCES products(id) ON DELETE SET NULL,
    quantity INTEGER NOT NULL DEFAULT 1
);

CREATE TABLE

-- Preço deve ser positivo, desconto não pode exceder o preço
CREATE TABLE promotions (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    discount_price DECIMAL(10, 2) CHECK (discount_price > 0),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    -- CHECK do PG pode referenciar outras colunas na mesma linha!
    CHECK (end_date > start_date)
    -- Nota: CHECK não pode referenciar uma subconsulta entre tabelas; validação entre tabelas precisa de um trigger
);

CREATE TABLE

-- Adicionar uma nova coluna
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Adicionar com valor padrão
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

-- Alterar tipo da coluna (requer USING para tipos incompatíveis)
ALTER TABLE users ALTER COLUMN phone TYPE TEXT;

-- Renomear coluna
ALTER TABLE users RENAME COLUMN phone TO phone_number;

-- Definir valor padrão
ALTER TABLE users ALTER COLUMN is_active SET DEFAULT true;

-- Adicionar restrição CHECK
ALTER TABLE products ADD CONSTRAINT price_reasonable
    CHECK (price < 100000);

-- Remover uma coluna
ALTER TABLE users DROP COLUMN IF EXISTS phone_number;

-- Renomear tabela
ALTER TABLE users RENAME TO customers;

-- instrução SQL executada com sucesso

-- Exclusão segura (sem erro se a tabela não existir)
DROP TABLE IF EXISTS test_table;

-- Exclusão em cascata (também remove objetos dependentes como views)
DROP TABLE IF EXISTS users CASCADE;

-- instrução SQL executada com sucesso

# Listar todas as tabelas no banco de dados atual
\dt

# Mostrar estrutura da tabela users
\d users

                                        Table "public.users"
   Column    |          Type          | Collation | Nullable |              Default
-------------+------------------------+-----------+----------+-----------------------------------
 id          | integer                |           | not null | nextval('users_id_seq'::regclass)
 email       | character varying(255) |           | not null |
 name        | character varying(100) |           | not null |
 password_hash | character(60)        |           | not null |
 created_at  | timestamp with time zone |         |          | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)

-- ============================================
-- Exemplo completo: tabelas centrais do e-commerce
-- Usuários, Produtos, Pedidos com restrições adequadas
-- ============================================

-- 1. Tabela de usuários
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    password_hash CHAR(60) NOT NULL,
    role VARCHAR(20) DEFAULT 'customer'
        CHECK (role IN ('customer', 'admin', 'manager')),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Tabela de produtos
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
    stock INTEGER DEFAULT 0 CHECK (stock >= 0),
    category VARCHAR(50),
    attributes JSONB DEFAULT '{}'::jsonb,
    is_available BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 3. Tabela de pedidos com chaves estrangeiras
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),
    shipping_address TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Tabela de itens do pedido (tabela de junção)
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10, 2) NOT NULL CHECK (unit_price > 0),
    subtotal DECIMAL(12, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

-- 5. Inserir dados de exemplo
INSERT INTO users (email, name, password_hash) VALUES
    ('alice@example.com', 'Alice', '$2a$12$dummyhashforalice1234567890abcdefghijklmnopqr'),
    ('bob@example.com', 'Bob', '$2a$12$dummyhashforbob1234567890abcdefghijklmnopqrstuv');

INSERT INTO products (name, price, stock, category, attributes) VALUES
    ('Running Shoes', 89.99, 150, 'Footwear', '{"color": "red", "size": 42}'::jsonb),
    ('Laptop Backpack', 49.99, 300, 'Accessories', '{"color": "black", "material": "nylon"}'::jsonb);

-- 6. Verificar estrutura da tabela
\d users
\d products
\d orders
\d order_items