PostgreSQL Tutorial mit ausführbaren Beispielen
Ein kostenloses PostgreSQL Tutorial mit Abfragen, die auf der Seite laufen: Datentypen, CREATE TABLE, Upserts, JSONB, Datumswerte, CTEs, Fensterfunktionen, Indizes und VACUUM. Ändere eine Abfrage und starte sie neu.
Kurs startenErste Schritte
- PostgreSQL vs MySQLPostgreSQL und MySQL sind beide kostenlose relationale Datenbanken. PostgreSQL hat das reichere SQL, JSONB und transaktionales DDL; MySQL ist dort einfacher zu betreiben, wo es schon Standard ist.
- PostgreSQL vs SQL ServerPostgreSQL ist kostenlos und Open Source; SQL Server ist die kommerzielle Datenbank von Microsoft mit T-SQL und Werkzeugen rund um Windows. So unterscheiden sich SQL, JSON, Sperren und Lizenzen.
- PostgreSQL vs MongoDBPostgreSQL speichert Zeilen in Tabellen mit festem Schema und kann JSON-Dokumente in jsonb Spalten halten; MongoDB speichert Dokumente mit flexiblem Schema in Collections und verteilt sie per Sharding auf Server.
- Datenbanken und Tabellen anzeigenIn psql listet \l die Datenbanken auf, \c wechselt zu einer und \dt zeigt ihre Tabellen. In SQL fragst du pg_database, information_schema.tables und information_schema.columns ab.
Tabellen und Typen
- DatentypenDie PostgreSQL Datentypen für fast jede Spalte: Integer, numeric, text, boolean, timestamptz, uuid, jsonb, Arrays und Enums, und wann du welchen nimmst.
- CREATE TABLECREATE TABLE legt die Spalten, Typen und Constraints einer Tabelle fest. Mit Identity-Schlüsseln, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS und LIKE.
- ALTER TABLEALTER TABLE ändert eine bestehende Tabelle: Spalten hinzufügen, umbenennen und löschen, den Typ einer Spalte mit USING ändern, Defaults und NOT NULL setzen und Constraints anlegen.
- PrimärschlüsselWie du in PostgreSQL einen Primärschlüssel definierst und wie du ihn erzeugst: Identity-Spalten, das ältere SERIAL und UUIDs mit gen_random_uuid(). Dazu zusammengesetzte Schlüssel und das Zurücksetzen einer Sequenz.
- Foreign KeysEin Foreign Key sorgt dafür, dass eine Spalte nur Werte annimmt, die es in einer anderen Tabelle gibt. Mit REFERENCES, ON DELETE CASCADE, SET NULL und RESTRICT, einem Schlüssel für eine bestehende Tabelle und seinem Index.
- Enum-TypenEin Enum-Typ ist eine feste, geordnete Liste erlaubter Werte, angelegt mit CREATE TYPE ... AS ENUM. Mit Werte hinzufügen und umbenennen, Werte auflisten, Sortieren und wann ein CHECK Constraint die bessere Wahl ist.
- Timestampstimestamptz speichert einen Zeitpunkt und zeigt ihn in der Zeitzone der Sitzung; timestamp speichert eine Uhrzeit ohne Zone. Keiner von beiden speichert die Zone, die du geschrieben hast. Nimm fast immer timestamptz.
Abfragen
- JoinsEin Join verbindet Zeilen aus zwei Tabellen über eine Bedingung. INNER JOIN behält Treffer, LEFT JOIN jede Zeile der ersten Tabelle, FULL JOIN beide Seiten, CROSS JOIN kombiniert alles, und LATERAL führt pro Zeile eine Unterabfrage aus.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort behält die erste Zeile jeder Gruppe, etwa die neueste Bestellung jedes Kunden. Das gibt es nur in PostgreSQL.
- COALESCE, NULLIF und CASECOALESCE(a, b, ...) liefert das erste Argument, das nicht NULL ist, NULLIF(a, b) macht einen Wert zu NULL, und CASE WHEN wählt einen Wert nach einer Bedingung.
- LIKE, ILIKE und RegexLIKE vergleicht Text mit einem Muster aus % und _, ILIKE macht dasselbe ohne Rücksicht auf Groß- und Kleinschreibung, und die ~ Operatoren prüfen POSIX-Regex.
- CAST und ::CAST(value AS type) und value::type wandeln in PostgreSQL einen Wert in einen anderen Typ um. Beides ist dieselbe Umwandlung; :: ist die kürzere Schreibweise von PostgreSQL.
Daten ändern
- Upsert (ON CONFLICT)Upserts in PostgreSQL mit INSERT ... ON CONFLICT: DO UPDATE mit EXCLUDED, DO NOTHING, bedingte Updates, Zähler und wie du eingefügte von aktualisierten Zeilen unterscheidest.
- RETURNINGMit RETURNING geben INSERT, UPDATE, DELETE und MERGE die Zeilen zurück, die sie geändert haben: generierte IDs, neue Werte, gelöschte Zeilen und welche MERGE-Aktion lief.
- TransaktionenSo funktionieren Transaktionen in PostgreSQL: BEGIN, COMMIT und ROLLBACK, Savepoints, was ein Fehler mit einer offenen Transaktion macht, und die Isolationsstufen.
JSON und Arrays
- JSONBJSON in PostgreSQL mit jsonb speichern und abfragen: die Operatoren -> und ->>, Containment mit @>, jsonb_set und andere Updates, Arrays in Zeilen zerlegen, GIN-Indizes und json vs jsonb.
- ArraysArray-Spalten in PostgreSQL: deklarieren, ab 1 indizieren, mit ANY, @> und && prüfen, ob ein Array einen Wert enthält, unnest und die Array-Funktionen, die du brauchst.
- string_agg und array_aggstring_agg fügt die Werte einer Gruppe zu einem String zusammen, und array_agg sammelt sie in einem Array. Reihenfolge, DISTINCT, FILTER, Umgang mit NULL und die typischen Fehler.
Strings und Datumswerte
- String-FunktionenDie PostgreSQL String-Funktionen für jeden Tag: substring, left und right, || und concat, split_part, position, replace, regexp_replace, trim, lpad und format.
- DatumsfunktionenMit Datumswerten in PostgreSQL arbeiten: now() und current_date, Intervalle addieren und subtrahieren, date_trunc zum Gruppieren, extract und age und Formatieren mit to_char.
- generate_seriesgenerate_series liefert eine Menge von Zeilen von einem Start- bis zu einem Endwert: Zahlen, Daten und Timestamps. Damit füllst du Lücken in Berichten, erzeugst Testdaten und baust Kalender.
Fortgeschrittene Abfragen
- CTE (WITH)Eine Common Table Expression gibt einer Unterabfrage in einer WITH-Klausel einen Namen, damit die Hauptabfrage sie wie eine Tabelle liest. Mit WITH RECURSIVE verweist sie auf sich selbst und durchläuft Bäume und Graphen.
- Window FunctionsWindow Functions berechnen für jede Zeile einen Wert aus einer Gruppe zusammengehöriger Zeilen, ohne sie zusammenzufassen: Rankings, laufende Summen, gleitende Durchschnitte und Vergleiche mit der vorigen Zeile.
Performance und Wartung
- IndizesCREATE INDEX baut standardmäßig einen B-Tree-Index, damit PostgreSQL Zeilen findet, ohne die ganze Tabelle zu lesen. Andere Typen (GIN, GiST, BRIN, Hash) decken JSONB, Arrays, Volltextsuche, Bereiche und sehr große Tabellen ab.
- EXPLAIN ANALYZEEXPLAIN zeigt den Plan, den PostgreSQL für eine Abfrage wählt, und EXPLAIN ANALYZE führt die Abfrage aus und ergänzt die echten Zeilenzahlen und Zeiten jedes Schritts. So liest du den Planbaum und die gängigen Knoten.
- Views und Materialized ViewsEine View ist eine gespeicherte Abfrage, die bei jedem Lesen läuft. Eine Materialized View speichert das Ergebnis der Abfrage und ändert sich nur, wenn du REFRESH MATERIALIZED VIEW ausführst.
- VACUUMVACUUM entfernt die toten Zeilenversionen, die UPDATE und DELETE hinterlassen, damit ihr Platz wiederverwendet werden kann. Autovacuum erledigt das für dich; diese Seite zeigt wann, wie du es einstellst und wann VACUUM FULL seinen Lock wert ist.
Funktionen und Trigger
- Funktionen und ProzedurenCREATE FUNCTION definiert eine Funktion, die du in Abfragen aufrufst, geschrieben in SQL oder PL/pgSQL. CREATE PROCEDURE definiert eine Routine, die du mit CALL startest und die unterwegs Transaktionen committen kann.
- TriggerEin Trigger führt automatisch eine Funktion aus, wenn Zeilen eingefügt, aktualisiert oder gelöscht werden. In PostgreSQL schreibst du eine Triggerfunktion, die trigger zurückgibt, und hängst sie mit CREATE TRIGGER an eine Tabelle.