Daten in PostgreSQL validieren

Ein praktischer Leitfaden zur Datenvalidierung in PostgreSQL — die sechs Prüfungen, die jede Tabelle braucht, das SQL dazu, die NULL- und Sperrfallen, die Sie umgehen sollten, und wie daraus ein versionierter Data Contract wird.

2026-08-27 · 7 min read

Um Daten in PostgreSQL zu validieren, formulieren Sie jede Erwartung als Aggregatabfrage, die eine Anzahl von Verstößen zurückgibt, führen diese Abfragen gemeinsam gegen die Tabelle aus und lassen die Prüfung fehlschlagen, sobald eine Anzahl ihren Schwellenwert überschreitet. Sechs Prüfungen decken die überwiegende Mehrheit realer Vorfälle ab: NULL-Werte in Pflichtspalten, doppelte Schlüssel, Werte außerhalb einer erlaubten Menge, Zahlen außerhalb eines plausiblen Bereichs, fehlerhaft formatierte Zeichenketten und veraltete Zeilen. Postgres bietet echte reguläre Ausdrücke und eine ehrliche NULL-Semantik, was die Sache einfacher macht als in den meisten Engines — es hat aber auch eigene Fallstricke, vor allem rund um NULL, Statistiken und lang laufende Scans auf einer Primärdatenbank.

Warum PostgreSQL ein guter Einstieg ist

Postgres hat von allen gängigen Systemen die reichhaltigste Oberfläche für Validierung:

• Echte Regex. Der Operator ~ arbeitet nach POSIX Extended, verankerte Muster, Alternativen und Quantoren funktionieren also ohne Übersetzung.
• information_schema plus pg_catalog. Spaltentypen, Nullbarkeit und Primärschlüssel lassen sich in standardisierter Form abfragen, der Schema-Import ist deshalb exakt statt geraten.
• Günstige Näherungswerte. pg_class.reltuples liefert kostenlos eine Schätzung des Planners, wenn ein exaktes COUNT(*) zu teuer wäre.

Der Preis dieser Reichhaltigkeit: Postgres ist meist Ihre transaktionale Primärdatenbank, kein Data Warehouse. Validierungsabfragen sind vollständige Scans, und vollständige Scans auf einer Primärdatenbank konkurrieren mit Ihrer Anwendung. Alles, was nicht Postgres-spezifisch ist — wo Prüfungen laufen sollten, wie man sie plant, welche Tabellen zuerst abgedeckt gehören —, steht im vollständigen Leitfaden zur Datenvalidierung.

Die sechs Prüfungen, die jede Tabelle braucht

1. Vollständigkeit — NULL-Werte in Pflichtspalten

SELECT count(*) AS violations
FROM sales.orders
WHERE order_id IS NULL;

2. Eindeutigkeit — doppelte Geschäftsschlüssel

SELECT count(*) AS violations
FROM (
    SELECT order_id
    FROM sales.orders
    WHERE order_id IS NOT NULL
    GROUP BY order_id
    HAVING count(*) > 1
) dupes;

Ein Unique Index würde das bereits beim Schreiben verhindern. In einem Analyseschema, das von einem ELT-Job befüllt wird, existiert dieser Index in der Regel nicht — dbt-Modelle werden als gewöhnliche Tabellen neu erzeugt, und CREATE TABLE AS SELECT überträgt keine Constraints. Die Duplikatprüfung ist das Einzige, was diese Lücke schließt.

3. Konformität — Werte außerhalb einer erlaubten Menge

SELECT count(*) AS violations
FROM sales.orders
WHERE status IS NOT NULL
  AND status NOT IN ('pending', 'paid', 'shipped', 'refunded');

Die Absicherung mit IS NOT NULL ist nicht optional. NULL NOT IN (...) ergibt NULL, nicht true; NULL-Werte verschwinden also aus dem Ergebnis, und eine Spalte, die nur aus NULL-Werten besteht, meldet perfekte Konformität. Das ist das mit Abstand häufigste falsche „bestanden" in handgeschriebenem Datenqualitäts-SQL.

4. Genauigkeit — Zahlen außerhalb eines plausiblen Bereichs

SELECT count(*) AS violations
FROM sales.orders
WHERE total_amount IS NOT NULL
  AND (total_amount < 0 OR total_amount > 100000);

Verwenden Sie numeric für Geldbeträge. Bei double precision werden Bereichsgrenzen mit Rundungsfehlern verglichen, und eine als > 100000 geschriebene Prüfung widerspricht gelegentlich sich selbst.

