Menu

Tableaux PostgreSQL : ANY, contient (@>) et unnest

Les colonnes de type tableau dans PostgreSQL : les déclarer, l'indexation à partir de 1, vérifier si un tableau contient une valeur avec ANY, @> et &&, unnest, et les fonctions de tableau utiles.

Cette page contient des éditeurs exécutables. Modifiez, exécutez et voyez la sortie instantanément.

N'importe quel type PostgreSQL peut être un tableau. Déclarez la colonne en text[] ou int[], écrivez les valeurs sous la forme '{a,b}' ou ARRAY['a', 'b'], et lisez les éléments avec tags[1] (les indices commencent à 1). Pour vérifier si un tableau contient une valeur, utilisez 'value' = ANY(tags) ou tags @> ARRAY['value'].

Les articles 1 et 2 ont le tag sql. tags[1] est le premier élément et cardinality compte les éléments. Les deux formes littérales stockent la même valeur ; ARRAY[...] est plus pratique quand les éléments viennent d'expressions ou contiennent des virgules.

Vérifier si un tableau contient une valeur

Quatre opérateurs couvrent l'appartenance :

TestSignificationVrai pour '{sql,json}'
'sql' = ANY(tags)une valeur est dans le tableauoui
tags @> '{sql,json}'le tableau contient toutes ces valeursoui
tags <@ '{sql,json,go}'chaque élément est dans cette listeoui
tags && '{go,json}'les tableaux ont au moins un élément en communoui

Seul l'article 1 a les deux tags. Les articles 2 et 3 ont un élément en commun avec {go,json}. L'article 3 est le seul où no_go est faux. ALL est le pendant de ANY : la comparaison doit être vraie pour chaque élément.

= ANY suit les règles de NULL du SQL. Si la valeur n'est pas trouvée et que le tableau contient un NULL, le résultat est NULL, pas faux. Donc WHERE NOT ('x' = ANY(tags)) peut écarter des lignes que vous attendiez. @> et && renvoient vrai ou faux.

Un index GIN rend @>, <@, && et = rapides sur les grosses tables. Il n'aide pas = ANY(column), donc pour une recherche indexée écrivez tags @> ARRAY['sql'] :

CREATE INDEX posts_tags_idx ON posts USING gin (tags);

Transformer un tableau en lignes avec unnest

unnest renvoie une ligne par élément, ce qui permet de compter, joindre ou filtrer sur chaque élément :

sql apparaît dans deux articles et les autres tags dans un seul chacun. Ajoutez WITH ORDINALITY (unnest(tags) WITH ORDINALITY AS t(tag, pos)) pour obtenir aussi la position de chaque élément. Pour regrouper des lignes en tableau, utilisez array_agg (voir string_agg et array_agg).

Ajouter, retirer et modifier des éléments

Cette requête renvoie {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, 2, {20,30} et a, b, c. Dans un UPDATE, les mêmes fonctions modifient un tableau stocké : SET tags = array_append(tags, 'new') ou SET tags = array_remove(tags, 'old'). array_remove supprime toutes les occurrences de la valeur. string_to_array('a,b,c', ',') découpe un texte en tableau.

Obtenir la longueur d'un tableau

cardinality(tags) renvoie le nombre d'éléments, et 0 pour un tableau vide. array_length(tags, 1) renvoie la longueur de la dimension 1, et NULL pour un tableau vide. Donc WHERE array_length(tags, 1) = 0 ne correspond jamais. Testez un tableau vide avec cardinality(tags) = 0 ou tags = '{}'.

Les tableaux PostgreSQL peuvent avoir plusieurs dimensions (int[][]), mais chaque sous-tableau doit avoir la même longueur, et la taille déclarée (int[3]) n'est pas vérifiée. Pour des données imbriquées ou irrégulières, JSONB convient en général mieux.

Questions fréquentes

Les tableaux PostgreSQL commencent-ils à 0 ou à 1 ?

À 1 par défaut : tags[1] est le premier élément. Un indice au-delà de la fin renvoie NULL au lieu de lever une erreur.

Quelle différence entre IN et = ANY dans PostgreSQL ?

x IN (1, 2, 3) prend une liste écrite dans la requête ; x = ANY(array) prend une seule valeur de type tableau. ANY convient au code applicatif, car vous pouvez passer un seul paramètre tableau (WHERE id = ANY($1)) au lieu de construire une liste de placeholders.

Faut-il utiliser une colonne tableau ou une table séparée ?

Un tableau convient à une courte liste que vous lisez et écrivez avec sa ligne, comme des tags. Quand les éléments ont besoin de leurs propres colonnes, de clés étrangères, ou sont modifiés un par un, une table séparée avec une ligne par élément est plus facile à interroger et à contraindre.

Comment savoir si un tableau est vide dans PostgreSQL ?

Utilisez cardinality(arr) = 0 ou arr = '{}'. array_length(arr, 1) = 0 ne correspond jamais, car array_length renvoie NULL pour un tableau vide.

Illustration des langages de programmation de Coddy

Apprendre à coder avec Coddy

Commencer