Erreurs typiques avec les tableaux et comment les éviter

Aujourd’hui, on termine notre petit voyage fun autour des tableaux dans PostgreSQL. Au menu — les erreurs classiques que tu risques de croiser, et surtout, comment les esquiver. Si tu t’es déjà dit “ce tableau fait encore n’importe quoi”, cette leçon est pour toi. On y va !

Erreurs à la création des tableaux : soucis de types de données dans le tableau

Quand tu crées des tableaux dans PostgreSQL, faut pas oublier que tous les éléments du tableau doivent être du même type. Par exemple :

SELECT ARRAY[1, 2, 'trois'];
-- Erreur : tous les éléments du tableau doivent être du même type

PostgreSQL ne te laissera pas mélanger des nombres et des chaînes dans le même tableau. Si jamais t’as besoin de ça, pense à caster :

SELECT ARRAY[1::TEXT, 2::TEXT, 'trois'];
-- Maintenant le tableau est composé de chaînes

Erreurs avec ARRAY[] et différents types de données

Par défaut, PostgreSQL essaie de deviner le type du tableau selon ce qu’il y a dedans. Si tu lui files des données ambiguës, prépare-toi à une erreur :

SELECT ARRAY[1, NULL];
-- Erreur : PostgreSQL ne sait pas comment interpréter NULL

Pour régler ça, indique clairement le type de données :

SELECT ARRAY[1, NULL]::INTEGER[];
-- Là, tout roule

Erreurs à l’extraction des données : soucis d’indexation des tableaux

Si t’as l’habitude de coder en Python ou JavaScript, où les index commencent à zéro, tu vas être surpris avec PostgreSQL. Ici, les tableaux sont indexés à partir de 1.

SELECT ARRAY[10, 20, 30][0];
-- Erreur : l’index doit commencer à 1

La bonne requête :

SELECT ARRAY[10, 20, 30][1];
-- Résultat : 10

Erreurs avec les fonctions d’extraction

Des fonctions comme unnest() peuvent te perdre si tu oublies qu’elles “déplient” le tableau sur toutes les lignes :

CREATE TEMP TABLE exemple (
    id SERIAL PRIMARY KEY,
    tags TEXT[]
);

INSERT INTO exemple (tags) VALUES (ARRAY['tag1', 'tag2']), (ARRAY['tag3']);

SELECT unnest(tags) FROM exemple;
-- Résultat :
-- tag1
-- tag2
-- tag3

Si tu veux garder le contexte de la ligne (genre id), ajoute-le explicitement :

SELECT id, unnest(tags) AS tag FROM exemple;
-- Résultat :
-- id | tag
--  1 | tag1
--  1 | tag2
--  2 | tag3

Erreurs lors du filtrage et de la comparaison des tableaux

1. Mauvaise utilisation des opérateurs @>, <@, &&

Ces opérateurs servent à des trucs précis :

• @> vérifie si un tableau contient un autre tableau.
• <@ vérifie si un tableau est contenu dans un autre.
• && vérifie si deux tableaux ont des éléments en commun.

Erreur classique : les utiliser à l’arrache :

SELECT ARRAY[1, 2, 3] @> 2;
-- Erreur : l’opérateur @> est fait pour les tableaux

La bonne façon :

SELECT ARRAY[1, 2, 3] @> ARRAY[2];
-- Résultat : true

2. Problèmes de perf sans index

Si tu utilises beaucoup les opérateurs sur les tableaux et que tes requêtes rament, t’as sûrement zappé l’indexation. Voilà comment indexer un tableau :

CREATE INDEX idx_tags ON exemple USING GIN (tags);

Maintenant, les requêtes avec @> et && vont beaucoup plus vite.

Erreurs à la modification des tableaux

1. Suppression et ajout de valeurs

Les fonctions array_remove() et array_append() ne modifient pas le tableau “en place”, elles renvoient un nouveau tableau. Si tu t’attends à ce que le tableau d’origine change, c’est une erreur :

UPDATE exemple
SET tags = array_remove(tags, 'tag1');
-- Maintenant le tableau est mis à jour

Si t’oublies de mettre SET tags =, SQL va exécuter la requête mais le tableau ne changera pas.

2. Doublons avec array_append()

La fonction array_append() ne vérifie pas si l’élément existe déjà. Du coup, tu peux te retrouver avec des doublons :

SELECT array_append(ARRAY['tag1', 'tag2'], 'tag1');
-- Résultat : {tag1, tag2, tag1}

Pour éviter les doublons, filtre avant :

SELECT array_remove(array_append(ARRAY['tag1', 'tag2'], 'tag1'), 'tag1') || 'tag1';
-- Résultat : {tag1, tag2}

Conseils pour éviter les erreurs

Pour esquiver les erreurs vues plus haut avec les tableaux :

1. Vérifie les types de données. Indique toujours le type du tableau si c’est ambigu.
2. Sois attentif avec les index. Rappelle-toi que les tableaux dans PostgreSQL commencent à 1.
3. Optimise tes requêtes avec des tableaux. Utilise l’indexation pour accélérer les comparaisons et les filtres.
4. Teste sur des petits jeux de données. Si ta requête rame, essaye-la sur un échantillon pour trouver le souci.
5. Évite les doublons. Ajoute des checks quand tu ajoutes des éléments si tu veux pas de répétitions.

Les tableaux dans PostgreSQL, c’est comme une arme puissante : faut les manier avec respect et précaution. Avec ces conseils, tes tableaux ne te feront plus jamais passer de nuits blanches (enfin, presque). Bonne chance pour le design et l’optimisation de tes bases de données !