PostgreSQL Erweiterte Datentypen

Einführung

In diesem Lab werden Sie die erweiterten Datentypen von PostgreSQL erkunden, mit Schwerpunkt auf JSON/JSONB, Arrays und UUIDs. Sie lernen, wie Sie Daten innerhalb dieser Typen speichern, abfragen und manipulieren.

Das Lab beginnt mit der Demonstration, wie JSON- und JSONB-Daten gespeichert und abgefragt werden, einschließlich der Erstellung einer Tabelle mit einer JSONB-Spalte, dem Einfügen von JSON-Daten und der Verwendung von Operatoren wie -> und ->> zum Extrahieren spezifischer Werte. Anschließend lernen Sie Array-Spalten und UUIDs kennen.

JSON- und JSONB-Daten speichern und abfragen

In diesem Schritt lernen Sie, wie Sie JSON- und JSONB-Daten in PostgreSQL speichern und abfragen. PostgreSQL bietet zwei Datentypen für die Speicherung von JSON-Daten: JSON und JSONB. Der Datentyp JSON speichert eine exakte Kopie des eingegebenen JSON-Textes, während der Datentyp JSONB JSON-Daten in einem zerlegten Binärformat speichert. JSONB wird im Allgemeinen bevorzugt, da es eine bessere Leistung für Abfragen und Indizierung bietet.

Beginnen wir mit dem Öffnen der PostgreSQL-Shell. Verbinden Sie sich zuerst mit der labex-Datenbank:

sudo -u postgres psql -d labex

Sie sollten die PostgreSQL-Eingabeaufforderung sehen:

labex=#

Nun erstellen wir eine Tabelle zur Speicherung von JSONB-Daten:

CREATE TABLE products (id SERIAL PRIMARY KEY, data JSONB);

Dieser SQL-Befehl erstellt eine Tabelle namens products. Die Tabelle hat zwei Spalten: id (ein automatisch inkrementierender Integer-Primärschlüssel) und data (eine JSONB-Spalte zur Speicherung von JSON-Daten).

Sie sollten eine Ausgabe ähnlich dieser sehen:

CREATE TABLE

Nun fügen wir einige Daten in die Tabelle products ein:

INSERT INTO products (data) VALUES ('{"name": "Laptop", "price": 1200, "features": ["16GB RAM", "512GB SSD"]}');
INSERT INTO products (data) VALUES ('{"name": "Keyboard", "price": 75, "features": ["Mechanical", "RGB Backlight"]}');

Diese Befehle fügen zwei Zeilen in die Tabelle products ein. Jede Zeile enthält ein JSON-Objekt mit Informationen über ein Produkt.

Für jede Einfügung sollten Sie eine Ausgabe ähnlich dieser sehen:

INSERT 0 1

Um die JSON-Daten abzufragen, können Sie die Operatoren -> und ->> verwenden. Der Operator -> gibt ein JSON-Objekt zurück, während der Operator ->> einen JSON-Wert als Text zurückgibt.

Um beispielsweise den Namen des ersten Produkts abzurufen, können Sie die folgende Abfrage verwenden:

SELECT data ->> 'name' FROM products WHERE id = 1;

Dieser Befehl wählt den Wert aus, der dem Schlüssel name in der Spalte data der Tabelle products zugeordnet ist, wobei die id 1 ist. Der Operator ->> stellt sicher, dass das Ergebnis als Text zurückgegeben wird.

Sie sollten eine Ausgabe ähnlich dieser sehen:

  ?column?
----------
 Laptop
(1 row)

Sie können auch verschachtelte JSON-Objekte abfragen. Um beispielsweise das erste Element des features-Arrays des ersten Produkts abzurufen, können Sie die folgende Abfrage verwenden:

SELECT data -> 'features' ->> 0 FROM products WHERE id = 1;

Dieser Befehl wählt zuerst das features-Array aus der Spalte data aus und dann das Element am Index 0 aus dem Array. Der Operator ->> stellt sicher, dass das Ergebnis als Text zurückgegeben wird.

