Verwenden von Arrays in PostgreSQL: Eine Anleitung

Wenn Sie an SQL-Abbilder von Tabellen denken, kommen Ihnen wahrscheinlich SELECT-Abfragen und das Konzept einer Zeile pro Datenpunkt ohne verschachtelte Struktur in den Sinn.

Aber was wäre, wenn Sie eine Liste mit Nummern, Produkten und mehr in einer Zeile speichern möchten?

Sie könnten die Liste auf viele Zeilen erweitern, dies kann jedoch zu einem Zeilendschungel führen, der möglicherweise nur schwer den Überblick behält. Glücklicherweise bietet PostgreSQL den Array-Datentyp, der zum Speichern und Bearbeiten von Listen verwendet werden kann.

Mit ARRAY ermöglicht PostgreSQL die Definition von Spalten einer Tabelle als mehrdimensionale Arrays variabler Länge. Es können Arrays jedes integrierten oder benutzerdefinierten Basistyps, Aufzählungstyps, zusammengesetzten Typs, Bereichstyps oder jeder Domäne erstellt werden.

Arrays können viele Formen annehmen und es gibt mehrere Möglichkeiten, sie zu deklarieren.

Sie können das Schlüsselwort ARRAY nach der Datentypdeklaration für eine Spalte verwenden, um anzugeben, dass Sie ein Array des zuvor deklarierten Datentyps erstellen möchten, wie unten gezeigt.

Sie könnten der Datentypdeklaration auch eckige Klammern folgen lassen, z. B. text[], aber ich finde, das ist weniger explizit. Und es entspricht nicht dem SQL-Standard, was bei der Schlüsselwortversion der Fall ist.

Im folgenden Beispiel erstelle ich eine kleine Tabelle, um eine Einkaufskarte nachzuahmen und sie mit einigen Permutationen mehrerer Produkte zu füllen. Wenn Sie die vollständige Abfrage ausführen, können Sie auch sehen, wie Arrays als Ausgabe dargestellt werden.

Eine andere Möglichkeit, einer Tabelle ein Array hinzuzufügen, ist die Verwendung der {}-Syntax anstelle des ARRAY-Schlüsselworts mit Klammern. Beide Wege funktionieren. Man muss lediglich auf die Zitate mit den geschweiften Klammern achten. Auch hier finde ich die Schlüsselwortsyntax expliziter und die Klammern näher an Array-Darstellungen in anderen Programmiersprachen, aber beide funktionieren gut.

Mehr zu Data Science10 fortgeschrittene SQL-Konzepte, die Sie für Data Science-Interviews kennen sollten

Was wäre, wenn Sie das Array tatsächlich für bestimmte Zeilen auflösen oder entschachteln müssten, um eine Produktinformationstabelle zu verbinden oder andere Vorgänge auf Listenelementbasis durchzuführen?

Kein Problem. Arrays in PostgreSQL können mit dem Schlüsselwort UNNEST leicht entschachtelt werden:

Wie erwartet erstellt UNNEST eine Zeile pro Element im entsprechenden Array in der ursprünglichen Zeile:

Sie fragen sich vielleicht, ob Sie ein Array jedes Mal entschachteln müssen, wenn Sie für weitere Vorgänge auf ein einzelnes Element zugreifen möchten. Wenn das der Fall ist, ist eine Array-Struktur möglicherweise doch keine gute Idee. Glücklicherweise ermöglichen Arrays in PostgreSQL den Zugriff durch Indizierung mit Klammern und Slices, wie unten gezeigt. Dies ermöglicht eine einfache Auswahl beispielsweise des ersten Elements in jeder Liste oder eines bestimmten Bereichs von Elementen in einer Liste.

Die Indizierung in PostgreSQL-Arrays beginnt bei Eins und nicht bei Null, was sich möglicherweise von dem unterscheidet, was Sie aus anderen Programmiersprachen gewohnt sind.

Slices funktionieren mit der [start:end]-Syntax. Zugriff auf Array-Elemente durch Slicing für alle Zeilen mit mehr als zwei Array-Elementen.

Wenn Sie nicht sicher sind, wie viele Elemente Ihr Array enthält, oder wenn Sie wie oben beschrieben nach der Größe eines Arrays filtern möchten, können Sie das Schlüsselwort CARDINALITY verwenden. Dadurch wird die Anzahl der Elemente im Array als Ganzzahl zurückgegeben.

Was wäre, wenn Sie alle Einkaufswagen auswählen möchten, die ein bestimmtes Produkt in der Reihe haben? Oder eine andere Filteroperation, die auf einem Array-Element basiert? Kein Problem, PostgreSQL ermöglicht Ihnen die Verwendung des Elementwerts in der WHERE-Klausel in Verbindung mit dem Schlüsselwort ANY.

Unten filtere ich nach Zeilen, die „product_c“ in der Spalte „products“ enthalten. Diese Filtermethode eignet sich am besten für einzelne Filterwerte.

Sie können die Filterlogik auch von oben erweitern und nach jeder Einkaufskarte fragen, die ein bestimmtes Unterarray enthält. Hier verwende ich den Operator „@>“, der für „enthält“ steht. Es kann wie folgt gelesen werden:

„Das Produktarray enthält das Array ['product_a', 'product_b'].“

Arrays und Array-Werte können ähnlich wie andere Datentypen mit einer UPDATE … SET …-Klausel aktualisiert werden, wie unten gezeigt. Sie können entweder ein einzelnes Array-Element durch Indizierung aktualisieren oder das gesamte Array.

Wenn Sie ein Array anhängen oder voranstellen (vor den aktuellen Werten einfügen) möchten, können Sie die Funktionen ARRAY_APPEND und ARRAY_PREPEND entsprechend verwenden. Beachten Sie unbedingt den Unterschied in der Argumentreihenfolge, die für jede Funktion unterschiedlich ist, aber einer intuitiven Reihenfolge folgt.

Wenn Sie ein bestimmtes Array-Element entfernen möchten, können Sie UPDATE…SET… zusammen mit der Funktion ARRAY_REMOVE verwenden. Dadurch können Sie entweder ein Element aus den Arrays für alle Zeilen oder nur ein bestimmtes Element entfernen, wenn Sie es mit einer WHERE-Klausel verwenden.

Mehr zu Data Science10 Häufige Fragen im SQL-Interview

Schließlich können Sie PostgreSQL-Arrays mit ARRAY_CAT wie folgt zu einem größeren Array verketten:

Können PostgreSQL-Arrays noch mehr? Schauen Sie sich natürlich die PostgreSQL-Dokumente an, um viele zusätzliche Möglichkeiten zu finden.

Sie verfügen nun über alle Tools, um Listen und verschachtelte Strukturen in Ihren SQL-Tabellen zu verwenden. Mit diesen leistungsstarken Tools können Sie zwar aus dem „Eine Zeile, ein Datensatz“-Paradigma ausbrechen, Sie sollten sie jedoch nur verwenden, wenn sie einen echten Mehrwert bieten und nicht durch einen traditionelleren Ansatz ersetzt werden können. Es gibt gute Gründe für bestehende SQL-Best Practices, daher sollten Ihre Gründe, sie zu umgehen, noch besser sein.