Utilisation des tableaux

Fonctions permettant de manipuler les données dans PostgreSQL

Brian Piccolo

Sr. Director, Digital Strategy

Avant de commencer

CREATE TABLE exemple

CREATE TABLE my_first_table (
    first_column text,
    second_column integer
);

INSERT exemple

INSERT INTO my_first_table 
    (first_column, second_column) VALUES ('text value', 12);

ARRAY un type particulier

Créons un tableau simple avec deux colonnes.

CREATE TABLE grades (
    student_id int,
    email text[][],
    test_scores int[]
);

Instructions INSERT avec des tableaux

Exemple d’instruction INSERT :

INSERT INTO grades 
    VALUES (1, 
    '{{"work","[email protected]"},{"other","[email protected]"}}', 
    '{92,85,96,88}' );

Accès aux tableaux

SELECT
   email[1][1] AS type,
   email[1][2] AS address,
   test_scores[1],
FROM grades;

+--------+--------------------+-------------+
| type   |  address           | test_scores |
|--------|--------------------|-------------|
| work   | [email protected] | 92          |
| work   | [email protected] | 76          |
+--------+--------------------+-------------+

Veuillez noter que les index des tableaux PostgreSQL commencent à un et non à zéro.

Recherche dans les tableaux

SELECT
   email[1][1] as type,
   email[1][2] as address,
   test_scores[1]
FROM grades
WHERE email[1][1] = 'work';

+--------+--------------------+-------------+
| type   |  address           | test_scores |
|--------|--------------------|-------------|
| work   | [email protected] | 92          |
| work   | [email protected] | 76          |
+--------+--------------------+-------------+

Fonctions et opérateurs ARRAY

SELECT
   email[2][1] as type,
   email[2][2] as address,
   test_scores[1]
FROM grades
WHERE 'other' = ANY (email);


+---------+---------------------+-------------+
| type    |  address            | test_scores |
|---------|-----------------------------------|
| other   | [email protected] | 92          |
| null    | null                | 76          |
+---------+---------------------+-------------+

Fonctions et opérateurs ARRAY

SELECT
   email[2][1] as type,
   email[2][2] as address,
   test_scores[1]
FROM grades
WHERE email @> ARRAY['other'];

+---------+---------------------+-------------+
| type    |  address            | test_scores |
|---------|-----------------------------------|
| other   | [email protected] | 92          |
| null    | null                | 76          |
+---------+---------------------+-------------+

Passons à la pratique !

Fonctions permettant de manipuler les données dans PostgreSQL