Arbeiten mit ARRAYs

Funktionen zur Datenbearbeitung in PostgreSQL

Brian Piccolo

Sr. Director, Digital Strategy

Bevor es losgeht

Beispiel für CREATE TABLE

CREATE TABLE my_first_table (
    first_column text,
    second_column integer
);

Beispiel für INSERT

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

Der ARRAY-Typ

Wir erstellen eine einfache Tabelle mit zwei Array-Spalten.

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

INSERT bei Anweisungen mit ARRAYS

Beispiel für eine INSERT-Anweisung:

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

Zugriff auf ARRAYs

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          |
+--------+--------------------+-------------+

Beachte, dass Array-Indizes in PostgreSQL bei 1 beginnen und nicht bei 0.

ARRAYs durchsuchen

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          |
+--------+--------------------+-------------+

Funktionen und Operatoren mit 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          |
+---------+---------------------+-------------+

Funktionen und Operatoren mit 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          |
+---------+---------------------+-------------+

Lass uns üben!

Funktionen zur Datenbearbeitung in PostgreSQL