Sie sollten eine Ausgabe ähnlich dieser sehen:

  ?column?
----------
 16GB RAM
(1 row)

Sie können auch den Operator @> verwenden, um zu überprüfen, ob ein JSON-Objekt ein bestimmtes Schlüssel-Wert-Paar enthält. Um beispielsweise alle Produkte mit einem Preis von 75 zu finden, können Sie die folgende Abfrage verwenden:

SELECT data ->> 'name' FROM products WHERE data @> '{"price": 75}';

Dieser Befehl wählt den Namen aller Zeilen aus der Tabelle products aus, bei denen die Spalte data ein JSON-Objekt mit dem Schlüssel price und dem Wert 75 enthält.

Sie sollten eine Ausgabe ähnlich dieser sehen:

  ?column?
----------
 Keyboard
(1 row)

Großartig! Wir haben erfolgreich unsere erste Tabelle mit JSONB-Daten erstellt und gelernt, wie man sie abfragt. Wir werden in diesem Schritt mit dieser Tabelle weiterarbeiten, um fortgeschrittenere Funktionen hinzuzufügen.

Array-Spalten zu bestehender Tabelle hinzufügen

In diesem Schritt lernen Sie, wie Sie Array-Spalten in PostgreSQL hinzufügen und manipulieren. Wir erweitern unsere bestehende products-Tabelle um eine Array-Spalte zur Speicherung von Tags. Array-Spalten ermöglichen es Ihnen, mehrere Werte desselben Datentyps in einer einzigen Spalte zu speichern, was nützlich ist, um Listen von Elementen wie Tags, Kategorien oder Funktionen zu speichern.

Da wir bereits mit der Datenbank verbunden sind und unsere products-Tabelle aus dem vorherigen Schritt haben, fügen wir unserer bestehenden Tabelle Array-Spalten hinzu:

ALTER TABLE products ADD COLUMN name VARCHAR(255);
ALTER TABLE products ADD COLUMN tags TEXT[];

Diese Befehle fügen zwei neue Spalten zu unserer bestehenden products-Tabelle hinzu: name (ein String) und tags (ein Array von Strings). Der Datentyp TEXT[] gibt an, dass die Spalte tags ein Array von Textwerten ist.

Sie sollten für jeden ALTER-Befehl eine Ausgabe ähnlich dieser sehen:

ALTER TABLE

Nun aktualisieren wir unsere bestehenden Daten und fügen neue Daten mit den neuen Spalten ein:

UPDATE products SET name = data ->> 'name' WHERE id = 1;
UPDATE products SET name = data ->> 'name' WHERE id = 2;
UPDATE products SET tags = ARRAY['electronics', 'computers', 'portable'] WHERE id = 1;
UPDATE products SET tags = ARRAY['electronics', 'accessories', 'input'] WHERE id = 2;

Diese Befehle aktualisieren unsere bestehenden Produkte mit Namens- und Tag-Informationen, die aus den JSONB-Daten extrahiert wurden, sowie mit neuen Array-Werten.

Sie sollten für jede Aktualisierung eine Ausgabe ähnlich dieser sehen:

UPDATE 1

Um die Array-Daten abzufragen, können Sie die Array-Indizierung verwenden. Array-Indizes beginnen in PostgreSQL bei 1.

Um beispielsweise den ersten Tag des ersten Produkts abzurufen, können Sie die folgende Abfrage verwenden:

SELECT tags[1] FROM products WHERE id = 1;

Dieser Befehl wählt das Element am Index 1 aus dem tags-Array der products-Tabelle aus, wobei die id 1 ist.

Sie sollten eine Ausgabe ähnlich dieser sehen:

   tags
-----------
 electronics
(1 row)

Sie können auch die Funktion UNNEST verwenden, um ein Array in eine Menge von Zeilen zu erweitern.