5. Konformität — fehlerhaft formatierte Bezeichner

SELECT count(*) AS violations
FROM sales.orders
WHERE reference IS NOT NULL
  AND reference !~ '^ORD-[0-9]{6}
#39;;

Nutzen Sie ~ für Groß-/Kleinschreibung beachtendes Matching und ~* für den Fall, dass sie ignoriert werden soll. Anders als im SQL Server ist keine Übersetzung nötig — das Muster, das Sie schreiben, ist das Muster, das ausgeführt wird.

6. Aktualität — Freshness

SELECT count(*) AS violations
FROM sales.orders
WHERE created_at < now() - interval '24 hours';

Bevorzugen Sie timestamptz gegenüber timestamp. Eine reine timestamp-Spalte trägt keine Zeitzone, now() vergleicht also gegen das, was der Parameter TimeZone der Session gerade sagt — und dieselbe Prüfung liefert bei zwei Clients unterschiedliche Antworten.

Referenzielle Integrität über Tabellen hinweg

Postgres ist eine der wenigen Engines, in denen die tabellenübergreifende Prüfung wirklich günstig ist, weil der Planner einen Hash Join wählt statt eine korrelierte Unterabfrage auszuführen:

SELECT count(*) AS violations
FROM sales.orders o
LEFT JOIN sales.customers c ON o.customer_id = c.id
WHERE o.customer_id IS NOT NULL
  AND c.id IS NULL;

Verwaiste Fremdschlüssel sind das klassische Symptom eines unvollständigen Backfills — eine Tabelle neu geladen, die übergeordnete nicht — und sie bleiben unsichtbar, bis ein Dashboard in einem Inner Join stillschweigend Zeilen verliert.

Eine sichere Nur-Lese-Rolle einrichten

CREATE ROLE catalyst_ro LOGIN PASSWORD '<generated>';
GRANT CONNECT ON DATABASE analytics TO catalyst_ro;
GRANT USAGE ON SCHEMA sales TO catalyst_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA sales TO catalyst_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA sales
  GRANT SELECT ON TABLES TO catalyst_ro;

Die Zeile mit ALTER DEFAULT PRIVILEGES ist die, die alle vergessen: Ohne sie ist jede Tabelle, die Ihr ELT-Job morgen neu anlegt, für die Validierungsrolle unsichtbar, und Prüfungen scheitern mit Berechtigungsfehlern, die wie Datenprobleme aussehen.

Dann schützen Sie die Primärdatenbank:

ALTER ROLE catalyst_ro SET statement_timeout = '60s';
ALTER ROLE catalyst_ro SET idle_in_transaction_session_timeout = '30s';

Besser noch: Richten Sie die Verbindung auf ein Read Replica. Validierungsabfragen sind lesende Aggregat-Scans ohne Sortieranforderung — genau die Last, für die Replicas existieren.

Von Ad-hoc-SQL zum Data Contract

Ad-hoc-Skripte laufen mit der Zeit aus dem Takt mit den Tabellen, die sie prüfen. Die Erwartungen deklarativ zu formulieren, löst das Problem — die Regeln liegen beim Schema, in einem Format, das sowohl ein Mensch als auch ein Runner lesen kann. Catalyst nutzt den Open Data Contract Standard:

apiVersion: v3.0.0
kind: DataContract
info:
  title: orders
  version: 1.4.0
  owner: data-platform
schema:
  - name: orders
    physicalName: orders
    physicalType: table
    properties:
      - name: order_id
        logicalType: string
        physicalType: uuid
        required: true
        primaryKey: true
        quality:
          - rule: nullCount
            dimension: completeness
            severity: error
            mustBe: "0"
          - rule: duplicateCount
            dimension: uniqueness
            severity: error
            mustBe: "0"
      - name: customer_id
        logicalType: string
        quality:
          - rule: referentialIntegrity
            dimension: consistency
            severity: error
            mustBe: "customers.id"
      - name: status
        logicalType: string
        quality:
          - rule: validValues
            dimension: conformity
            severity: error
            mustBe: "['pending', 'paid', 'shipped', 'refunded']"
      - name: total_amount
        logicalType: number
        physicalType: numeric
        quality:
          - rule: between
            dimension: accuracy
            severity: error
            mustBe: "[0, 100000]"
      - name: created_at
        logicalType: timestamp
        physicalType: timestamptz
        quality:
          - rule: freshness
            dimension: timeliness
            severity: error
            mustBe: "<= 24h"
    quality:
      - rule: rowCount
        dimension: consistency
        severity: warning
        mustBe: "> 0"

