psql Kapitel 7: Erweiterungen & Spezialfunktionen

PostgreSQL ist nicht nur eine klassische SQL-Datenbank – es ist ein Framework für Datenverarbeitung. Du kannst neue Datentypen, Funktionen und sogar eigene Programmiersprachen hinzufügen.

🔹 Erweiterungen verwalten

Alle installierbaren Erweiterungen anzeigen:

dx

Eine Erweiterung aktivieren:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

👉 Erweiterungen sind wie Plugins, die zusätzliche Features bereitstellen. 🔹 JSON & JSONB Postgres unterstützt JSON-Daten von Haus aus – MySQL nur eingeschränkt. Tabelle mit JSONB

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    data JSONB
);

Daten einfügen

INSERT INTO users (name, data)
VALUES ('Anna', '{"stadt": "Berlin", "alter": 29}');

Daten abfragen

SELECT name, data->>'stadt' AS stadt
FROM users
WHERE data->>'alter' = '29';

👉 JSONB speichert Daten binär & effizient – schneller als reines JSON.

🔹 Arrays

Postgres kann Arrays direkt speichern:

CREATE TABLE produkte (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    tags TEXT[]
);

Einfügen:

INSERT INTO produkte (name, tags)
VALUES ('Laptop', '{"Elektronik","Computer","Mobil"}');

Abfragen:

SELECT * FROM produkte WHERE 'Computer' = ANY(tags);

🔹 UUIDs (Universally Unique Identifier)

UUIDs sind eindeutige IDs über Systeme hinweg – oft besser als SERIAL. Erweiterung aktivieren:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

Tabelle mit UUID:

CREATE TABLE kunden (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name VARCHAR(100)
);

🔹 PostGIS (Geodaten)

PostGIS macht PostgreSQL zu einem Geodaten-GIS-System. Damit kannst du Standorte, Karten und Entfernungen speichern und berechnen. Beispiel:

CREATE EXTENSION postgis;

Tabelle mit Geodaten:

CREATE TABLE orte (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    position GEOGRAPHY(Point, 4326)
);

👉 Damit kannst du Abfragen wie "Alle Orte im Umkreis von 10 km" machen.

🔹 Eigene Funktionen schreiben

Mit PL/pgSQL (Postgres’ eigener Sprache) kannst du Funktionen schreiben:

CREATE OR REPLACE FUNCTION verdoppeln(x INT)
RETURNS INT AS $$
BEGIN
  RETURN x * 2;
END;
$$ LANGUAGE plpgsql;

Test:

SELECT verdoppeln(5);

👉 Ergebnis: 10

🔹 Unterschiede zu MySQL/MariaDB

• PostgreSQL hat mehr Datentypen (JSONB, Arrays, Geodaten).
• Erweiterungen wie PostGIS machen Postgres einzigartig.
• MySQL ist einfacher, aber weniger flexibel.

🔹 Übungen

1. Erstelle eine Tabelle users mit einer Spalte data JSONB und speichere zusätzliche Infos (Alter, Stadt).
2. Füge einen Benutzer mit JSON-Daten ein und frage die Stadt ab.
3. Lege eine Tabelle mit tags TEXT[] an und suche nach einem bestimmten Tag.
4. Bonus: Aktiviere uuid-ossp und erstelle eine Tabelle mit UUID-Primärschlüssel.

✅ Zusammenfassung

• PostgreSQL unterstützt JSONB, Arrays und UUIDs direkt.
• Erweiterungen wie uuid-ossp oder PostGIS erweitern die Datenbank enorm.
• Eigene Funktionen mit PL/pgSQL ermöglichen komplexe Logik in der Datenbank.
• Postgres ist dadurch flexibler und mächtiger als MySQL/MariaDB.

👉 Im nächsten Kapitel lernst du, wie man Backups & Restore in PostgreSQL macht – ein absolutes Muss für Admins.  

📚 Inhaltsverzeichnis

👉 psql Kapitel 1: Installation & Anmeldung mit psql 👉 psql Kapitel 2: Benutzer & Rollen in PostgreSQL 👉 psql Kapitel 3: Datenbanken & Tabellen erstellen 👉 psql Kapitel 4: CRUD-Befehle (SELECT, INSERT, UPDATE, DELETE) 👉 psql Kapitel 5: Schlüssel & Constraints in PostgreSQL 👉 psql Kapitel 6: Indizes & Performance 👉 psql Kapitel 7: Erweiterungen & Spezialfunktionen 👉 psql Kapitel 8: Backups & Restore in PostgreSQL

⭐ Bonus-Kapitel

👉 psql Kapitel 9 (Bonus): Admin-Tipps & Best Practices für PostgreSQL