Menu

PostgreSQL Arrays: ANY, enthält (@>) und unnest

Array-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.

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

Jeder PostgreSQL Typ kann ein Array sein. Deklariere die Spalte als text[] oder int[], schreib Werte als '{a,b}' oder ARRAY['a', 'b'] und lies Elemente mit tags[1] (Indizes beginnen bei 1). Um zu prüfen, ob ein Array einen Wert enthält, nimmst du 'value' = ANY(tags) oder tags @> ARRAY['value'].

Die Posts 1 und 2 haben den Tag sql. tags[1] ist das erste Element, und cardinality zählt sie. Beide Literalformen speichern denselben Wert; ARRAY[...] ist bequemer, wenn die Elemente aus Ausdrücken stammen oder Kommas enthalten.

Prüfen, ob ein Array einen Wert enthält

Vier Operatoren decken die Zugehörigkeit ab:

TestBedeutungWahr für '{sql,json}'
'sql' = ANY(tags)ein Wert steht im Arrayja
tags @> '{sql,json}'das Array enthält all diese Werteja
tags <@ '{sql,json,go}'jedes Element steht in dieser Listeja
tags && '{go,json}'die Arrays haben mindestens ein gemeinsames Elementja

Nur Post 1 hat beide Tags. Die Posts 2 und 3 teilen ein Element mit {go,json}. Post 3 ist der einzige, bei dem no_go falsch ist. ALL ist das Gegenstück zu ANY: Der Vergleich muss für jedes Element gelten.

= ANY folgt den NULL Regeln von SQL. Wird der Wert nicht gefunden und das Array enthält ein NULL, ist das Ergebnis NULL und nicht falsch. WHERE NOT ('x' = ANY(tags)) kann deshalb Zeilen verlieren, die du erwartet hast. @> und && liefern wahr oder falsch.

Ein GIN-Index macht @>, <@, && und = auf großen Tabellen schnell. Bei = ANY(column) hilft er nicht, für Abfragen über den Index schreibst du also tags @> ARRAY['sql']:

CREATE INDEX posts_tags_idx ON posts USING gin (tags);

Ein Array mit unnest in Zeilen zerlegen

unnest liefert eine Zeile pro Element, so kannst du einzelne Elemente zählen, joinen oder filtern:

sql kommt in zwei Posts vor, die anderen Tags in je einem. Mit WITH ORDINALITY (unnest(tags) WITH ORDINALITY AS t(tag, pos)) bekommst du zusätzlich die Position jedes Elements. Um Zeilen wieder in ein Array zu sammeln, nimmst du array_agg (siehe string_agg und array_agg).

Elemente hinzufügen, entfernen und ändern

Das liefert {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, 2, {20,30} und a, b, c. In einem UPDATE ändern dieselben Funktionen ein gespeichertes Array: SET tags = array_append(tags, 'new') oder SET tags = array_remove(tags, 'old'). array_remove löscht jedes Vorkommen des Werts. string_to_array('a,b,c', ',') teilt Text in ein Array auf.

Länge eines Arrays ermitteln

cardinality(tags) liefert die Anzahl der Elemente und 0 für ein leeres Array. array_length(tags, 1) liefert die Länge von Dimension 1 und NULL für ein leeres Array. WHERE array_length(tags, 1) = 0 trifft deshalb nie zu. Auf ein leeres Array prüfst du mit cardinality(tags) = 0 oder tags = '{}'.

PostgreSQL Arrays können mehr als eine Dimension haben (int[][]), aber jedes Unterarray muss gleich lang sein, und die deklarierte Größe (int[3]) wird nicht erzwungen. Für verschachtelte oder ungleichmäßige Daten passt JSONB meist besser.

Häufig gestellte Fragen

Beginnen PostgreSQL Arrays bei 0 oder bei 1?

Standardmäßig bei 1: tags[1] ist das erste Element. Ein Index hinter dem Ende liefert NULL statt eines Fehlers.

Was ist der Unterschied zwischen IN und = ANY in PostgreSQL?

x IN (1, 2, 3) nimmt eine Liste, die in der Abfrage steht; x = ANY(array) nimmt einen einzelnen Array-Wert. ANY passt gut zu Anwendungscode, weil du einen einzigen Array-Parameter übergibst (WHERE id = ANY($1)), statt eine Liste von Platzhaltern zu bauen.

Soll ich eine Array-Spalte oder eine eigene Tabelle nehmen?

Ein Array passt zu einer kurzen Liste, die du zusammen mit ihrer Zeile liest und schreibst, etwa Tags. Brauchen die Einträge eigene Spalten oder Foreign Keys oder werden sie einzeln geändert, ist eine eigene Tabelle mit einer Zeile pro Eintrag leichter abzufragen und abzusichern.

Wie prüfe ich in PostgreSQL, ob ein Array leer ist?

Mit cardinality(arr) = 0 oder arr = '{}'. array_length(arr, 1) = 0 trifft nie zu, weil array_length für ein leeres Array NULL liefert.

Illustration der Programmiersprachen bei Coddy

Lerne mit Coddy zu programmieren

Los geht's