Catalyst importiert die Spalten aus information_schema, schlägt anhand der vorgefundenen Typen und Nullbarkeit einen Basisvertrag vor, kompiliert jeden quality-Eintrag in das oben gezeigte Postgres-SQL und protokolliert das Ergebnis jeder Prüfung. Weil das YAML verlustfrei hin- und zurückläuft — Kommentare und Schlüsselreihenfolge überstehen Änderungen im visuellen Builder —, ist der Contract, den Sie im Pull Request prüfen, Byte für Byte derselbe, der ausgeführt wird.

PostgreSQL-Stolperfallen, die Sie kennen sollten

Zeitplanung und Alarmierung

Führen Sie die Prüfungen direkt nach dem Ladevorgang aus, der die Daten erzeugt, nicht nach einem glatten Cron-Zeitpunkt. Eine Tagestabelle, die um 06:00 Uhr validiert wird, während das ELT erst um 06:40 Uhr fertig ist, scheitert jeden Morgen aus einem Grund, der nichts mit Qualität zu tun hat.

Alarmieren Sie bei Übergängen, nicht bei Zuständen. „Orders hat angefangen zu scheitern" ist handlungsleitend; „Orders scheitert immer noch", stündlich wiederholt, ist der Weg, auf dem ein Channel stummgeschaltet wird. Trennen Sie die Severity entsprechend — error für Prüfungen, die nachgelagerte Konsumenten blockieren sollen, warning für die, die Sie als Trendlinie sehen wollen.

Häufig gestellte Fragen

Kann ich PostgreSQL-Daten validieren, ohne SQL zu schreiben?

Ja. Deklarieren Sie die Erwartung — Pflichtfeld, eindeutig, erlaubte Werte, Zahlenbereich, Regex-Muster, maximales Alter, Fremdschlüssel — und lassen Sie das Werkzeug sie kompilieren. Catalyst liest Ihre Spalten und Typen aus information_schema, schlägt einen Startsatz an Regeln vor und erzeugt die Abfragen. Handgeschriebenes SQL bleibt wirklich maßgeschneiderter Logik vorbehalten, über eine customSql-Regel.

Braucht die Validierung Schreibzugriff auf meine Datenbank?

Nein. Jede Prüfung ist ein SELECT, das eine einzige Zahl zurückgibt. CONNECT auf der Datenbank, USAGE auf dem Schema und SELECT auf den Tabellen sind der vollständige Rechtesatz. Catalyst speichert ausschließlich Metadaten und Prüfergebnisse; die Zeilen verlassen Ihren Server nie.

Sollte ich Validierungen gegen ein Read Replica laufen lassen?

Ja, sofern Sie eines haben. Die Prüfungen sind vollständige Aggregat-Scans ohne Konsistenzanforderung jenseits von „aktuell", eine Replikationsverzögerung von wenigen Sekunden ist also irrelevant. Sie von der Primärdatenbank wegzunehmen, entkräftet den wichtigsten betrieblichen Einwand dagegen, sie häufig auszuführen.

Wie prüfe ich in Postgres einen regulären Ausdruck?

Verwenden Sie den Operator ~ für POSIX-Matching mit Beachtung der Groß-/Kleinschreibung oder ~* ohne, und negieren Sie mit !~. Postgres benötigt keine Musterübersetzung — anders als SQL Server, wo Regex-Regeln als LIKE umgeschrieben werden müssen.

Worin unterscheidet sich das von dbt-Tests oder CHECK-Constraints?

CHECK-Constraints weisen fehlerhafte Zeilen bereits beim Schreiben zurück. Für eine transaktionale Tabelle ist das richtig, für ein Data Warehouse falsch — dort möchten Sie die Daten lieber landen lassen und in Quarantäne stellen. dbt-Tests laufen innerhalb eines dbt-Builds und decken deshalb nur Modelle ab, die dbt gehören. Ein Data Contract steht über beidem: Er beschreibt die Zusagen der Tabelle in einem portablen Format, versioniert unabhängig von einem einzelnen Pipeline-Werkzeug, und gilt gleichermaßen für Tabellen, die von dbt, Airflow oder einem selbstgebauten Loader erzeugt wurden.