Menu

Tablice w PostgreSQL: ANY, zawieranie (@>) i unnest

Kolumny tablicowe w PostgreSQL: deklarowanie, indeksowanie od 1, sprawdzanie, czy tablica zawiera wartość, przez ANY, @> i &&, unnest i najpotrzebniejsze funkcje tablicowe.

Na tej stronie są działające edytory: edytuj, uruchamiaj i od razu zobacz wynik.

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:

TestZnaczeniePrawda dla '{sql,json}'
'sql' = ANY(tags)jedna wartość jest w tablicytak
tags @> '{sql,json}'tablica zawiera wszystkie te wartościtak
tags <@ '{sql,json,go}'każdy element jest na tej liścietak
tags && '{go,json}'tablice mają co najmniej jeden wspólny elementtak

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.

Ilustracja języków programowania w Coddy

Ucz się programowania z Coddy

Zacznij