PostgreSQL Standard SQL Gap Analyse

Letzte Woche habe ich auf der PGCon.org in Ottawa einen Vortrag „PostgreSQL Standard SQL Gap Analysis“ gehalten. Falls dir das bekannt vorkommt, verwechselst du es vielleicht mit dem gegenteiligen Vortrag „Modernes SQL: Wie PostgreSQL die Konkurrenz aussticht“, den ich auf der FOSDEM und der PgConf.de gehalten habe.

Der Inhalt der Gap-Analyse:

PostgreSQL supports an impressive number of standard SQL features in an outstanding quality. Yet there remain some cases where other databases exceed PostgreSQL’s capabilities in regard to standard SQL conformance.

This session presents the gaps found during an in-depth comparison of selected standard SQL features among six popular SQL databases. The selected features include, among others, window functions and common tables expressions—both of them were recently introduced to MySQL and MariaDB.

The comparison uses a set of conformance tests I use for my website modern-sql.com. These tests are based on the SQL:2016 standard and attempt to do a rather complete test of the requirements set out in the standard. This includes the correct declared type of expressions as well as the correct SQLSTATE in case of errors (teaser: nobody seems to care about SQLSTATE).

This presentation covers two aspects: (1) features not supported by PostgreSQL but by other databases; (2) features available in PostgreSQL that are less complete or conforming as in other databases.

Du kannst die Folien hier runterladen [PDF; 5MB].

Als Kostprobe liste ich die erwähnten Funktionen hier mir kurzen Kommentaren auf. In den Folien finden sich weitere Hintergründe und Charts, die zeigen welche Datenbanken diese Funktionen unterstützen.

Unvollständige und „falsch“ umgesetzte Funktionen

Den Anfang machen Funktionen, die in PostgreSQL zwar umgesetzt wurden, aber weniger vollständig oder korrekt als in anderen Datenbanken.

extract

In PostgreSQL liefert der extract-Ausdruck einen double-Wert anstatt einer exakten Zahl (z. B. numeric).

[respect|ignore] nulls für lead, lag, first_value, last_value und nth_value

PostgreSQL unterstützt den [respect|ignore] null-Modifier bei diesen Window-Funktionen nicht.

Distinct-Aggregate als Window-Funktion

PostgreSQL unterstützt kein distinct in Aggregatfunktionen, wenn sie als Window-Funktion (over) genutzt werden: count(distinct …) over(…).

fetch [first|next] …

Fetch first ist die Standardklausel für limit. PostgreSQL unterstützt dabei keine prozentualen Einschränkungen und kennt den with ties-Zusatz nicht.

Funktionale Abhängigkeiten

PostgreSQL erkennt nur wenige der „known functional dependencies“ des Standards.

Funktionen, die in PostgreSQL fehlen

Als Nächstes sehen wir uns Funktionen an, die von mindestens einer gängigen Datenbank, nicht aber von PostgreSQL unterstützt werden.

Row Pattern Recognition (match_recognize)

Im Vortrag habe ich klar gemacht, dass ich das für die SQL-Erweiterung des Jahrzehntes halte. Wenn du glaubst, Window-Funktionen haben das Gesicht von SQL geändert, dann wirst du die Zeilenmustererkennung lieben.

Mehr darüber findest du im technischen Report von ISO (gratis): Row Pattern Recognition in SQL [ZIP+PDF; 850kB]

Noch mehr darüber gibt es in meinen Folien über Zeilenmustererkennung und in Stew Ashton’s Blog.

Temporale und bi-temporale Tabellen

Dies umfasst System- und Applikationsversionierung und wird manchmal auch als „Zeitreisen“ oder „temporale Gültigkeit“ bezeichnet. Überraschend ist, das PostgreSQL unter den sieben analysierten Datenbanken zu der Minderheit gehört, die hierzu nichts anzubieten haben. Das ist jedoch erst seit einer Woche so, als MariaDB 10.3 erschienen ist.

Worum geht es bei diesen Funkionen? Lies dazu den besten, frei verfügbaren Artikel: Temporal features in SQL:2011 [PDF; 220kB]

Generierte Spalten

Auch hier gehört PostgreSQL zur Minderheit der Datenbaken, die diese Funktion nicht anbieten. Das liegt wohl daran, dass bei PostgreSQL Indizes auf Ausdrücken auch ohne Umweg über generierte Spalten funktionieren.

Kombinierte Datenänderung und Abfrage

Auf diese Funktion wurde ich kürzlich durch Lukas Eders Artikel auf dem jOOQ-Blog aufmerksam. Im Wesentlichen ist es die Standardvariante für insert, update, delete in PostgreSQL with-Klauseln („writeable CTE“). Der Standard bietet jedoch auch die Möglichkeit, bei einer Update-Anweisung die alten Daten abzufragen.

Partitionierter Join

Das hat nichts mit partitionierten Tabellen zu tun. Es geht vielmehr um das Füllen von Löchern in Zeitreihen. Wenn man nur eine Zeitreihe hat, erledigt man das recht einfach mit einem Outer-Join. Gibt es jedoch mehrere Zeitreihen, bei denen alle Löcher gefüllt werden sollen, hilft der partitionierte Join:

SELECT * 
  FROM data PARTITION BY (grp) 
 RIGHT JOIN generate_series(...) 
         ON ...

listagg

Über listagg habe ich bereits einen ganzen Artikel geschrieben. Natürlich hat PostgreSQL string_agg und andere Möglichkeiten ein ähnliches Ergebnis zu erhalten – nur das string_agg eben nicht Standard ist. Wen kümmerst? Nunja, SQL Server hat auch eine string_agg-Funktion – jedoch mit einer anderen Syntax. Genau das sollte mit Standardfunktionen nicht passieren.

Unterscheidbare Datentypen

Hier geht es um create type ... as <vordefinierter Typ>. Natürlich unterstützt PostgreSQL strukturierte Typen und sogar Domains aber eben nicht diese Methode, einen neuen Namen – samt Typensicherheit – für einen vordefinierten Datentypen wie integer einzuführen.

In Arbeit

Zu guter Letzt habe ich auch noch zwei Funktionen erwähnt, an denen aktuell gearbeitet wird:

merge

Die Standardvariante für upsert (update oder insert) – mit mehr Möglichkeiten. Der Patch war für PostgreSQL 11 bereits committed, wurde kurz darauf jedoch wieder zurückgenommen. Ich glaube, dass für PostgreSQL 12 ein weiterer Versuch unternommen wird.

Ich habe den Patch getestet und keine groben Lücken festgestellt.

JSON

Natürlich hat PostgreSQL JSON-Funktionen. Es ist nur so, dass Ende 2016 der SQL-Standard auch um JSON erweitert wurde, und diese Standard-Funktionen nicht denen entsprechen, die PostgreSQL Jahre zuvor eingeführt hatte. In der Zwischenzeit lernen andere Datenbanken die Standard-JSON-Funktionen und so natürlich auch PostgreSQL.

Ich habe einen vorläufigen Test des JSON-Patches gemacht. Dabei sind mir ein paar Themen aufgefallen, die ich aber noch nicht näher kontrolliert habe. Das werde ich in den nächsten Wochen erledigen und Probleme gegebenenfalls melden.

Die letzte Folie ist mein Angebot an die PostgreSQL-Community bei der Interpretation des Standards und dem Testen neuer Funktionen zu helfen.

Vergiss nicht, dass dieser Artikel nur eine Zusammenfassung ist. Mehr gibt’s in den Folien [PDF; 5MB].