Arbeiten mit Arrays in PostgreSQL

Arbeiten mit Arrays in PostgreSQL

Jetzt, wo wir die Basics kennen, lass uns tiefer in die praktische Erstellung von Arrays in SQL-Queries eintauchen. Hier wird’s richtig spannend!

Verwendung des Konstruktors ARRAY[] im SELECT

Der ARRAY[]-Konstruktor ist besonders praktisch in SELECT-Queries, wenn du explizit ein Array bauen willst. Das ist wie zu PostgreSQL zu sagen: „Hey, hier ist ein Array!“.

-- Erstellen eines Zahlen-Arrays
SELECT ARRAY[1, 2, 3, 4, 5] AS zahlen;

-- Erstellen eines String-Arrays
SELECT ARRAY['Montag', 'Dienstag', 'Mittwoch'] AS wochentage;

Vorteile von ARRAY[] gegenüber der {}-Syntax

1. Explizites Casting von Typen:

-- Mit ARRAY[] kannst du den Typ explizit angeben
SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS daten;

-- Mit {} musst du vorsichtiger sein
SELECT '{"2023-01-01", "2023-12-31"}'::DATE[] AS daten;

2. Bessere Lesbarkeit in komplexen Queries:

SELECT
product_name,
ARRAY[category, subcategory, brand] AS produkt_hierarchie
FROM products;

Beispiel: Erstellen eines Zahlen-Arrays

Starten wir klassisch. Angenommen, du willst ein Array aus Zahlen bauen:

SELECT ARRAY[1, 2, 3, 4, 5] AS mein_array;

Das Ergebnis sieht so aus:

Beachte: PostgreSQL gibt das Array im {}-Format zurück – das ist einfach seine Art zu zeigen, dass es ein Array ist. Der Stil ist speziell, aber man gewöhnt sich schnell dran.

Beispiel: Erstellen eines String-Arrays

Wenn du Strings statt Zahlen brauchst, einfach Anführungszeichen drumherum:

SELECT ARRAY['apfel', 'banane', 'orange'] AS früchte;

Ergebnis:

Übrigens, PostgreSQL macht dir das Leben echt leicht. Selbst wenn du Kyrillisch oder ein anderes Alphabet benutzt, funktionieren Arrays trotzdem einwandfrei.

Beispiel: Arrays mit anderen Datentypen (z.B. Datum)

Und was, wenn wir ein Array aus Daten machen wollen? Super easy:

SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS wichtige_daten;

Ergebnis:

Achte auf das ::DATE. Wir haben PostgreSQL explizit gesagt, dass das der Datentyp DATE ist. Ohne das würde er die Strings einfach so nehmen, was für Daten nicht ganz korrekt wäre.

Datenaggregation in Arrays mit array_agg()

Jetzt kommen wir zum spannenderen Teil. Was, wenn wir schon eine Tabelle mit Daten haben und die in Arrays gruppieren wollen? Hier hilft die Funktion array_agg().

Einer der mächtigsten Tricks: Mit array_agg() kannst du viele Zeilen in Arrays verwandeln.

Basic Usage:

-- Test-Tabelle erstellen
CREATE TEMP TABLE studenten (
group_id INTEGER,
studenten_name TEXT
);

INSERT INTO studenten VALUES
(1, 'Anna'), (1, 'Otto'), (1, 'Maria'),
(2, 'Alex'), (2, 'Kira'),
(3, 'Elena');

-- Studenten nach Gruppen gruppieren
SELECT
group_id,
array_agg(studenten_name) AS studenten
FROM studenten
GROUP BY group_id
ORDER BY group_id;

Sortierung der Elemente im Array:

SELECT
group_id,
array_agg(studenten_name ORDER BY studenten_name) AS studenten_sortiert
FROM studenten
GROUP BY group_id;

Filtern beim Aggregieren:

SELECT
group_id,
array_agg(studenten_name) FILTER (WHERE studenten_name LIKE 'A%') AS studenten_a
FROM studenten
GROUP BY group_id;

Praktische Anwendungsbeispiele

Arrays sind in vielen Alltagsszenarien praktisch: von Tag-Speicherung und Zugriffsrechten bis zum Sammeln von User-Aktionen pro Tag. Hier ein paar Beispiele, damit du besser verstehst, wie und wo du Arrays in PostgreSQL nutzen kannst.

Beispiel 1: Tag-System für einen Blog

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    titel TEXT NOT NULL,
    inhalt TEXT,
    tags TEXT[]
);

-- Einfügen mit verschiedenen Syntaxen
INSERT INTO blog_posts (titel, inhalt, tags) VALUES
    ('PostgreSQL lernen', 'Inhalt des Artikels...',
     ARRAY['PostgreSQL', 'SQL', 'Datenbank']),
    ('Webentwicklung in 2024', 'Inhalt des Artikels...',
     '{"JavaScript", "React", "Node.js"}'),
    ('Maschinelles Lernen', 'Inhalt des Artikels...',
     ARRAY['ML', 'Python', 'Data Science']);

-- Artikel nach Tags suchen
SELECT titel FROM blog_posts
WHERE 'PostgreSQL' = ANY(tags);

Beispiel 2: User-Berechtigungssystem

CREATE TABLE benutzer (
id SERIAL PRIMARY KEY,
benutzername TEXT NOT NULL,
berechtigungen TEXT[]
);

INSERT INTO benutzer (benutzername, berechtigungen) VALUES
('admin', ARRAY['lesen', 'schreiben', 'löschen', 'benutzer_verwalten']),
('editor', ARRAY['lesen', 'schreiben']),
('viewer', ARRAY['lesen']);

-- Alle einzigartigen Berechtigungen im System aggregieren
SELECT array_agg(DISTINCT berechtigung) AS alle_berechtigungen
FROM benutzer, unnest(berechtigungen) AS berechtigung;

Beispiel 3: User-Action-Historie

CREATE TABLE benutzer_aktionen (
benutzer_id INTEGER,
aktion TEXT,
aktions_datum DATE
);

INSERT INTO benutzer_aktionen VALUES
(1, 'login', '2024-01-01'),
(1, 'profil_ansehen', '2024-01-01'),
(1, 'einstellungen_bearbeiten', '2024-01-01'),
(2, 'login', '2024-01-01'),
(2, 'logout', '2024-01-01');

-- Aktionen des Benutzers pro Tag gruppieren
SELECT
benutzer_id,
aktions_datum,
array_agg(aktion ORDER BY aktion) AS tages_aktionen
FROM benutzer_aktionen
GROUP BY benutzer_id, aktions_datum
ORDER BY benutzer_id, aktions_datum;

4. Queries mit Arrays: Auswahl und Filterung

Wenn wir Arrays haben, müssen wir sie auch abfragen und analysieren können. Du kannst ein ganz normales SELECT nutzen, um ein Array zu bekommen:

SELECT tags FROM artikel WHERE id = 1;

Das gibt zurück:

Aber was, wenn wir einen Artikel finden wollen, der einen bestimmten Tag hat, zum Beispiel PostgreSQL? Das schauen wir uns in der nächsten Vorlesung genauer an, aber die Idee ist simpel: Arrays geben uns Flexibilität und erlauben es, Werte innerhalb von Arrays zu suchen.