Um beispielsweise alle Tags von allen Produkten abzurufen, können Sie die folgende Abfrage verwenden:

SELECT name, UNNEST(tags) AS tag FROM products;

Dieser Befehl wählt den name und jeden einzelnen tag aus dem tags-Array aus und erstellt für jeden Tag eine neue Zeile.

Sie sollten eine Ausgabe ähnlich dieser sehen:

  name   |     tag
---------+-------------
 Laptop  | electronics
 Laptop  | computers
 Laptop  | portable
 Keyboard| electronics
 Keyboard| accessories
 Keyboard| input
(6 rows)

Sie können den Operator @> verwenden, um zu überprüfen, ob ein Array einen bestimmten Wert enthält.

Um beispielsweise alle Produkte mit dem Tag 'electronics' zu finden, können Sie die folgende Abfrage verwenden:

SELECT name FROM products WHERE tags @> ARRAY['electronics'];

Dieser Befehl wählt den Namen aller Zeilen aus der products-Tabelle aus, bei denen das tags-Array den Wert 'electronics' enthält.

Sie sollten eine Ausgabe ähnlich dieser sehen:

  name
----------
 Laptop
 Keyboard
(2 rows)

Sie können auch den Operator && verwenden, um zu überprüfen, ob zwei Arrays gemeinsame Elemente haben.

Um beispielsweise alle Produkte zu finden, die Tags mit dem ersten Produkt gemeinsam haben, können Sie die folgende Abfrage verwenden:

SELECT p2.name FROM products p1, products p2 WHERE p1.id = 1 AND p1.tags && p2.tags AND p2.id != 1;

Dieser Befehl wählt den Namen aller Zeilen aus der products-Tabelle (alias p2) aus, die mindestens einen Tag mit den Tags des ersten Produkts (alias p1) gemeinsam haben, wobei das erste Produkt selbst ausgeschlossen wird.

Sie sollten eine Ausgabe ähnlich dieser sehen:

  name
----------
 Keyboard
(1 row)

Perfekt! Wir haben erfolgreich Array-Spalten zu unserer bestehenden Tabelle hinzugefügt und gelernt, wie man mit Arrays arbeitet. Unsere products-Tabelle enthält nun sowohl JSONB- als auch Array-Datentypen und ist somit für den nächsten Schritt bereit.

UUID-Spalte hinzufügen und UUID-Generierung lernen

In diesem Schritt lernen Sie, wie Sie UUIDs (Universally Unique Identifiers) in PostgreSQL generieren und verwenden. UUIDs sind 128-Bit-Zahlen, die so konzipiert sind, dass sie über Raum und Zeit eindeutig sind. Sie werden oft als eindeutige Identifikatoren in Datenbanktabellen verwendet, um Konflikte beim Zusammenführen von Daten aus verschiedenen Quellen zu vermeiden.

Da wir unsere bestehende Sitzung und Tabelle fortsetzen, fügen wir unserer aktuellen products-Tabelle eine UUID-Spalte hinzu, um die UUID-Funktionalität zu demonstrieren.

Zuerst aktivieren wir die UUID-Erweiterung, die Funktionen zur UUID-Generierung bereitstellt:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Dieser Befehl erstellt die Erweiterung uuid-ossp, falls sie noch nicht existiert.

Sie sollten eine Ausgabe ähnlich dieser sehen:

CREATE EXTENSION

Nun fügen wir unserer bestehenden products-Tabelle eine UUID-Spalte hinzu:

ALTER TABLE products ADD COLUMN uuid_id UUID;

Dieser Befehl fügt unserer bestehenden products-Tabelle eine neue UUID-Spalte hinzu.

Sie sollten eine Ausgabe ähnlich dieser sehen:

ALTER TABLE

Jetzt können wir unsere bestehenden Zeilen mit UUID-Werten unter Verwendung der Funktion uuid_generate_v4() aktualisieren:

UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 1;
UPDATE products SET uuid_id = uuid_generate_v4() WHERE id = 2;

Diese Befehle aktualisieren unsere bestehenden Produkte mit eindeutigen UUID-Werten. Die Funktion uuid_generate_v4() generiert für jede Zeile eine neue UUID.

Sie sollten für jede Aktualisierung eine Ausgabe ähnlich dieser sehen:

UPDATE 1

Um die Daten mithilfe von UUIDs abzufragen, können Sie die UUID-Werte in Ihren WHERE-Klauseln verwenden. Da UUIDs zufällig generiert werden, lassen Sie uns zuerst unsere aktuellen Daten anzeigen:

SELECT id, name, uuid_id FROM products;

Dieser Befehl wählt alle Spalten aus der products-Tabelle aus, um die generierten UUIDs neben unseren anderen Daten anzuzeigen.

Sie sollten eine Ausgabe ähnlich dieser sehen (Ihre UUID wird anders sein):

                  id
------------------------------------
 a1b2c3d4-e5f6-7890-1234-567890abcdef
(1 row)

Verwenden Sie nun diese UUID im folgenden Befehl und ersetzen Sie <YOUR_UUID_HERE> durch die tatsächliche UUID, die Sie abgerufen haben:

SELECT name FROM products WHERE id = '<YOUR_UUID_HERE>';

Dieser Befehl wählt den name aus der products-Tabelle aus, bei der die id mit der angegebenen UUID übereinstimmt.

Sie sollten eine Ausgabe ähnlich dieser sehen (abhängig davon, welche UUID Sie ausgewählt haben):

  name
----------
 Laptop
(1 row)

Zum Schluss räumen wir die von uns erstellte Tabelle und Erweiterung auf:

DROP TABLE products;
DROP EXTENSION "uuid-ossp";

Sie sollten für jeden DROP-Befehl eine Ausgabe ähnlich dieser sehen:

DROP TABLE
DROP EXTENSION

Daten aus erweiterten Datentypen extrahieren

In diesem letzten Schritt üben Sie das Extrahieren von Daten aus allen erweiterten Datentypen, die wir im Laufe dieses Labs zu unserer products-Tabelle hinzugefügt haben. Unsere Tabelle enthält nun Spalten für JSONB, Arrays und UUIDs und bietet ein umfassendes Beispiel für die erweiterten Datentypen von PostgreSQL.

Da wir unsere bestehende Sitzung und Tabelle fortsetzen, können wir sofort mit den Daten arbeiten, die wir in den vorherigen Schritten aufgebaut haben.

Zuerst fügen wir einige zusätzliche JSONB-Daten hinzu, um unsere Extraktionsbeispiele umfassender zu gestalten:

UPDATE products SET data = '{"brand": "Dell", "model": "XPS 13", "specs": {"ram": "16GB", "storage": "512GB SSD"}, "warranty": "3 years"}' WHERE id = 1;
UPDATE products SET data = '{"brand": "Logitech", "model": "G915", "specs": {"type": "Mechanical", "backlight": "RGB"}, "warranty": "2 years"}' WHERE id = 2;

Diese Befehle aktualisieren unsere bestehenden JSONB-Daten mit detaillierteren Informationen, einschließlich verschachtelter Objekte und zusätzlicher Felder.

Sie sollten für jede Aktualisierung eine Ausgabe ähnlich dieser sehen:

UPDATE 1

Nun üben wir das Extrahieren von Daten aus all unseren erweiterten Datentypen. Um Daten aus der JSONB-Spalte data zu extrahieren, können Sie die Operatoren -> und ->> verwenden. Um beispielsweise die Marke aus unseren aktualisierten Daten zu extrahieren:

SELECT data ->> 'brand' FROM products WHERE id = 1;

Dieser Befehl ruft den Wert ab, der dem Schlüssel brand in der Spalte data zugeordnet ist.

Sie sollten eine Ausgabe ähnlich dieser sehen:

 ?column?
