PostgreSQL: Tipos de Dados do PostgreSQL: Guia Completo

-- Escolhas corretas de tipo para um banco de dados de e-commerce
CREATE TABLE smart_products (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,  -- ID preparado para o futuro
    name VARCHAR(200) NOT NULL,                          -- string com limite
    description TEXT,                                    -- texto sem limite
    price DECIMAL(10, 2) NOT NULL,                       -- dinheiro exato
    weight_kg REAL,                                      -- aproximado é aceitável
    is_available BOOLEAN DEFAULT true,                   -- flag sim/não
    created_at TIMESTAMPTZ DEFAULT NOW(),                -- com fuso horário
    source_ip INET,                                      -- tipo de endereço IP
    attributes JSONB DEFAULT '{}'::jsonb                 -- esquema flexível
);

-- SMALLINT para valores de intervalo pequeno
CREATE TABLE ratings (
    user_id INTEGER REFERENCES users(id),
    product_id INTEGER REFERENCES products(id),
    score SMALLINT CHECK (score BETWEEN 1 AND 5),  -- 1-5 está bem dentro do intervalo SMALLINT
    PRIMARY KEY (user_id, product_id)
);

-- INTEGER para a maioria dos IDs
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,  -- SERIAL = INTEGER + sequência autoincremental
    name VARCHAR(100) NOT NULL
);

