Trabalhando com arrays no PostgreSQL

Trabalhando com arrays no PostgreSQL

Agora que a gente já sabe o básico, bora mergulhar na criação prática de arrays nas queries SQL. É aqui que a parada fica massa!

Usando o construtor ARRAY[] no SELECT

O construtor ARRAY[] é super útil nas queries SELECT, quando você quer criar um array na lata. É tipo falar pro PostgreSQL: “Ei, olha esse array aqui!”

-- Criando um array de números
SELECT ARRAY[1, 2, 3, 4, 5] AS numeros;

-- Criando um array de strings
SELECT ARRAY['Segunda-feira', 'Terça-feira', 'Quarta-feira'] AS dias_da_semana;

Vantagens do ARRAY[] sobre a sintaxe {}

1. Conversão de tipo explícita:

-- Com ARRAY[] dá pra definir o tipo na moral
SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS datas;

-- Com {} tem que tomar mais cuidado
SELECT '{"2023-01-01", "2023-12-31"}'::DATE[] AS datas;

2. Leitura mais fácil em queries complexas:

SELECT
product_name,
ARRAY[category, subcategory, brand] AS hierarquia_produto
FROM products;

Exemplo: criando um array de números

Vamos começar pelo clássico. Suponha que você precise criar um array de números:

SELECT ARRAY[1, 2, 3, 4, 5] AS meu_array;

O resultado vai ser assim:

Repara só: o PostgreSQL retorna o array no formato {} — é só o jeitão dele mostrar que é um array. O estilo é diferente, mas rapidinho você acostuma.

Exemplo: criando um array de strings

Se você quiser strings em vez de números, só colocar aspas:

SELECT ARRAY['maca', 'banana', 'laranja'] AS frutas;

Resultado:

Aliás, o PostgreSQL facilita muito a vida. Mesmo usando cirílico ou qualquer outro alfabeto, os arrays vão funcionar de boa.

Exemplo: arrays com outros tipos de dados (tipo datas)

E se a gente quiser um array de datas? Moleza:

SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS datas_importantes;

Resultado:

Repara no ::DATE. A gente deixou claro pro PostgreSQL que o tipo é DATE. Sem isso, ele ia tratar como string mesmo, o que não é o ideal pra datas.

Agregando dados em arrays com array_agg()

Agora bora pra parte mais avançada e divertida. E se já tiver uma tabela com dados e você quiser agrupar em arrays? Aí entra a função array_agg().

Uma das paradas mais poderosas — transformar várias linhas em arrays usando array_agg().

Uso básico:

-- Criando uma tabela de teste
CREATE TEMP TABLE estudantes (
group_id INTEGER,
student_name TEXT
);

INSERT INTO estudantes VALUES
(1, 'Anna'), (1, 'Otto'), (1, 'Maria'),
(2, 'Alex'), (2, 'Kira'),
(3, 'Elena');

-- Agrupando estudantes por grupo
SELECT
group_id,
array_agg(student_name) AS estudantes
FROM estudantes
GROUP BY group_id
ORDER BY group_id;

Ordenando elementos no array:

SELECT
group_id,
array_agg(student_name ORDER BY student_name) AS estudantes_ordenados
FROM estudantes
GROUP BY group_id;

Filtrando durante a agregação:

SELECT
group_id,
array_agg(student_name) FILTER (WHERE student_name LIKE 'A%') AS estudantes_a
FROM estudantes
GROUP BY group_id;

Exemplos práticos de uso

Arrays são úteis em vários cenários do dia a dia: guardar tags, permissões de acesso ou juntar as ações do usuário do dia. Olha só esses exemplos pra sacar melhor como e onde usar arrays no PostgreSQL.

Exemplo 1: Sistema de tags pra blog

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    tags TEXT[]
);

-- Inserindo com sintaxes diferentes
INSERT INTO blog_posts (title, content, tags) VALUES
    ('Aprendendo PostgreSQL', 'Conteúdo do artigo...',
     ARRAY['PostgreSQL', 'SQL', 'Banco de dados']),
    ('Desenvolvimento web em 2024', 'Conteúdo do artigo...',
     '{"JavaScript", "React", "Node.js"}'),
    ('Machine learning', 'Conteúdo do artigo...',
     ARRAY['ML', 'Python', 'Data Science']);

-- Buscando artigos por tag
SELECT title FROM blog_posts
WHERE 'PostgreSQL' = ANY(tags);

Exemplo 2: Sistema de permissões de usuários

CREATE TABLE usuarios (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL,
permissions TEXT[]
);

INSERT INTO usuarios (username, permissions) VALUES
('admin', ARRAY['read', 'write', 'delete', 'manage_users']),
('editor', ARRAY['read', 'write']),
('viewer', ARRAY['read']);

-- Agregando todas as permissões únicas do sistema
SELECT array_agg(DISTINCT permission) AS todas_permissoes
FROM usuarios, unnest(permissions) AS permission;

Exemplo 3: Histórico de ações do usuário

CREATE TABLE acoes_usuario (
user_id INTEGER,
action TEXT,
action_date DATE
);

INSERT INTO acoes_usuario VALUES
(1, 'login', '2024-01-01'),
(1, 'view_profile', '2024-01-01'),
(1, 'edit_settings', '2024-01-01'),
(2, 'login', '2024-01-01'),
(2, 'logout', '2024-01-01');

-- Agrupando ações do usuário por dia
SELECT
user_id,
action_date,
array_agg(action ORDER BY action) AS acoes_diarias
FROM acoes_usuario
GROUP BY user_id, action_date
ORDER BY user_id, action_date;

4. Queries com arrays: seleção e filtragem

Quando a gente tem arrays, precisa saber como pegar e analisar eles. Você pode usar o SELECT padrão pra pegar um array:

SELECT tags FROM artigos WHERE id = 1;

Isso vai retornar:

Mas e se você quiser achar um artigo que tenha uma tag específica, tipo PostgreSQL? Esse é um assunto que a gente vai ver com mais detalhes na próxima aula, mas a ideia é simples: arrays dão flexibilidade e permitem buscar valores dentro deles.