Menu

PostgreSQL CTE: WITH Abfragen und WITH RECURSIVE

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.

Diese Seite enthält ausführbare Editoren. Bearbeiten, ausführen und Ausgabe sofort sehen.

Eine Common Table Expression (CTE) ist eine benannte Unterabfrage in einer WITH Klausel vor der Hauptanweisung: WITH name AS (SELECT ...) SELECT ... FROM name. Die Hauptabfrage liest die CTE wie eine Tabelle, verschachtelte Unterabfragen werden so zu Schritten, die du von oben nach unten liest.

Die CTE totals summiert die Bestellungen jedes Kunden, und die Hauptabfrage behält die Kunden über 100: cy mit 300 und ana mit 180. Eine CTE existiert nur für die Anweisung, zu der sie gehört.

Mehrere CTEs verketten

Trenne CTEs mit Kommas. Jede kann die CTEs lesen, die vor ihr definiert sind.

average liest totals, und die Hauptabfrage liest beide. Die Kundensummen sind 180, 70 und 300, ihr Durchschnitt liegt bei etwa 183, also liegt nur cy darüber.

Einen Baum mit WITH RECURSIVE durchlaufen

Mit WITH RECURSIVE darf eine CTE auf sich selbst verweisen. Sie hat zwei Teile, verbunden durch UNION ALL:

  • die Ankerabfrage, die die Startzeilen liefert;
  • die rekursive Abfrage, die die CTE mit der Tabelle joint, um die nächste Ebene zu finden.

PostgreSQL führt den rekursiven Teil auf den Zeilen aus, die der vorige Schritt erzeugt hat, und hört auf, wenn ein Schritt keine Zeilen liefert.

Jeder Schritt findet die Personen, die an die Zeilen aus dem vorigen Schritt berichten. Mia landet auf Tiefe 3 mit dem Pfad Rosa > Omar > Ivo > Mia. Um statt des ganzen Baums nur alle unter einer Führungskraft aufzulisten, änderst du das WHERE des Ankers in id = 2.

Um nach oben zu laufen, von einem Mitarbeiter bis zur Spitze, drehst du die Join-Bedingung zu s.id = c.manager_id um.

Eine endlose rekursive CTE stoppen

Eine rekursive CTE endet erst, wenn ein Schritt nichts mehr liefert. Bei Daten mit Schleifen (A berichtet an B, B berichtet an A) endet sie nie. Füge eine Tiefengrenze wie WHERE c.depth < 20 hinzu, oder nimm die CYCLE Klausel (PostgreSQL 14+), die die erste wiederholte Zeile markiert und ihr nicht weiter folgt:

WITH RECURSIVE chain AS (
  SELECT id, manager_id FROM staff WHERE id = 5
  UNION ALL
  SELECT s.id, s.manager_id FROM staff s JOIN chain c ON s.id = c.manager_id
) CYCLE id SET is_cycle USING visited
SELECT * FROM chain;

Zeilen mit einer rekursiven CTE erzeugen

Der rekursive Teil braucht keine Tabelle. Das hier zählt von 1 bis 5, indem es den Wert jeder Zeile wieder einspeist:

Für einfache Zahlen- oder Datumsreihen ist generate_series kürzer.

Zeilen mit einer datenändernden CTE verschieben

Eine CTE kann ein INSERT, UPDATE oder DELETE mit einer RETURNING Klausel sein, und die Hauptanweisung liest die zurückgegebenen Zeilen. Das hier verschiebt alte Bestellungen in einer einzigen Anweisung in eine Archivtabelle:

Die Bestellungen 1 und 3 werden zusammen gelöscht und ins Archiv eingefügt, ein Fehler lässt also beide Tabellen, wie sie waren. Alle Teile der Anweisung sehen denselben Snapshot: Die Hauptabfrage sieht die Wirkung des DELETE auf orders nicht, nur das, was RETURNING weitergibt (siehe RETURNING).

Einbetten mit MATERIALIZED steuern

Seit PostgreSQL 12 wird eine CTE ohne Nebenwirkungen, die die Hauptabfrage einmal referenziert, eingebettet. Der Planer optimiert sie zusammen mit der äußeren Abfrage, ein WHERE außerhalb der CTE kann also trotzdem einen Index nutzen. Eine CTE, die zwei- oder mehrmals referenziert wird, wird einmal berechnet und ihr Ergebnis wiederverwendet. Du kannst beide Verhalten erzwingen:

WITH recent AS MATERIALIZED (
  SELECT * FROM events WHERE created_at > now() - interval '1 day'
)
SELECT * FROM recent WHERE user_id = 42;

WITH recent AS NOT MATERIALIZED (...)

MATERIALIZED berechnet die CTE einmal für sich und hält das äußere WHERE aus ihr heraus. Nimm es, wenn der eingebettete Plan langsamer ausfällt. NOT MATERIALIZED verlangt das Einbetten auch dann, wenn die CTE mehr als einmal referenziert wird. Rekursive und datenändernde CTEs werden immer separat berechnet.

Häufig gestellte Fragen

Ist eine CTE in PostgreSQL schneller als eine Unterabfrage?

Meist ist keine von beiden schneller. Seit PostgreSQL 12 wird eine CTE, die einmal referenziert wird und keine Nebenwirkungen hat, in die Hauptabfrage eingebettet und bekommt so denselben Plan wie die entsprechende Unterabfrage. Vor Version 12 wurde jede CTE separat berechnet, was die Nutzung von Indizes verhindern konnte.

Kann ich eine CTE in einem UPDATE oder DELETE verwenden?

Ja. WITH kann vor INSERT, UPDATE, DELETE und MERGE stehen, und die CTE selbst kann ein INSERT, UPDATE oder DELETE mit RETURNING sein, dessen Zeilen die Hauptanweisung liest.

Wie stoppe ich eine Endlosschleife in einer rekursiven CTE?

Gib dem rekursiven Teil eine Bedingung, die irgendwann keine Zeilen mehr liefert, etwa eine Tiefengrenze (WHERE depth < 10). Für Daten, die auf sich selbst zurückführen können, markiert die CYCLE Klausel (PostgreSQL 14+) eine wiederholte Zeile und hält dort an.

Kann ich eine CTE in mehr als einer Abfrage nutzen?

Nein. Eine CTE existiert nur für die Anweisung, in der sie steht. Um eine Abfrage in mehreren Anweisungen wiederzuverwenden, legst du eine View oder eine temporäre Tabelle an.

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's