----------
 Dell
(1 row)

Um verschachtelte Daten aus der JSONB-Spalte zu extrahieren, können Sie die Operatoren -> und ->> verketten. Um beispielsweise die RAM-Spezifikation zu extrahieren:

SELECT data -> 'specs' ->> 'ram' FROM products WHERE id = 1;

Dieser Befehl ruft den Wert ab, der dem Schlüssel ram innerhalb des specs-Objekts zugeordnet ist.

Sie sollten eine Ausgabe ähnlich dieser sehen:

 ?column?
----------
 16GB
(1 row)

Um Daten aus der Spalte tags (Array) zu extrahieren, können Sie die Array-Indizierung verwenden, wie in Schritt 2 gelernt. Um beispielsweise das erste Tag des ersten Produkts zu extrahieren:

SELECT tags[1] FROM products WHERE id = 1;

Dieser Befehl ruft das Element am Index 1 aus dem tags-Array der products-Tabelle ab, wobei die id 1 ist.

Sie sollten eine Ausgabe ähnlich dieser sehen:

   tags
-----------
 electronics
(1 row)

Nun erstellen wir eine umfassende Abfrage, die Daten aus all unseren erweiterten Datentypen extrahiert – JSONB, Arrays und UUIDs:

SELECT
    id,
    name,
    data ->> 'brand' AS brand,
    data -> 'specs' ->> 'ram' AS ram,
    tags[1] AS first_tag,
    uuid_id
FROM products;

Dieser Befehl ruft Daten aus allen erweiterten Datentypen ab, mit denen wir gearbeitet haben: der Ganzzahl id, dem name, der brand aus der JSONB-Spalte data, der verschachtelten ram-Spezifikation, dem ersten Element des tags-Arrays und der uuid_id.

Sie sollten eine Ausgabe ähnlich dieser sehen:

   name   |  ?column?  |   tags
----------+------------+-----------
 Laptop   | Dell       | electronics
 Keyboard | Logitech   | electronics
(2 rows)

Ausgezeichnet! Sie haben erfolgreich mit allen drei erweiterten PostgreSQL-Datentypen in einer einzigen Tabelle gearbeitet. Dieses umfassende Beispiel zeigt, wie JSONB, Arrays und UUIDs zusammen verwendet werden können, um flexible und leistungsstarke Datenbankschemata zu erstellen.

Wenn Sie mit allen Schritten fertig sind, können Sie die PostgreSQL-Shell verlassen, indem Sie Folgendes eingeben:

\q

Sie können die Tabelle auch behalten, um weiter mit den erweiterten Datentypen von PostgreSQL zu experimentieren.

Zusammenfassung

In diesem Lab haben Sie schrittweise eine umfassende products-Tabelle erstellt und damit gearbeitet, die die erweiterten Datentypen von PostgreSQL in Aktion demonstriert. Sie begannen mit der Erstellung einer Tabelle mit JSONB-Spalten und lernten, wie JSON-Daten mit Operatoren wie -> und ->> gespeichert und abgefragt werden.

Anschließend erweiterten Sie die Tabelle um Array-Spalten, lernten, wie mehrere Werte in einer einzigen Spalte gespeichert und mit Array-Indizierung und Funktionen wie UNNEST abgefragt werden. Als Nächstes fügten Sie UUID-Funktionalität hinzu, indem Sie die Erweiterung uuid-ossp aktivierten und eine UUID-Spalte zum Generieren eindeutiger Identifikatoren hinzufügten.

Schließlich übten Sie umfassende Techniken zur Datenextraktion, die alle drei erweiterten Datentypen – JSONB, Arrays und UUIDs – in anspruchsvollen Abfragen kombinierten. Dieser schrittweise Ansatz zeigte, wie diese Datentypen in realen Datenbankschemata zusammenarbeiten können und Flexibilität sowie leistungsstarke Abfragefähigkeiten für moderne Anwendungen bieten.