postgres

docker compose exec db psql -U postgres -d tanamao

psql "postgresql://postgres:postgres@localhost:5432/tanamao"

-- Total por tabela
SELECT 'municipios' as tabela, COUNT(*) FROM municipios
UNION ALL
SELECT 'beneficiarios', COUNT(*) FROM beneficiarios
UNION ALL
SELECT 'programas', COUNT(*) FROM programas;

SELECT
    p.nome as programa,
    COUNT(b.id) as total_beneficiarios,
    SUM(b.valor) as valor_total
FROM beneficiarios b
JOIN programas p ON b.programa_id = p.id
GROUP BY p.nome
ORDER BY total_beneficiarios DESC;

SELECT
    CONCAT('***', RIGHT(cpf, 4)) as cpf_masked,
    programa_id,
    valor,
    data_referencia
FROM beneficiarios
WHERE cpf = '12345678901'
LIMIT 10;

SELECT
    m.nome as municipio,
    m.uf,
    COUNT(b.id) as total
FROM municipios m
LEFT JOIN beneficiarios b ON b.municipio_id = m.id
GROUP BY m.id, m.nome, m.uf
ORDER BY total DESC
LIMIT 20;

SELECT
    m.uf,
    COUNT(DISTINCT m.id) as municipios,
    COUNT(b.id) as beneficiarios,
    SUM(b.valor) as valor_total
FROM municipios m
LEFT JOIN beneficiarios b ON b.municipio_id = m.id
GROUP BY m.uf
ORDER BY m.uf;

\dt                     -- Listar tabelas
\d beneficiarios        -- Estrutura da tabela
\d+ beneficiarios       -- Estrutura detalhada

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'beneficiarios';

SELECT
    relname as tabela,
    pg_size_pretty(pg_total_relation_size(relid)) as tamanho
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

from sqlalchemy import text

async def consultar_beneficiario(session, cpf: str):
    query = text("""
        SELECT programa_id, valor, data_referencia
        FROM beneficiarios
        WHERE cpf = :cpf
        LIMIT 10
    """)
    result = await session.execute(query, {"cpf": cpf})
    return result.fetchall()