Trabajando con arrays en PostgreSQL

Trabajando con arrays en PostgreSQL

Ahora que ya sabemos lo básico, vamos a meternos de lleno en la creación práctica de arrays en consultas SQL. ¡Aquí empieza lo divertido!

Uso del constructor ARRAY[] en SELECT

El constructor ARRAY[] es súper útil en consultas SELECT cuando quieres crear un array explícitamente. Es como decirle a PostgreSQL: “¡Eh, aquí tienes un array!”

-- Crear un array de números
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;

-- Crear un array de strings
SELECT ARRAY['Lunes', 'Martes', 'Miércoles'] AS weekdays;

Ventajas de ARRAY[] sobre la sintaxis {}

1. Conversión de tipos explícita:

-- Con ARRAY[] puedes indicar el tipo explícitamente
SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS dates;

-- Con {} hay que tener más cuidado
SELECT '{"2023-01-01", "2023-12-31"}'::DATE[] AS dates;

2. Mejor legibilidad en consultas complejas:

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

Ejemplo: crear un array de números

Vamos con lo clásico. Supón que necesitas crear un array de números:

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

El resultado será:

Ojo: PostgreSQL devuelve el array en formato {} — es solo su forma de mostrar que es un array. Es un estilo peculiar, pero te acostumbras rápido.

Ejemplo: crear un array de strings

Si quieres strings en vez de números, solo pon comillas:

SELECT ARRAY['manzana', 'plátano', 'naranja'] AS fruits;

Resultado:

Por cierto, PostgreSQL te hace la vida fácil. Incluso si usas cirílico o cualquier otro alfabeto, los arrays siguen funcionando perfecto.

Ejemplo: arrays con otros tipos de datos (por ejemplo, fechas)

¿Y si queremos meter un array de fechas? Súper fácil:

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

Resultado:

Fíjate en el ::DATE. Le decimos explícitamente a PostgreSQL que es de tipo DATE. Sin eso, podría tomar los strings tal cual, y eso no sería lo ideal para fechas.

Agregando datos en arrays con array_agg()

Ahora vamos a una parte más chula e interesante. ¿Qué pasa si ya tienes una tabla con datos y necesitas agruparlos en un array? Aquí entra la función array_agg().

Una de las cosas más potentes es convertir varias filas en arrays usando array_agg().

Uso básico:

-- Creamos una tabla de prueba
CREATE TEMP TABLE students (
group_id INTEGER,
student_name TEXT
);

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

-- Agrupamos estudiantes por grupo
SELECT
group_id,
array_agg(student_name) AS students
FROM students
GROUP BY group_id
ORDER BY group_id;

Ordenando elementos en el array:

SELECT
group_id,
array_agg(student_name ORDER BY student_name) AS students_sorted
FROM students
GROUP BY group_id;

Filtrado al agregar:

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

Ejemplos prácticos de uso

Los arrays son útiles en un montón de situaciones del día a día: desde guardar etiquetas y permisos de acceso hasta recopilar acciones de usuario por día. Aquí tienes ejemplos para entender mejor cómo y dónde usar arrays en PostgreSQL.

Ejemplo 1: Sistema de etiquetas para un blog

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

-- Insertar usando diferentes sintaxis
INSERT INTO blog_posts (title, content, tags) VALUES
    ('Aprendiendo PostgreSQL', 'Contenido del artículo...',
     ARRAY['PostgreSQL', 'SQL', 'Base de datos']),
    ('Desarrollo web en 2024', 'Contenido del artículo...',
     '{"JavaScript", "React", "Node.js"}'),
    ('Aprendizaje automático', 'Contenido del artículo...',
     ARRAY['ML', 'Python', 'Data Science']);

-- Buscar artículos por etiqueta
SELECT title FROM blog_posts
WHERE 'PostgreSQL' = ANY(tags);

Ejemplo 2: Sistema de permisos de usuarios

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

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

-- Agregar todos los permisos únicos del sistema
SELECT array_agg(DISTINCT permission) AS all_permissions
FROM users, unnest(permissions) AS permission;

Ejemplo 3: Historial de acciones de usuario

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

INSERT INTO user_actions VALUES
(1, 'login', '2024-01-01'),
(1, 'ver_perfil', '2024-01-01'),
(1, 'editar_configuración', '2024-01-01'),
(2, 'login', '2024-01-01'),
(2, 'logout', '2024-01-01');

-- Agrupar acciones de usuario por día
SELECT
user_id,
action_date,
array_agg(action ORDER BY action) AS daily_actions
FROM user_actions
GROUP BY user_id, action_date
ORDER BY user_id, action_date;

4. Consultas con arrays: selección y filtrado

Cuando tienes arrays, necesitas saber cómo extraerlos y analizarlos. Puedes usar un SELECT normal para obtener un array:

SELECT tags FROM articles WHERE id = 1;

Esto devolverá:

Pero, ¿qué pasa si necesitas encontrar un artículo que tenga una etiqueta concreta, como PostgreSQL? Es un tema que veremos a fondo en la próxima lección, pero la idea es sencilla: los arrays te dan flexibilidad y te permiten buscar valores dentro de ellos.