Utiliser des tableaux dans PostgreSQL : un guide

Lorsque vous pensez aux images SQL de tables, les requêtes SELECT et le concept d'une ligne par point de données sans structure imbriquée vous viennent probablement à l'esprit.

Mais que se passe-t-il si vous souhaitez stocker une liste de numéros, de produits et bien plus encore dans une rangée ?

Vous pouvez étendre la liste sur plusieurs lignes, mais cela peut conduire à une jungle de lignes dont il peut être difficile de suivre. Heureusement, PostgreSQL propose le type de données tableau qui peut être utilisé pour stocker et manipuler des listes.

Avec ARRAY, PostgreSQL permet de définir les colonnes d'une table sous forme de tableaux multidimensionnels de longueur variable. Des tableaux de n'importe quel type de base intégré ou défini par l'utilisateur, type d'énumération, type composite, type de plage ou domaine peuvent être créés.

Les tableaux peuvent prendre plusieurs formes et il existe plusieurs façons de les déclarer.

Vous pouvez utiliser le mot-clé ARRAY après la déclaration du type de données pour une colonne pour indiquer que vous souhaitez créer un tableau du type de données déclaré précédemment, comme indiqué ci-dessous.

Vous pouvez également suivre la déclaration du type de données avec des crochets, comme text[], mais je trouve cela moins explicite. Et il n'est pas conforme à la norme SQL, contrairement à la version du mot clé.

Dans l'exemple suivant, je crée un petit tableau pour simuler une carte d'achat et la remplir avec quelques permutations de plusieurs produits. Si vous exécutez la requête complète, vous pouvez également voir comment les tableaux sont représentés en sortie.

Une autre façon d'ajouter un tableau à une table consiste à utiliser la syntaxe {} au lieu du mot-clé ARRAY entre crochets. Les deux manières fonctionnent. Il suffit de prêter une attention particulière aux citations entre accolades. Encore une fois, je trouve la syntaxe des mots clés plus explicite et les crochets plus proches des représentations matricielles dans d'autres langages de programmation, mais les deux fonctionnent bien.

En savoir plus sur la science des données10 concepts SQL avancés à connaître pour les entretiens en science des données

Que se passe-t-il si vous avez réellement besoin d'exploser ou de désimbriquer le tableau pour des lignes spécifiques afin de rejoindre un tableau d'informations sur un produit ou d'effectuer d'autres opérations sur la base d'un élément de liste ?

Aucun problème. Les tableaux dans PostgreSQL peuvent être facilement désimbriqués avec le mot-clé UNNEST :

Comme prévu, UNNEST crée une ligne par élément dans le tableau respectif dans la ligne d'origine :

Vous vous demandez peut-être si vous devez désemboîter un tableau chaque fois que vous souhaitez accéder à un seul élément pour des opérations ultérieures. Si tel est le cas, une structure de tableau n’est peut-être pas une bonne idée après tout. Heureusement, les tableaux de PostgreSQL permettent l'accès via l'indexation avec des crochets et des tranches, comme indiqué ci-dessous. Cela permet une sélection facile, par exemple du premier élément de chaque liste ou d'une plage spécifique d'éléments dans une liste.

L'indexation dans les tableaux PostgreSQL commence à un et non à zéro, ce qui peut différer de ce à quoi vous êtes habitué dans d'autres langages de programmation.

Les tranches fonctionnent avec la syntaxe [start:end]. Accès aux éléments du tableau via le découpage pour toutes les lignes contenant plus de deux éléments du tableau.

Si vous n'êtes pas sûr du nombre d'éléments que contient votre tableau, ou si vous souhaitez filtrer selon la taille d'un tableau, comme je l'ai fait ci-dessus, vous pouvez utiliser le mot-clé CARDINALITY. Cela renverra le nombre d'éléments dans le tableau sous forme d'entier.

Que se passe-t-il si vous souhaitez sélectionner tous les paniers contenant un produit spécifique dans la gamme ? Ou toute autre opération de filtrage basée sur un élément du tableau ? Pas de problème, PostgreSQL vous permet d'utiliser la valeur de l'élément dans la clause WHERE en conjonction avec le mot-clé ANY.

Ci-dessous, je filtre les lignes qui contiennent « product_c » dans la colonne du tableau de produits. Cette méthode de filtrage est particulièrement utile pour les valeurs de filtre unique.

Vous pouvez également étendre la logique de filtrage par le haut et demander n'importe quelle carte d'achat contenant un sous-tableau spécifique. Ici, j'utilise l'opérateur '@>', qui signifie 'contient'. On peut le lire ainsi :

"Le tableau de produits contient le tableau ['product_a', 'product_b']."

Les tableaux et les valeurs de tableau peuvent être mis à jour de la même manière que les autres types de données, avec une clause UPDATE… SET…, comme indiqué ci-dessous. Vous pouvez mettre à jour un seul élément du tableau via l'indexation ou l'ensemble du tableau.