Każdy typ PostgreSQL może być tablicą. Zadeklaruj kolumnę jako text[] albo int[], zapisuj wartości jako '{a,b}' albo ARRAY['a', 'b'] i odczytuj elementy przez tags[1] (indeksy zaczynają się od 1). Żeby sprawdzić, czy tablica zawiera wartość, użyj 'value' = ANY(tags) albo tags @> ARRAY['value'].
Posty 1 i 2 mają tag sql. tags[1] to pierwszy element, a cardinality liczy elementy. Obie formy literału zapisują tę samą wartość; ARRAY[...] jest wygodniejsze, gdy elementy pochodzą z wyrażeń albo zawierają przecinki.
Sprawdzanie, czy tablica zawiera wartość
Przynależność obsługują cztery operatory:
| Test | Znaczenie | Prawda dla '{sql,json}' |
|---|---|---|
'sql' = ANY(tags) | jedna wartość jest w tablicy | tak |
tags @> '{sql,json}' | tablica zawiera wszystkie te wartości | tak |
tags <@ '{sql,json,go}' | każdy element jest na tej liście | tak |
tags && '{go,json}' | tablice mają co najmniej jeden wspólny element | tak |
Tylko post 1 ma oba tagi. Posty 2 i 3 mają wspólny element z {go,json}. Post 3 jest jedynym, dla którego no_go jest fałszem. ALL to odpowiednik ANY: porównanie musi być spełnione dla każdego elementu.
= ANY stosuje reguły NULL z SQL. Jeśli wartości nie ma, a tablica zawiera NULL, wynikiem jest NULL, a nie fałsz. Dlatego WHERE NOT ('x' = ANY(tags)) może pominąć wiersze, których się spodziewasz. @> i && zwracają prawdę albo fałsz.
Indeks GIN przyspiesza @>, <@, && i = na dużych tabelach. Nie pomaga przy = ANY(column), więc przy wyszukiwaniu przez indeks pisz tags @> ARRAY['sql']:
CREATE INDEX posts_tags_idx ON posts USING gin (tags);
Zamiana tablicy na wiersze przez unnest
unnest zwraca jeden wiersz na element, więc możesz liczyć, złączać albo filtrować pojedyncze elementy:
sql występuje w dwóch postach, a pozostałe tagi w jednym. Dodaj WITH ORDINALITY (unnest(tags) WITH ORDINALITY AS t(tag, pos)), żeby dostać też pozycję każdego elementu. Żeby zebrać wiersze z powrotem w tablicę, użyj array_agg (zobacz string_agg i array_agg).
Dodawanie, usuwanie i zmiana elementów
Wynik to {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, 2, {20,30} i a, b, c. W UPDATE te same funkcje zmieniają zapisaną tablicę: SET tags = array_append(tags, 'new') albo SET tags = array_remove(tags, 'old'). array_remove usuwa każde wystąpienie wartości. string_to_array('a,b,c', ',') dzieli tekst na tablicę.
Długość tablicy
cardinality(tags) zwraca liczbę elementów, a dla pustej tablicy 0. array_length(tags, 1) zwraca długość wymiaru 1, a dla pustej tablicy NULL. Dlatego WHERE array_length(tags, 1) = 0 nigdy nie pasuje. Pustą tablicę sprawdzaj przez cardinality(tags) = 0 albo tags = '{}'.
Tablice w PostgreSQL mogą mieć więcej niż jeden wymiar (int[][]), ale każda podtablica musi mieć tę samą długość, a zadeklarowany rozmiar (int[3]) nie jest egzekwowany. Do danych zagnieżdżonych albo nieregularnych zwykle lepiej pasuje JSONB.
Najczęściej zadawane pytania
Czy tablice w PostgreSQL są indeksowane od 0 czy od 1?
Domyślnie od 1: tags[1] to pierwszy element. Indeks poza końcem zwraca NULL zamiast błędu.
Czym różni się IN od = ANY w PostgreSQL?
x IN (1, 2, 3) przyjmuje listę wpisaną w zapytaniu; x = ANY(array) przyjmuje jedną wartość tablicową. ANY pasuje do kodu aplikacji, bo przekazujesz jeden parametr tablicowy (WHERE id = ANY($1)) zamiast budować listę placeholderów.
Kolumna tablicowa czy osobna tabela?
Tablica pasuje do krótkiej listy, którą czytasz i zapisujesz razem z wierszem, na przykład tagów. Gdy elementy potrzebują własnych kolumn, kluczy obcych albo są zmieniane pojedynczo, osobna tabela z jednym wierszem na element jest łatwiejsza do odpytywania i pilnowania ograniczeniami.
Jak sprawdzić, czy tablica jest pusta w PostgreSQL?
Użyj cardinality(arr) = 0 albo arr = '{}'. array_length(arr, 1) = 0 nigdy nie pasuje, bo array_length zwraca NULL dla pustej tablicy.