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:
| Test | Bedeutung | Wahr für '{sql,json}' |
|---|---|---|
'sql' = ANY(tags) | ein Wert steht im Array | ja |
tags @> '{sql,json}' | das Array enthält all diese Werte | ja |
tags <@ '{sql,json,go}' | jedes Element steht in dieser Liste | ja |
tags && '{go,json}' | die Arrays haben mindestens ein gemeinsames Element | ja |
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.