Travailler avec les tableaux dans PostgreSQL

Travailler avec les tableaux dans PostgreSQL

Maintenant qu’on connaît les bases, plongeons dans la création pratique de tableaux dans les requêtes SQL. C’est là que ça devient fun !

Utiliser le constructeur ARRAY[] dans SELECT

Le constructeur ARRAY[] est super pratique dans les requêtes SELECT quand tu veux créer explicitement un tableau. C’est comme dire à PostgreSQL : « Hey, voilà un tableau ! ».

-- Création d’un tableau de nombres
SELECT ARRAY[1, 2, 3, 4, 5] AS numbers;

-- Création d’un tableau de chaînes de caractères
SELECT ARRAY['Lundi', 'Mardi', 'Mercredi'] AS weekdays;

Avantages de ARRAY[] par rapport à la syntaxe {}

1. Cast de type explicite :

-- Avec ARRAY[] tu peux préciser le type
SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS dates;

-- Avec {} il faut faire gaffe
SELECT '{"2023-01-01", "2023-12-31"}'::DATE[] AS dates;

2. Meilleure lisibilité dans les requêtes complexes :

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

Exemple : création d’un tableau de nombres

On commence par un classique. Disons que tu veux créer un tableau de nombres :

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

Le résultat sera :

À noter : PostgreSQL retourne le tableau au format {} — c’est juste sa façon de montrer que c’est un tableau. Le style est un peu spécial, mais on s’y fait vite.

Exemple : création d’un tableau de chaînes

Si tu veux des chaînes au lieu de chiffres, ajoute juste des guillemets :

SELECT ARRAY['pomme', 'banane', 'orange'] AS fruits;

Résultat :

Au fait, PostgreSQL adore te simplifier la vie. Même si tu utilises du cyrillique ou n’importe quel autre alphabet, les tableaux marcheront nickel.

Exemple : tableaux avec d’autres types de données (genre des dates)

Et si on veut mettre un tableau de dates ? Trop facile :

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

Résultat :

Regarde le ::DATE. On a dit explicitement à PostgreSQL que c’est du type DATE. Sans ça, il aurait pris les chaînes telles quelles, ce qui n’est pas top pour des dates.

Agrégation de données en tableaux avec array_agg()

Passons à un truc plus costaud et intéressant. Si t’as déjà une table de données et que tu veux les regrouper dans un tableau ? C’est là que la fonction array_agg() entre en jeu.

Un des trucs les plus puissants — transformer plein de lignes en tableaux avec array_agg().

Utilisation de base :

-- Créons une table de test
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');

-- Grouper les étudiants par groupe
SELECT
group_id,
array_agg(student_name) AS students
FROM students
GROUP BY group_id
ORDER BY group_id;

Trier les éléments dans le tableau :

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

Filtrer pendant l’agrégation :

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

Exemples pratiques d’utilisation

Les tableaux sont utiles dans plein de cas du quotidien : stocker des tags, des droits d’accès ou collecter les actions d’un utilisateur sur la journée. Voici des exemples pour mieux piger comment et où utiliser les tableaux dans PostgreSQL.

Exemple 1 : Système de tags pour un blog

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

-- Insertion avec différentes syntaxes
INSERT INTO blog_posts (title, content, tags) VALUES
    ('Découvrir PostgreSQL', 'Contenu de l’article...',
     ARRAY['PostgreSQL', 'SQL', 'Base de données']),
    ('Développement web en 2024', 'Contenu de l’article...',
     '{"JavaScript", "React", "Node.js"}'),
    ('Machine learning', 'Contenu de l’article...',
     ARRAY['ML', 'Python', 'Data Science']);

-- Chercher des articles par tag
SELECT title FROM blog_posts
WHERE 'PostgreSQL' = ANY(tags);

Exemple 2 : Système de permissions utilisateur

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']);

-- Agréger toutes les permissions uniques du système
SELECT array_agg(DISTINCT permission) AS all_permissions
FROM users, unnest(permissions) AS permission;

Exemple 3 : Historique des actions utilisateur

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

INSERT INTO user_actions 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');

-- Grouper les actions utilisateur par jour
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. Requêtes avec des tableaux : sélection et filtrage

Quand on a des tableaux, il faut savoir les extraire et les analyser. Tu peux utiliser un SELECT classique pour récupérer un tableau :

SELECT tags FROM articles WHERE id = 1;

Ça retournera :

Mais si tu veux trouver un article qui a un tag précis, genre PostgreSQL ? On verra ça en détail dans la prochaine leçon, mais l’idée est simple : les tableaux te donnent de la flexibilité et te permettent de chercher des valeurs à l’intérieur.