-- BIGINT para tabelas de alto crescimento
CREATE TABLE audit_log (
    id BIGSERIAL PRIMARY KEY,  -- BIGSERIAL = BIGINT + autoincremento
    action VARCHAR(50),
    details JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE

-- Coluna de identidade padrão SQL (PostgreSQL 10+)
CREATE TABLE orders_v2 (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total DECIMAL(12, 2) DEFAULT 0
);

-- Inserir sem especificar id (gerado automaticamente)
INSERT INTO orders_v2 (user_id, total) VALUES (1, 99.99);

-- Tentar inserir id manualmente (irá FALHAR com GENERATED ALWAYS)
-- INSERT INTO orders_v2 (id, user_id, total) VALUES (100, 1, 50.00);

-- Use GENERATED BY DEFAULT se precisar de substituição manual às vezes
CREATE TABLE logs (
    id INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    message TEXT
);

INSERT 0 1

-- Armadilha de precisão float: 0.1 + 0.2 != 0.3
SELECT 0.1::REAL + 0.2::REAL = 0.3::REAL AS float_equal;
-- Resultado: f (falso!)

-- DECIMAL não tem perda de precisão
SELECT 0.1::DECIMAL + 0.2::DECIMAL = 0.3::DECIMAL AS decimal_equal;
-- Resultado: t (verdadeiro!)

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- DECIMAL(precision, scale)
-- precision = total de dígitos, scale = dígitos após o ponto decimal
-- DECIMAL(10,2) = até 99.999.999,99

CREATE TABLE products_precise (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    price DECIMAL(10, 2) NOT NULL,       -- máx. 99.999.999,99
    tax_rate DECIMAL(5, 4) DEFAULT 0.0875, -- 0,0875 = 8,75%
    discount NUMERIC(5, 2) DEFAULT 0.00   -- máx. 999,99%
);

-- Cálculos de dinheiro são exatos
SELECT name,
       price,
       price * tax_rate AS tax_amount,
       price + (price * tax_rate) AS price_with_tax
FROM products_precise;

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

-- CHAR: comprimento fixo (preenchido com espaços)
SELECT LENGTH('abc'::CHAR(5));
-- Resultado: 5 (preenchido para 5 caracteres)

-- VARCHAR: comprimento variável com limite
SELECT LENGTH('abc'::VARCHAR(5));
-- Resultado: 3 (armazenado como está, máx. 5)

-- TEXT: comprimento variável, sem limite
SELECT LENGTH('abc'::TEXT);
-- Resultado: 3 (sem limite)

-- Teste de desempenho: VARCHAR vs TEXT são idênticos no PG
-- (Diferente do MySQL onde VARCHAR é mais rápido que TEXT)

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Definir fuso horário para UTC
SET timezone = 'UTC';

-- Inserir o mesmo momento em tipos diferentes
INSERT INTO test_times (ts_no_tz, ts_with_tz) VALUES
    ('2026-07-13 10:00:00', '2026-07-13 10:00:00+00');

-- Alterar para fuso horário de Tóquio
SET timezone = 'Asia/Tokyo';

-- TIMESTAMP (sem fuso horário) mostra o mesmo literal
SELECT ts_no_tz FROM test_times;
-- Resultado: 2026-07-13 10:00:00 (inalterado, poderia ser qualquer fuso horário!)

-- TIMESTAMPTZ converte para o fuso horário local
SELECT ts_with_tz FROM test_times;
-- Resultado: 2026-07-13 19:00:00+09 (10:00 UTC = 19:00 Tóquio)

INSERT 0 1

-- Data e hora atuais
SELECT NOW();                    -- 2026-07-13 10:30:00.123456+00
SELECT CURRENT_DATE;             -- 2026-07-13
SELECT CURRENT_TIMESTAMP;        -- igual a NOW()

-- Aritmética de data com INTERVAL
SELECT NOW() + INTERVAL '7 days';     -- uma semana a partir de agora
SELECT NOW() - INTERVAL '3 months';   -- 3 meses atrás

-- Cálculo de idade
SELECT AGE(TIMESTAMP '1990-05-15');   -- 36 anos 1 mês 28 dias

-- Extrair partes
SELECT EXTRACT(YEAR FROM NOW());      -- 2026
SELECT EXTRACT(MONTH FROM NOW());     -- 7
SELECT EXTRACT(DOW FROM NOW());       -- 1 (Segunda-feira, 0=Domingo)

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Habilitar extensão uuid-ossp
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Gerar diferentes versões de UUID
SELECT uuid_generate_v4();  -- UUID aleatório (mais comum)
SELECT uuid_generate_v1();  -- UUID baseado em tempo

-- INET: IP único ou intervalo de IP
CREATE TABLE access_log (
    id BIGSERIAL PRIMARY KEY,
    source_ip INET NOT NULL,
    access_time TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO access_log (source_ip) VALUES
    ('192.168.1.100'),
    ('10.0.0.5'),
    ('2001:db8::1');

-- Consulta: encontrar todos os IPs em uma sub-rede (impossível com VARCHAR!)
SELECT source_ip FROM access_log
WHERE source_ip << '192.168.1.0/24'::INET;
-- << significa "está contido em"

-- CIDR: intervalo de rede
SELECT '192.168.1.0/24'::CIDR;

INSERT 0 1

-- Booleano aceita múltiplas representações
SELECT true, 't', 'true', 'yes', 'on', '1';   -- todos = TRUE
SELECT false, 'f', 'false', 'no', 'off', '0';  -- todos = FALSE

-- Booleano na cláusula WHERE
SELECT name FROM products WHERE is_available IS TRUE;
SELECT name FROM products WHERE is_available IS NOT FALSE;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Criar um tipo enum (uma vez, reutilizável entre tabelas)
CREATE TYPE order_status AS ENUM (
    'pending', 'paid', 'shipped', 'delivered', 'cancelled'
);

-- Usar na definição da tabela
CREATE TABLE orders_enum (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending'
);

-- Valores enum são validados automaticamente
INSERT INTO orders_enum (status) VALUES ('pending');   -- OK
-- INSERT INTO orders_enum (status) VALUES ('unknown');  -- ERRO!

INSERT 0 1

-- ============================================
-- Exemplo completo: tabela com todos os principais tipos
-- Um sistema de coleta de dados de sensores
-- ============================================

-- Criar tipo enum
CREATE TYPE sensor_status AS ENUM ('active', 'inactive', 'maintenance');

-- Criar tabela com diversos tipos de dados
CREATE TABLE sensors (
    -- Identidade
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    uuid UUID DEFAULT uuid_generate_v4() UNIQUE,

    -- Texto
    name VARCHAR(100) NOT NULL,
    description TEXT,
    location_code CHAR(10),

    -- Numérico
    latitude DECIMAL(9, 6) CHECK (latitude BETWEEN -90 AND 90),
    longitude DECIMAL(9, 6) CHECK (longitude BETWEEN -180 AND 180),
    altitude REAL,

    -- Rede
    ip_address INET,
    subnet CIDR,

    -- Status
    status sensor_status DEFAULT 'active',
    is_online BOOLEAN DEFAULT false,

    -- Tempo
    installed_at DATE,
    last_reading_at TIMESTAMPTZ,
    reading_interval INTERVAL DEFAULT INTERVAL '5 minutes',

    -- Dados flexíveis
    metadata JSONB DEFAULT '{}'::jsonb,

    -- Auditoria
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Inserir dados de exemplo
INSERT INTO sensors (name, latitude, longitude, ip_address, status, installed_at, metadata)
VALUES (
    'Sensor de Temperatura A1',
    35.6762, 139.6503,
    '192.168.1.50',
    'active',
    '2025-01-15',
    '{"model": "TX-200", "unit": "celsius", "range_min": -40, "range_max": 85}'::jsonb
);

-- Consulta com operadores específicos de tipo
SELECT name, ip_address, metadata->>'model' AS model
FROM sensors
WHERE ip_address << '192.168.1.0/24'::INET
  AND status = 'active';